Ruotare le chiavi Always Encrypted con PowerShell

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

In questo articolo sono descritti i passaggi per ruotare le chiavi per Always Encrypted usando il modulo PowerShell SqlServer. Per informazioni su come usare il modulo PowerShell SqlServer per Always Encrypted, vedere Configurare Always Encrypted tramite PowerShell.

La rotazione delle chiavi Always Encrypted è il processo di sostituzione di una chiave esistente con una nuova. Potrebbe essere necessario ruotare una chiave se questa è stata compromessa oppure per conformità ai criteri e alle normative dell'organizzazione che impongono la rotazione a intervalli regolari delle chiavi crittografiche.

Always Encrypted usa due tipi di chiavi, quindi ci sono due flussi di lavoro principali per la rotazione delle chiavi: rotazione delle chiavi master della colonna e rotazione delle chiavi di crittografia della colonna.

• Rotazione della chiave di crittografia della colonna : comporta la decrittografia dei dati crittografati con la chiave corrente e la nuova crittografia dei dati tramite la nuova chiave di crittografia della colonna. Dal momento che la rotazione di una chiave di crittografia della colonna richiede l'accesso alle chiavi e al database, questa può solo essere eseguita senza la separazione dei ruoli.
• Rotazione delle chiavi master della colonna : comporta la decrittografia delle chiavi di crittografia della colonna che sono protette dalla chiave master corrente della colonna, la nuova crittografia tramite la nuova chiave master della colonna e l'aggiornamento dei metadati per entrambi i tipi di chiavi. La rotazione delle chiavi master della colonna può essere completata con o senza la separazione dei ruoli (quando si usa il modulo PowerShell SqlServer).

Rotazione delle chiavi master della colonna senza la separazione dei ruoli

Il metodo di rotazione di una chiave master della colonna descritta in questa sezione non supporta la separazione dei ruoli tra un amministratore della sicurezza e un amministratore di database. I passaggi seguenti combinano operazioni sulle chiavi fisiche con operazioni sui metadati della chiave, quindi questo flusso di lavoro è consigliato per le organizzazioni che usano il modello DevOps oppure se il database è ospitato nel cloud e l'obiettivo principale consiste nel limitare l'accesso ai dati sensibili agli amministratori del cloud, escludendo gli amministratori di database locale. Questo metodo non è consigliato nel caso in cui eventuali concorrenti includano amministratori di database oppure se gli amministratori di database non devono avere accesso ai dati sensibili.

Rotazione di una chiave master della colonna senza la separazione dei ruoli (esempio di certificato Windows)

Lo script seguente è un esempio end-to-end che sostituisce una chiave master esistente della colonna (CMK1) con una nuova chiave master della colonna (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

Rotazione delle chiavi master della colonna con la separazione dei ruoli

Il flusso di lavoro della rotazione delle chiavi master della colonna descritto in questa sezione assicura la separazione dei ruoli tra un amministratore della sicurezza e un amministratore di database.

Parte 1: Amministratore di database

Un amministratore di database recupera metadati sulla chiave master della colonna da ruotare e sulle relative chiavi di crittografia associate alla chiave master della colonna corrente. L'amministratore di database condivide tutte queste informazioni con un amministratore della sicurezza.

Parte 2: Amministratore della sicurezza

L'amministratore della sicurezza genera una nuova chiave master della colonna, riesegue la crittografia delle chiavi di crittografia delle colonne interessate e condivide con l'amministratore di database le informazioni sulla nuova chiave master della colonna, nonché il set dei valori nuovi crittografati per le chiavi di crittografia delle colonne interessate.

Parte 3: Amministratore di database

L'amministratore di database crea metadati per la nuova chiave master della colonna e aggiorna i metadati delle chiavi di crittografia delle colonne interessate per aggiungere il set nuovo di valori crittografati. In questo passaggio, l'amministratore di database interagisce anche con gli amministratori delle applicazioni che eseguono query sulle colonne di crittografia, i quali assicurano che l'applicazione può accedere alla nuova chiave master della colonna. Dopo che tutte le applicazioni sono configurate per usare la nuova chiave master della colonna, l'amministratore di database rimuove il set precedente di valori crittografati e i metadati della chiave master precedente della colonna.

Rotazione di una chiave master della colonna con la separazione dei ruoli (esempio di certificato Windows)

Lo script seguente è un esempio end-to-end per la generazione di una nuova chiave master della colonna (certificato nell'archivio certificati di Windows), il quale ruota la chiave master esistente (corrente) della colonna per sostituirla con la nuova chiave master della colonna. Questo script presuppone che il database di destinazione contenga la chiave master della colonna, denominata CMK1 (da ruotare), che crittografa alcune chiavi di crittografia della colonna.

Parte 1: Amministratore di database

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

Parte 2: Amministratore della sicurezza

# 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

Parte 3: Amministratore di database

# 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

Rotazione di una chiave di crittografia della colonna

La rotazione di una chiave di crittografia della colonna comporta la decrittografia dei dati in tutte le colonne crittografate con la chiave da ruotare e la nuova crittografia dei dati che usano la nuova chiave di crittografia della colonna. Questo flusso di lavoro di rotazione richiede accesso alle chiavi e al database e quindi non può essere eseguito con la separazione dei ruoli. La rotazione di una chiave di crittografia della colonna può richiedere molto tempo, se le tabelle contenenti le colonne crittografate con la chiave da ruotare sono di grandi dimensioni. Pertanto, l'organizzazione deve pianificare con attenzione una rotazione della chiave di crittografia della colonna.

È possibile ruotare una chiave di crittografia della colonna usando un approccio online o offline. Il primo metodo è probabilmente il più veloce, ma le applicazioni non possono scrivere nelle tabelle interessate. L'ultimo approccio richiederà probabilmente più tempo, ma è possibile limitare l'intervallo di tempo durante il quale le tabelle interessate non sono disponibili per le applicazioni. Per ulteriori informazioni, vedere Configurare la crittografia delle colonne usando Always Encrypted con PowerShell e Set-SqlColumnEncryption.

Esempio: rotazione di una chiave di crittografia della colonna

Lo script seguente illustra la rotazione di una chiave di crittografia della colonna. Questo script presuppone che il database di destinazione contenga alcune colonne crittografate con una chiave di crittografia della colonna denominata CEK1 (da ruotare), che è protetta da una chiave master della colonna, denominata CMK1 (la chiave master della colonna non è archiviata in 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

Passaggi successivi

• Eseguire query sulle colonne usando Always Encrypted con SQL Server Management Studio
• Sviluppare applicazioni usando Always Encrypted

Vedi anche

• Always Encrypted
• Panoramica della gestione delle chiavi per Always Encrypted
• Configurare Always Encrypted tramite PowerShell
• Ruotare chiavi Always Encrypted con 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)