Rotieren von Always Encrypted-Schlüsseln mithilfe von PowerShell

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

Dieser Artikel enthält die Schritte zur Rotation von Always Encrypted-Schlüsseln unter Verwendung des SqlServer PowerShell-Moduls. Informationen darüber, wie Sie die Arbeit mit dem SQL Server-PowerShell-Modul beginnen, finden Sie unter Konfigurieren von Always Encrypted-Schlüsseln mithilfe von PowerShell.

Die Rotation von Always Encrypted-Schlüsseln bezieht sich auf das Ersetzen eines vorhandenen Schlüssels mit einem neuen Schlüssel. Sie müssen einen Schlüssel möglicherweise rotieren, wenn er kompromittiert wurde, oder um die Richtlinien und Kompatibilitätsbestimmungen Ihrer Organisation einzuhalten, die die regelmäßige Rotation kryptografischer Schlüssel vorschreiben.

Always Encrypted verwendet zwei Schlüsseltypen, es gibt also zwei allgemeine Schlüsselrotationsworkflows: das Drehen von Spaltenhauptschlüssel und das Drehen von Spaltenverschlüsselungsschlüssel.

• Rotieren des Spaltenverschlüsselungsschlüssels – umfasst das Entschlüsseln von Daten, die mit dem vorhandenen Schlüssel verschlüsselt sind und das erneute Verschlüsseln der Daten mit einem neuen Spaltenverschlüsselungsschlüssel. Da ein Spaltenverschlüsselungsschlüssel sowohl Zugriff auf den Schlüssel als auch die Datenbank benötigt, kann die Rotation von Spaltenverschlüsselungsschlüsseln nur ohne Rollentrennung ausgeführt werden.
• Rotation des Spaltenhauptschlüssels – umfasst das Entschlüsseln von Spaltenverschlüsselungsschlüsseln, die mit dem aktuellen Spaltenhauptschlüssel verschlüsselt sind, das erneute Verschlüsseln mit dem neuen Spaltenhauptschlüssel sowie das Aktualisieren der Metadaten für beide Schlüsseltypen. Die Rotation des Spaltenhauptschlüssels kann mit oder ohne Rollentrennung abgeschlossen werden (bei Verwendung des SqlServer PowerShell-Moduls).

Rotation eines Spaltenhauptschlüssels ohne Rollentrennung

Die in diesem Abschnitt beschriebene Methode zum Rotieren eines Spaltenhauptschlüssels unterstützt Rollentrennung zwischen einem Sicherheitsadministrator und einem Datenbankadministrator (DBA) nicht. Einige der nachfolgenden Schritte kombinieren Operationen an physischen Schlüsseln mit Operationen an Schlüsselmetadaten. Dieser Workflow ist also für Organisationen empfohlen, die das DevOps-Modell verwenden, oder für Fälle, in denen die Datenbank in der Cloud gehostet wird und das primäre Ziel ist, Cloudadministratoren (nicht jedoch lokalen DBAs) den Zugriff auf sensible Daten zu verweigern. Die Methode wird nicht empfohlen, wenn DBAs zu potenziellen Angreifern gehören oder wenn DBAs nicht über Zugriff auf sensible Daten verfügen sollen.

Rotieren eines Spaltenhauptschlüssels ohne Rollentrennung (Windows-Zertifikat-Beispiel)

Das folgende Skript stellt ein End-to-End-Beispiel dar, das einen vorhandenen Spaltenhauptschlüssel (CMK1) mit einem neuen Spaltenhauptschlüssel (CMK2) ersetzt.

# 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

Rotation eines Spaltenhauptschlüssels mit Rollentrennung

Der in diesem Abschnitt beschriebene Workflow der Rotation eines Spaltenhauptschlüssels stellt die Trennung zwischen einem Sicherheitsadministrator und einem DBA sicher.

Teil 1: DBA

Ein DBA ruft Metadaten über den Spaltenhauptschlüssel ab, der rotiert werden soll, und über die betroffenen Spaltenverschlüsselungsschlüssel, die dem aktuellen Spaltenhauptschlüssel zugeordnet sind. Der DBA gibt alle Informationen für den Sicherheitsadministrator frei.

Teil 2: Sicherheitsadministrator

Der Sicherheitsadministrator generiert einen neuen Spaltenhauptschlüssel, verschlüsselt den betroffenen Spaltenverschlüsselungsschlüssel erneut mit einem neuen Spaltenhauptschlüssel und gibt die Informationen über den neuen Spaltenhauptschlüssel sowie einen Satz neuer verschlüsselter Werte für die betroffenen Spaltenverschlüsselungsschlüssel für den DBA frei.

Teil 3: DBA

Der DBA erstellt Metadaten für den neuen Spaltenhauptschlüssel und aktualisiert die Metadaten der betroffenen Spaltenverschlüsselungsschlüssel, um den neuen Satz verschlüsselter Werte hinzuzufügen. In diesem Schritt koordiniert der DBA mit den Administratoren der Anwendungen, die Verschlüsselungsspalten abfragen. Diese stellen sicher, dass die Anwendung über Zugriff auf den neuen Spaltenhauptschlüssel verfügt. Sobald alle Anwendungen so eingerichtet sind, dass sie den neuen Spaltenhauptschlüssel verwenden können, entfernt der DBA den alten Satz der verschlüsselten Werte sowie die alten Spaltenhauptschlüssel-Metadaten.

Rotieren eines Spaltenhauptschlüssels mit Rollentrennung (Windows-Zertifikat-Beispiel)

Das folgende Skript ist ein End-to-End-Beispiel zum Generieren eines neuen Spaltenhauptschlüssels, der im Windows-Zertifikatspeicher ein Zertifikat darstellt, und zum Rotieren eines vorhandenen (aktuellen) Spaltenhauptschlüssels, um diesen mit dem neuen Spaltenhauptschlüssel zu ersetzen. Das Skript nimmt an, dass die Zieldatenbank den Spaltenhauptschlüssel namens CMK1 (der rotiert werden soll) enthält, der einige Spaltenverschlüsselungsschlüssel verschlüsselt.

Teil 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
    }
} 

Teil 2: Sicherheitsadministrator

# 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

Teil 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

Rotieren eines Spaltenverschlüsselungsschlüssels

Die Rotation eines Spaltenverschlüsselungsschlüssels umfasst das Entschlüsseln der Daten in allen Spalten, die mit dem zu rotierenden Schlüssel verschlüsselt wurden, und die Neuverschlüsselung der Daten mithilfe des neuen Spaltenverschlüsselungsschlüssels. Dieser Workflow für die Rotation benötigt Zugriff auf die Schlüssel und die Datenbank und kann deshalb nicht mit Rollentrennung ausgeführt werden. Die Rotation eines Spaltenverschlüsselungsschlüssels kann viel Zeit in Anspruch nehmen, wenn die Tabellen mit den Spalten, die mit dem zu rotierenden Schlüssel verschlüsselt wurden, groß ist. Daher muss Ihre Organisation eine Rotation der Spaltenverschlüsselungsschlüssel sorgfältig planen.

Sie können einen Spaltenverschlüsselungsschlüssel offline oder online rotieren. Die erste Methode ist wahrscheinlich schneller, aber Ihre Anwendungen können nicht in die betroffenen Tabellen schreiben. Der letztere Ansatz dauert wahrscheinlich länger, aber Sie können den Zeitraum einschränken, in dem die betroffenen Tabellen für Anwendungen nicht verfügbar sind. Weitere Details finden Sie unter Konfigurieren der Spaltenverschlüsselung mithilfe von Always Encrypted mit PowerShell und Set-SqlColumnEncryption.

Beispiel – Rotieren eines Spaltenverschlüsselungsschlüssels

Das folgende Skript zeigt, wie ein Spaltenverschlüsselungsschlüssel rotiert wird. Das Skript nimmt an, dass die Zieldatenbank einige Spalten enthält, die mit einem Spaltenverschlüsselungsschlüssel namens CEK1 (der rotiert werden soll) verschlüsselt sind, der mithilfe eines Spaltenhauptschlüssels namens CMK1 geschützt ist (der Spaltenhauptschlüssel ist nicht in Azure Key Vault gespeichert).

# 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

Nächste Schritte

• Abfragen von Spalten mit Always Encrypted und SQL Server Management Studio
• Entwickeln von Anwendungen mit Always Encrypted

Weitere Informationen

• Always Encrypted
• Übersicht über die Schlüsselverwaltung für Always Encrypted
• Konfigurieren von Always Encrypted mithilfe von PowerShell
• Rotieren von Always Encrypted-Schlüsseln mithilfe von SQL Server Management Studio
• 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)