PowerShell を使用して Always Encrypted キーをローテーションする

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance

この記事では、SqlServer PowerShell モジュールを使用し、Always Encrypted キーを交換する手順について説明します。 Always Encrypted に SqlServer PowerShell を使用する方法については、「 PowerShell を使用した Always Encrypted の構成」を参照してください。

Always Encrypted キーの交換は、既存のキーを新しいキーで置き換えるプロセスです。 キーの回転は、キーが侵害されている場合に、または必須の暗号化キーを定期的に回転することを求めた組織のポリシーまたはコンプライアンス規定に準拠するために、行うことが必要な場合があります。

Always Encrypted は 2 種類のキーを使用するので、列マスター キーの交換と列暗号化キーの交換という 2 つの高レベルのキー交換ワークフローがあります。

• 列暗号化キーの交換 - 現在のキーで暗号化されたデータの復号化と、新しい列暗号化キーを使用した再暗号化が行われます。 列暗号化キーの交換には、キーとデータベースの両方にアクセスする必要があるので、列暗号化キーの交換は、役割を分離せずに行う必要があります。
• 列マスター キーの交換 では、現在の列マスター キーで保護された列暗号化キーを暗号化解除し、新しい列マスター キーを使用して列暗号化キーを再度暗号化し、両方の種類のキーについてメタデータを更新するという処理が必要です。 列マスター キーの交換は、(SqlServer PowerShell モジュールを使用する場合) 役割の分離の有無にかかわらず実行できます。

役割の分離を行わない列マスター キーの交換

このセクションで説明されている列マスター キーを交換する方法では、セキュリティ管理者と DBA の間での役割の分離はサポートされていません。 以下の一部の手順では、物理キーの操作とキー メタデータの操作が組み合わされています。そのため、このワークフローは、DevOps を使用する組織、またはデータベースがクラウドでホストされ、主な目的が (オンプレミス DBA ではなく) クラウドの管理者が機密データにアクセスしないように制限することの場合に推奨されます。 潜在的な対立関係に DBA が含まれる場合、または DBA に機密データへのアクセスを許可するべきではない場合は、この方法は推奨されません。

役割の分離なしで列マスター キーを交換する (Windows Certificate の例)

次のスクリプトは、既存の列マスター キー (CMK1) を新しい列マスター キー (CMK2) で置き換えるエンドツーエンドの例です。

# Create a new column master key in Windows Certificate Store.
$cert = New-SelfSignedCertificate -Subject "AlwaysEncryptedCert" -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048

# Import the SqlServer module
Import-Module "SqlServer"

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Create a SqlColumnMasterKeySettings object for your new column master key. 
$newCmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "CurrentUser" -Thumbprint $cert.Thumbprint

# Create metadata for your new column master key in the database.
$newCmkName = "CMK2"
New-SqlColumnMasterKey -Name $newCmkName -InputObject $database -ColumnMasterKeySettings $newCmkSettings

# Initiate the rotation from the current column master key to the new column master key.
$oldCmkName = "CMK1"
Invoke-SqlColumnMasterKeyRotation -SourceColumnMasterKeyName $oldCmkName -TargetColumnMasterKeyName $newCmkName -InputObject $database

# Complete the rotation of the old column master key.
Complete-SqlColumnMasterKeyRotation -SourceColumnMasterKeyName $oldCmkName  -InputObject $database

# Remove the old column master key metadata.
Remove-SqlColumnMasterKey -Name $oldCmkName -InputObject $database

役割の分離ありの列マスター キーの交換

このセクションで説明する列マスター キー ワークフローでは、セキュリティ管理者と DBA 間を分離します。

パート 1: DBA

DBA は、交換する列マスター キーと、現在の列マスター キーと関連付けられている影響を受ける列暗号化キーに関するメタデータを取得します。 DBA は、セキュリティ管理者とすべての情報を共有します。

パート 2: セキュリティ管理者

セキュリティ管理者は、新しい列マスター キーを生成し、影響を受ける列暗号化キーを新しい列マスター キーで再暗号化し、新しい列マスター キーに関する情報と、影響を受ける列暗号化キーの新しい暗号化値セットを、DBA と共有します。

パート 3: DBA

DBA は、新しい列マスター キーのメタデータを作成し、影響を受ける列暗号化キーのメタデータを更新して、新しい暗号化値セットを追加します。 この手順で、DBA は、アプリケーションから新しい列マスター キーにアクセスできるようにする、暗号化列をクエリするアプリケーションの管理者とも調整します。 新しいマスター キーを使用するようにすべてのアプリケーションがセットアップされたら、DBA は古い暗号化値セットと古い列マスター キー メタデータを削除します。

役割の分離ありで列マスター キーを交換する (Windows Certificate の例)

次のスクリプトは、Windows 証明書ストアの証明書である新しい列マスター キーを生成し、既存の (現在の) 列マスター キーを交換し、新しい列マスター キーで置き換えるエンドツーエンドの例です。 このスクリプトでは、対象のデータベースに (交換対象の) CMK1 という列マスター キーが含まれている想定です。このキーで、一部の列暗号化キーが暗号化されます。

パート 1: DBA

# Import the SqlServer module.
Import-Module "SqlServer"

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Retrieve the data about the old column master key, which needs to be rotated.
$oldCmkName = "CMK1"
$oldCmk = Get-SqlColumnMasterKey -Name $oldCmkName -InputObject $database


# Share the location of the old column master key with a Security Administrator, via a CSV file on a share drive.
$oldCmkDataFile = "Z:\oldcmkdata.txt"
"KeyStoreProviderName, KeyPath" > $oldCmkDataFile
$oldCmk.KeyStoreProviderName +", " + $oldCmk.KeyPath >> $oldCmkDataFile


# Find column encryption keys associated with the old column master key and provide the encrypted values of column encryption keys to the Security Administrator, via a CSV file on a share drive.
$ceks = Get-SqlColumnEncryptionKey -InputObject $database
$oldCekValuesFile = "Z:\oldcekvalues.txt"
"CEKName, CEKEncryptedValue" > $oldCekValuesFile 
for($i=0; $i -lt $ceks.Length; $i++){
    if($ceks[$i].ColumnEncryptionKeyValues.Length -eq 2) {
        # This column encryption has 2 encrypted values - let's check, if it is associated with the old column master key.
        if($ceks[$i].ColumnEncryptionKeyValues[0].ColumnMasterKeyName -eq $oldCmkName -or $ceks[$i].ColumnEncryptionKeyValues[1].ColumnMasterKeyName -eq $oldCmkName) {
            Write-Host $ceks[$i].Name "already has 2 encrypted values and therefore" $oldCmkName + "cannot be rotated"
            exit 1
        }
    }
    if($ceks[$i].ColumnEncryptionKeyValues[0].ColumnMasterKeyName -eq $oldCmkName) {# This column encryption key has 1 encrypted value that was produced using the old column master key
        # Save the name and the encrypted value of the column encryption key in the file.
        $encryptedValue =  "0x" + -join ($ceks[$i].ColumnEncryptionKeyValues[0].EncryptedValue |  foreach {$_.ToString("X2") } )
        $ceks[$i].Name + "," + $encryptedValue >> $oldCekValuesFile
    }
} 

パート 2: セキュリティ管理者

# Obtain the location of the old column master key and the encrypted values of the corresponding column encryption keys, from your DBA, via a CSV file on a share drive.
$oldCmkDataFile = "Z:\oldcmkdata.txt"
$oldCmkData = Import-Csv $oldCmkDataFile
$oldCekValuesFile = "Z:\oldcekvalues.txt"
$oldCekValues = @(Import-Csv $oldCekValuesFile)

# Create a new column master key in Windows Certificate Store.
$storeLocation = "CurrentUser"
$certPath = "Cert:\" + $storeLocation + "\My"
$cert = New-SelfSignedCertificate -Subject "AlwaysEncryptedCert" -CertStoreLocation $certPath -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange

# Import the SqlServer module.
Import-Module "SqlServer"

# Create a SqlColumnMasterKeySettings object for your old column master key. 
$oldCmkSettings = New-SqlColumnMasterKeySettings -KeyStoreProviderName $oldCmkData.KeyStoreProviderName -KeyPath $oldCmkData.KeyPath

# Create a SqlColumnMasterKeySettings object for your new column master key. 
$newCmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation $storeLocation -Thumbprint $cert.Thumbprint


# Prepare a CSV file, you will use to share the encrypted values of column encryption keys, produced using the new column master key.
$newCekValuesFile = "Z:\newcekvalues.txt"
"CEKName, CEKEncryptedValue" > $newCekValuesFile

# Re-encrypt each value with the new column master key and save the new encrypted value in the file.
for($i=0; $i -lt $oldCekValues.Count; $i++){
    # Re-encrypt each value with the new CMK
    $newValue = New-SqlColumnEncryptionKeyEncryptedValue -TargetColumnMasterKeySettings $newCmkSettings -ColumnMasterKeySettings $oldCmkSettings -EncryptedValue $oldCekValues[$i].CEKEncryptedValue
    $oldCekValues[$i].CEKName + ", " + $newValue >> $newCekValuesFile
}

# Share the new column master key data with your DBA, via a CSV file.
$newCmkDataFile = $home + "\newcmkdata.txt"
"KeyStoreProviderName, KeyPath" > $newCmkDataFile
$newCmkSettings.KeyStoreProviderName +", " + $newCmkSettings.KeyPath >> $newCmkDataFile

パート 3: DBA

# Obtain the location of the new column master key and the new encrypted values of the corresponding column encryption keys, from your Security Administrator, via a CSV file on a share drive.
$newCmkDataFile = "Z:\newcmkdata.txt"
$newCmkData = Import-Csv $newCmkDataFile
$newCekValuesFile = "Z:\newcekvalues.txt"
$newCekValues = @(Import-Csv $newCekValuesFile)

# Import the SqlServer module.
Import-Module "SqlServer"

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Create a SqlColumnMasterKeySettings object for your new column master key. 
$newCmkSettings = New-SqlColumnMasterKeySettings -KeyStoreProviderName $newCmkData.KeyStoreProviderName -KeyPath $newCmkData.KeyPath
# Create metadata for the new column master key in the database.
$newCmkName = "CMK2"
New-SqlColumnMasterKey -Name $newCmkName -InputObject $database -ColumnMasterKeySettings $newCmkSettings


# Get all CEK objects
$oldCmkName = "CMK1"
$ceks = Get-SqlColumnEncryptionKey -InputObject $database
for($i=0; $i -lt $ceks.Length; $i++){
    if($ceks[$i].ColumnEncryptionKeyValues.Length -eq 2) {# This column encryption key has 2 encrypted values. Let's check, if it is associated with the old CMK.
        if($ceks[$i].ColumnEncryptionKeyValues[0].ColumnMasterKeyName -eq $oldCmkName -or $ceks[$i].ColumnEncryptionKeyValues[1].ColumnMasterKeyName -eq $oldCmkName) {
            Write-Host $ceks[$i].Name "already has 2 encrypted values and therefore" $oldCmkName + "cannot be rotated"
            exit 1
        }
    }
    if($ceks[$i].ColumnEncryptionKeyValues[0].ColumnMasterKeyName -eq $oldCmkName) {
        # Find the corresponding new encrypted value, received from the Security Administrator.
        $newValueRow = ($newCekValues| Where-Object {$_.CEKName -eq $ceks[$i].Name }[0])
        # Update the column encryption key metadata object by adding the new encrypted value
        Add-SqlColumnEncryptionKeyValue -ColumnMasterKeyName $newCmkName -Name $ceks[$i].Name -EncryptedValue $newValueRow.CEKEncryptedValue -InputObject $database 
    }
}

# Complete the rotation of the current column master key.
Complete-SqlColumnMasterKeyRotation -SourceColumnMasterKeyName $oldCmkName  -InputObject $database

# Remove the old column master key.
Remove-SqlColumnMasterKey -Name $oldCmkName -InputObject $database

列暗号化キーの交換

列暗号化キーを交換するには、交換するキーで暗号化されたすべての列のデータを暗号化解除し、新しい列暗号化キーを使用してデータを再度暗号化する必要があります。 この交換ワークフローでは、キーとデータベースの両方にアクセスする必要があるので、役割の分離ありでは実行できません。 交換されるキーで暗号化される列が含まれるテーブルが大きい場合、列暗号化キーの交換に長い時間がかかることがあります。 したがって、組織で列暗号化キーを交換する場合は、慎重に計画を立てる必要があります。

オフラインまたはオンラインの手法で、列暗号化キーを回転できます。 前者の方法のほうが速いですが、お使いのアプリケーションでは、影響を受けるテーブルに書き込めません。 後者の手法は時間がかかりますが、時間間隔を設定できます。その間、アプリケーションでは影響を受けるテーブルを利用できません。 詳細については、「PowerShell で Always Encrypted を使用した列暗号化を構成する」および「Set-SqlColumnEncryption」を参照してください。

例 - 列暗号化キーの交換

次のスクリプトは、列暗号化キーを交換する例です。 このスクリプトでは、対象データベースに、(交換対象の) CEK1 という列暗号化キーで暗号化されたいくつかの列が含まれていると仮定しています。このキーは、CMK1 という列マスター キーを使用して保護されています (列マスター キーは Azure Key Vault に格納されていません)。

# Import the SqlServer module.
Import-Module "SqlServer"

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Generate a new column encryption key, encrypt it with the column master key and create column encryption key metadata in the database. 
$cmkName = "CMK1"
$newCekName = "CEK2"
New-SqlColumnEncryptionKey -Name $newCekName -InputObject $database -ColumnMasterKey $cmkName 


# Find all columns encrypted with the old column encryption key, and create a SqlColumnEncryptionSetting object for each column.
$ces = @()
$oldCekName = "CEK1"
$tables = $database.Tables
for($i=0; $i -lt $tables.Count; $i++){
    $columns = $tables[$i].Columns
    for($j=0; $j -lt $columns.Count; $j++) {
        if($columns[$j].isEncrypted -and $columns[$j].ColumnEncryptionKeyName -eq $oldCekName) {
            $threeColPartName = $tables[$i].Schema + "." + $tables[$i].Name + "." + $columns[$j].Name 
            $ces += New-SqlColumnEncryptionSettings -ColumnName $threeColPartName -EncryptionType $columns[$j].EncryptionType -EncryptionKey $newCekName
        }
     }
}

# Re-encrypt all columns, currently encrypted with the old column encryption key, using the new column encryption key.
Set-SqlColumnEncryption -ColumnEncryptionSettings $ces -InputObject $database -UseOnlineApproach -MaxDowntimeInSeconds 120 -LogFileDirectory .

# Remove the old column encryption key metadata.
Remove-SqlColumnEncryptionKey -Name $oldCekName -InputObject $database

次のステップ

• SQL Server Management Studio で Always Encrypted を使用した列のクエリを実行する
• Always Encrypted を使用したアプリケーションの開発

参照

• 常に暗号化
• Always Encrypted のキー管理の概要
• PowerShell を使用した Always Encrypted の構成
• SQL Server Management Studio を使用して Always Encrypted キーを交換する
• CREATE COLUMN MASTER KEY (Transact-SQL)
• DROP COLUMN MASTER KEY (Transact-SQL)
• CREATE COLUMN ENCRYPTION KEY (Transact-SQL)
• ALTER COLUMN ENCRYPTION KEY (Transact-SQL)
• DROP COLUMN ENCRYPTION KEY (Transact-SQL)
• sys.column_master_keys (Transact-SQL)
• sys.column_encryption_keys (Transact-SQL)