Ruotare le chiavi di Always Encrypted con PowerShellRotate Always Encrypted Keys using PowerShell

In questo argomento si applica a: SìSQL ServerSìDatabase SQL di AzurenonAzure SQL Data Warehouse non Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

In questo articolo sono descritti i passaggi per ruotare le chiavi per Always Encrypted usando il modulo PowerShell SqlServer.This article provides the steps to rotate keys for Always Encrypted using the SqlServer PowerShell module. Per informazioni su come usare il modulo PowerShell SqlServer per Always Encrypted, vedere Configurare Always Encrypted tramite PowerShell.For information about how to start using the SqlServer PowerShell module for Always Encrypted, see Configure Always Encrypted using PowerShell.

La rotazione delle chiavi Always Encrypted è il processo di sostituzione di una chiave esistente con una nuova.Rotating Always Encrypted Keys is the process of replacing an existing key with a new one. 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 .You may need to rotate a key if it has been compromised, or in order to comply with your organization’s policies or compliance regulations that mandate cryptographic keys must be rotated on a regular basis.

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.Always Encrypted uses two types of keys, so there are two high-level key rotation workflows; rotating column master keys, and rotating column encryption keys.

  • 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.Column encryption key rotation - involves decrypting data that is encrypted with the current key, and re-encrypting the data using the new column encryption key. Poiché 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.Because rotating a column encryption key requires access to both the keys and the database, column encryption key rotation con only be performed without role separation.
  • 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.Column master key rotation - involves decrypting column encryption keys that are protected with the current column master key, re-encrypting them using the new column master key, and updating the metadata for both types of keys. La rotazione delle chiavi master della colonna può essere completata con o senza la separazione dei ruoli (quando si usa il modulo PowerShell SqlServer).Column master key rotation can be completed with or without role separation (when using the SqlServer PowerShell module).

Rotazione delle chiavi master della colonna senza la separazione dei ruoliColumn Master Key Rotation without Role Separation

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.The method of rotating a column master key described in this section does not support role separation between a Security Administrator and a DBA. 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.Some of the below steps combine operations on the physical keys with operations on key metadata so this workflow is recommended for organizations using the DevOps model, or when your database is hosted in the cloud and the primary goal is to restrict cloud administrators (but not on-premises DBAs) from accessing sensitive data. Questo metodo non è consigliato nel caso in cui eventuali concorrenti includano amministratori di database oppure se semplicemente gli amministratori di database non devono avere accesso ai dati sensibili.It is not recommended if potential adversaries include DBAs, or if DBAs should simply not have access to sensitive data.

AttivitàTask ArticoloArticle Accede alle chiavi di testo non crittografato o all'archivio chiaviAccesses plaintext keys/keystore Accede al databaseAccesses database
Passaggio 1.Step 1. Creare una nuova chiave master della colonna in un archivio chiavi.Create a new column master key in a key store.

Nota: il modulo PowerShell SqlServer non supporta questo passaggio.Note: The SqlServer PowerShell module does not support this step. Per eseguire questa operazione dalla riga di comando, è necessario usare gli strumenti specifici per l'archivio chiavi.To accomplish this task from the command-line, you need to use tools that are specific for your key store.
Creare e archiviare chiavi master della colonna (Always Encrypted)Create and Store Column Master Keys (Always Encrypted) Yes NoNo
Passaggio 2.Step 2. Avviare un ambiente PowerShell e importare il modulo SqlServerStart a PowerShell environment and import the SqlServer module Importare il modulo SqlServerImport the SqlServer module NoNo NoNo
Passaggio 3.Step 3. Connettersi al server e al database.Connect to your server and database. Connessione a un databaseConnecting to a Database NoNo Yes
Passaggio 4.Step 4. Creare un oggetto SqlColumnMasterKeySettings che includa informazioni sul percorso della nuova chiave master della colonna.Create a SqlColumnMasterKeySettings object that contains information about the location of your new column master key. SqlColumnMasterKeySettings è un oggetto presente in memoria (PowerShell).SqlColumnMasterKeySettings is an object that exists in memory (in PowerShell). Per crearlo, è necessario usare il cmdlet specifico dell'archivio chiavi.To create it, you need to use the cmdlet that is specific to your key store. New-SqlAzureKeyVaultColumnMasterKeySettingsNew-SqlAzureKeyVaultColumnMasterKeySettings

New-SqlCertificateStoreColumnMasterKeySettingsNew-SqlCertificateStoreColumnMasterKeySettings

New-SqlCngColumnMasterKeySettingsNew-SqlCngColumnMasterKeySettings

New-SqlCspColumnMasterKeySettingsNew-SqlCspColumnMasterKeySettings
NoNo NoNo
Passaggio 5.Step 5. Creare i metadati relativi alla nuova chiave master della colonna nel database.Create the metadata about your new column master key in your database. New-SqlColumnMasterKeyNew-SqlColumnMasterKey

Nota: questo cmdlet genera automaticamente l'istruzione CREATE COLUMN MASTER KEY (Transact-SQL) per creare i metadati della chiave.Note: under the covers, this cmdlet issues the CREATE COLUMN MASTER KEY (Transact-SQL) statement to create key metadata.
NoNo Yes
Passaggio 6.Step 6. Eseguire l'autenticazione in Azure, se la chiave master corrente o nuova della colonna è archiviata nell'insieme di credenziali delle chiavi di AzureAuthenticate to Azure, if your current column master key or your new column master key is stored in Azure Key Vault Add-SqlAzureAuthenticationContextAdd-SqlAzureAuthenticationContext Yes NoNo
Passaggio 7.Step 7. Avviare la rotazione, crittografando ognuna delle chiavi di crittografia della colonna, che è attualmente protetta con la chiave master della colonna precedente, usando la nuova chiave master della colonna.Start the rotation, by encrypting each of the column encryption keys, which is currently protected with the old column master key, using the new column master key. Dopo questo passaggio ciascuna chiave di crittografia della colonna interessata (associata con la chiave master precedente della colonna durante la rotazione) è crittografata con la chiave master precedente e nuova della colonna e ha due valori crittografati nei metadati del database.After this step each impacted column encryption key (associated with the old column master key, being rotated), is encrypted with both the old and the new column master key, and has two encrypted values in the database metadata. Invoke-SqlColumnMasterKeyRotationInvoke-SqlColumnMasterKeyRotation Yes Yes
Passaggio 8.Step 8. Chiedere agli amministratori di tutte le applicazioni di eseguire query sulle colonne crittografate del database (protette con la chiave master precedente della colonna) per assicurarsi che possano accedere alla nuova chiave master della colonna.Coordinate with the administrators of all applications that query encrypted columns in the database (and are protected with the old column master key), so that they can ensure the applications can access the new column master key. Creare e archiviare chiavi master della colonna (Always Encrypted)Create and Store Column Master Keys (Always Encrypted) Yes NoNo
Passaggio 9.Step 9. Completare la rotazioneComplete the rotation

Nota: prima di eseguire questo passaggio, assicurarsi che tutte le applicazioni che eseguono query sulle colonne crittografate protette con la chiave master precedente della colonna, siano state configurate per usare la nuova chiave master della colonna.Note: before executing this step, make sure all applications that query encrypted columns that are protected with the old column master key, have been configured to use the new column master key. Se non si esegue questo passaggio in maniera completa, alcune applicazioni potrebbero non essere in grado di decrittografare i dati.If you perform this step prematurely, some of those applications may not be able to decrypt the data. Completare la rotazione rimuovendo i valori crittografati dai database creati con la chiave master precedente della colonna.Complete the rotation by removing the encrypted values from the database that were created with the old column master key. Questa operazione rimuove l'associazione tra la chiave master precedente della colonna e le chiavi di crittografia della colonna da essa protetti.This removes the association between the old column master key and the column encryption keys it protects.
Complete-SqlColumnMasterKeyRotationComplete-SqlColumnMasterKeyRotation NoNo Yes
Passaggio 10.Step 10. Rimuovere i metadati dalla chiave master precedente della colonna.Remove the metadata from the old column master key. Remove-SqlColumnMasterKeyRemove-SqlColumnMasterKey NoNo Yes

Nota

Si consiglia di non eliminare definitivamente la chiave master precedente della colonna dopo la rotazione.It is highly recommended you do not permanently delete the old column master key after the rotation. È opportuno invece conservare la chiave master precedente della colonna nell'archivio chiavi corrente o archiviarla in un altro posto sicuro.Instead, you should keep the old column master key in its current key store or archive it in another secure place. Se si ripristina il database da un file di backup creato prima che la nuova chiave master della colonna sia stata configurata, è necessaria la chiave precedente per accedere ai dati.If you restore your database from a backup file to a point in time before the new column master key was configured, you will need the old key to access the data.

Rotazione di una chiave master della colonna senza la separazione dei ruoli (esempio di certificato Windows)Rotating a Column Master Key without Role Separation (Windows Certificate Example)

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).The below script is an end-to-end example that replaces an existing column master key (CMK1) with a new column master key (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>"
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$connection.ConnectionString = $connStr
$connection.Connect()
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
$database = $server.Databases[$databaseName]

# 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 ruoliColumn Master Key Rotation with Role Separation

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.The column master key rotation workflow described in this section ensures the separation between a Security Administrator and a DBA.

Importante

Prima di eseguire passaggi in cui Accede a chiavi di testo non crittografato/archivio chiavi= (passaggi che accedono a chiavi di testo non crittografato/archivio chiavi), assicurarsi che l'ambiente PowerShell venga eseguito su un computer protetto diverso da un computer che ospita il database.Before executing any steps where Accesses plaintext keys/keystore=Yes in the table below (steps that access plaintext keys or the key store), make sure that the PowerShell environment runs on a secure machine that is different from a computer hosting your database. Per altre informazioni, vedere Considerazioni sulla sicurezza per la gestione delle chiavi.For more information, see Security Considerations for Key Management.

Parte 1: Amministratore di databasePart 1: DBA

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.A DBA retrieves metadata about the column master key to be rotated, and about the impacted column encryption keys, which associated with the current column master key. L'amministratore di database condivide tutte queste informazioni con un amministratore della sicurezza.The DBA shares all this information with a Security Administrator.

AttivitàTask ArticoloArticle Accede alle chiavi di testo non crittografato o all'archivio chiaviAccesses plaintext keys/keystore Accede al databaseAccesses database
Passaggio 1.Step 1. Avviare un ambiente PowerShell e importare il modulo SqlServer.Start a PowerShell environment and import the SqlServer module. Importare il modulo SqlServerImport the SqlServer module NoNo NessunoNone
Passaggio 2.Step 2. Connettersi al server e a un database.Connect to your server and a database. Connessione a un databaseConnect to a Database NoNo Yes
Passaggio 3.Step 3. Recuperare i metadati sulla chiave master precedente della colonna.Retrieve the metadata about old column master key. Get-SqlColumnMasterKeyGet-SqlColumnMasterKey NoNo Yes
Passaggio 4.Step 4. Recuperare i metadati sulle chiavi di crittografia di colonna, protetti dalla chiave master precedente della colonna, inclusi i relativi valori crittografati.Retrieve the metadata about column encryption keys, protected by the old column master key, including their encrypted values. Get-SqlColumnEncryptionKeyGet-SqlColumnEncryptionKey NoNo Yes
Passaggio 5.Step 5. Condividere il percorso della chiave master della colonna (nome del provider e percorso della chiave master della colonna) e i valori crittografati delle chiavi di crittografia della colonna corrispondenti, protetti con la chiave master della colonna precedente.Share the location of the column master key (the provider name and a key path of the column master key) and the encrypted values of the corresponding column encryption keys, protected with the old column master key. Vedere gli esempi seguenti.See the examples below. NoNo NoNo

Parte 2: Amministratore della sicurezzaPart 2: Security Administrator

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.The Security Administrator generates a new column master key, re-encrypts the impacted column encryption keys with the new column master key, and shares the information about the new column master key as well as the set of new encrypted values for the impacted column encryption keys, with the DBA.

AttivitàTask ArticoloArticle Accede alle chiavi di testo non crittografato o all'archivio chiaviAccess plaintext keys/keystore Accede al databaseAccesses database
Passaggio 1.Step 1. Ottenere dall'amministratore del database il percorso della chiave master precedente della colonna e i valori crittografati delle chiavi di crittografia corrispondenti della colonna, protette con la chiave master precedente della colonna.Obtain the location of the old column master key and the encrypted values of the corresponding column encryption keys, protected with the old column master key, from your DBA. N/DN/A
Vedere gli esempi seguenti.See the examples below.
NoNo NoNo
Passaggio 2.Step 2. Creare una nuova chiave master della colonna in un archivio chiavi.Create a new column master key in a key store.

Nota: il modulo SqlServer non supporta questo passaggio.Note: The SqlServer module does not support this step. Per eseguire questa attività da una riga di comando, è necessario usare gli strumenti specifici del tipo di archivio chiavi.To accomplish this task from a command-line, you need to use the tools that are specific the type of your key store.
Creazione e archiviazione di chiavi master della colonna (Always Encrypted)Creating and Storing Column Master Keys (Always Encrypted) Yes NoNo
Passaggio 3.Step 3. Avviare un ambiente PowerShell e importare il modulo SqlServer.Start a PowerShell environment and import the SqlServer module. Importare il modulo SqlServerImport the SqlServer module NoNo NoNo
Passaggio 4.Step 4. Creare un oggetto SqlColumnMasterKeySettings che includa informazioni sul percorso della chiave master precedente della colonna.Create a SqlColumnMasterKeySettings object that contains information about the location of your old column master key. SqlColumnMasterKeySettings è un oggetto presente in memoria (PowerShell).SqlColumnMasterKeySettings is an object that exists in memory (in PowerShell). New-SqlColumnMasterKeySettingsNew-SqlColumnMasterKeySettings NoNo NoNo
Passaggio 5.Step 5. Creare un oggetto SqlColumnMasterKeySettings che includa informazioni sul percorso della nuova chiave master della colonna.Create a SqlColumnMasterKeySettings object that contains information about the location of your new column master key. SqlColumnMasterKeySettings è un oggetto presente in memoria (PowerShell).SqlColumnMasterKeySettings is an object that exists in memory (in PowerShell). Per crearlo, è necessario usare il cmdlet specifico dell'archivio chiavi.To create it, you need to use the cmdlet that is specific to your key store. New-SqlAzureKeyVaultColumnMasterKeySettingsNew-SqlAzureKeyVaultColumnMasterKeySettings

New-SqlCertificateStoreColumnMasterKeySettingsNew-SqlCertificateStoreColumnMasterKeySettings

New-SqlCngColumnMasterKeySettingsNew-SqlCngColumnMasterKeySettings

New-SqlCspColumnMasterKeySettingsNew-SqlCspColumnMasterKeySettings
NoNo NoNo
Passaggio 6.Step 6. Eseguire l'autenticazione in Azure, se la chiave master precedente (corrente) o nuova della colonna è archiviata nell'insieme di credenziali delle chiavi di Azure.Authenticate to Azure, if your old (current) column master key or your new column master key is stored in Azure Key Vault. Add-SqlAzureAuthenticationContextAdd-SqlAzureAuthenticationContext Yes NoNo
Passaggio 7.Step 7. Crittografare nuovamente ogni valore della chiave di crittografia della colonna, attualmente protetta con la chiave master precedente della colonna, usando la nuova chiave master della colonna.Re-encrypt each value of the column encryption key, which is currently protected with the old column master key, using the new column master key. New-SqlColumnEncryptionKeyEncryptedValueNew-SqlColumnEncryptionKeyEncryptedValue

Nota: quando si esegue questo cmdlet, è possibile eseguire il passaggio degli oggetti SqlColumnMasterKeySettings per la chiave master precedente e nuova della colonna, insieme a un valore della chiave di crittografia della colonna da crittografare di nuovo.Note: When calling this cmdlet, pass the SqlColumnMasterKeySettings objects for both the old and the new column master key, along with a value of the column encryption key, to be re-encrypted.
Yes NoNo
Passaggio 8.Step 8. Condividere il percorso della nuova chiave master della colonna (nome del provider e percorso della chiave master della colonna) e il set di nuovi valori crittografati delle chiavi di crittografia della colonna con l'amministratore del database.Share the location of the new column master key (the provider name and a key path of the column master key) and the set of new encrypted values of the column encryption keys, with your DBA. Vedere gli esempi seguenti.See the examples below. NoNo NoNo

Nota

Si consiglia di non eliminare definitivamente la chiave master precedente della colonna dopo la rotazione.It is highly recommended you do not permanently delete the old column master key after the rotation. È opportuno invece conservare la chiave master precedente della colonna nell'archivio chiavi corrente o archiviarla in un altro posto sicuro.Instead, you should keep the old column master key in its current key store or archive it in another secure place. Se si ripristina il database da un file di backup creato prima che la nuova chiave master della colonna sia stata configurata, è necessaria la chiave precedente per accedere ai dati.If you restore your database from a backup file to a point in time before the new column master key was configured, you will need the old key to access the data.

Parte 3: Amministratore di databasePart 3: DBA

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.The DBA creates metadata for the new column master key and updates the metadata of the impacted column encryption keys, to add the new set of encrypted values. 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.In this step, the DBA also coordinates with the administrators of the applications querying encryption columns, who ensure the application can access the new column master key. 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.Once all applications are set up to use the new column master key, the DBA removes the old set of encrypted values and the old column master key metadata.

AttivitàTask ArticoloArticle Accede alle chiavi di testo non crittografato o all'archivio chiaviAccess plaintext keys/keystore Accede al databaseAccesses database
Passaggio 1.Step 1. Ottenere dall'amministratore del database il percorso della nuova chiave master della colonna e il set nuovo di valori crittografati delle corrispondenti chiavi di crittografia della colonna, protette con la chiave master precedente della colonna.Obtain the location of the new column master key and the new set of encrypted values of the corresponding column encryption keys, protected with the old column master key, from your Security Administrator. Vedere gli esempi seguenti.See the examples below. NoNo NoNo
Passaggio 2.Step 2. Avviare un ambiente PowerShell e importare il modulo SqlServer.Start a PowerShell environment and import the SqlServer module. Importare il modulo SqlServerImport the SqlServer module NoNo NoNo
Passaggio 3.Step 3. Connettersi al server e a un database.Connect to your server and a database. Connessione a un databaseConnecting to a Database NoNo Yes
Passaggio 4.Step 4. Creare un oggetto SqlColumnMasterKeySettings che includa informazioni sul percorso della nuova chiave master della colonna.Create a SqlColumnMasterKeySettings object that contains information about the location of your new column master key. SqlColumnMasterKeySettings è un oggetto presente in memoria (PowerShell).SqlColumnMasterKeySettings is an object that exists in memory (in PowerShell). New-SqlColumnMasterKeySettingsNew-SqlColumnMasterKeySettings NoNo NoNo
Passaggio 5.Step 5. Creare i metadati relativi alla nuova chiave master della colonna nel database.Create the metadata about your new column master key in your database. New-SqlColumnMasterKeyNew-SqlColumnMasterKey

Nota: questo cmdlet genera automaticamente l'istruzione CREATE COLUMN MASTER KEY (Transact-SQL) per creare i metadati della chiave.Note: Under the covers this cmdlet issues the CREATE COLUMN MASTER KEY (Transact-SQL) statement to create key metadata.
NoNo Yes
Passaggio 6.Step 6. Recuperare i metadati sulle chiavi di crittografia della colonna, protetti dalla chiave master precedente della colonna.Retrieve the metadata about column encryption keys, protected by the old column master key. Get-SqlColumnEncryptionKeyGet-SqlColumnEncryptionKey NoNo Yes
Passaggio 7.Step 7. Aggiungere un valore crittografato nuovo (creato tramite la nuova chiave master della colonna) ai metadati di ogni chiave di crittografia della colonna interessata.Add a new encrypted value (produced using the new column master key) to the metadata for each impacted column encryption key. Add-SqlColumnEncryptionKeyValueAdd-SqlColumnEncryptionKeyValue NoNo Yes
Passaggio 8.Step 8. Chiedere agli amministratori di tutte le applicazioni di eseguire query sulle colonne crittografate del database (protette con la chiave master precedente della colonna) per assicurarsi che possano accedere alla nuova chiave master della colonna.Coordinate with the administrators of all applications that query encrypted columns in the database (and are protected with the old column master key), so that they can ensure the applications can access the new column master key. Creazione e archiviazione di chiavi master della colonna (Always Encrypted)Creating and Storing Column Master Keys (Always Encrypted) NoNo NoNo
Passaggio 9.Step 9. Completare la rotazione rimuovendo dal database i valori crittografati associati con la chiave master precedente della colonna.Complete the rotation, by removing the encrypted values associated with the old column master key from the database.

Nota: prima di eseguire questo passaggio, assicurarsi che tutte le applicazioni che eseguono query sulle colonne crittografate protette con la chiave master precedente della colonna, siano state configurate per usare la nuova chiave master della colonna.Note: Before executing this step, make sure all applications that query encrypted columns that are protected with the old column master key, have been configured to use the new column master key. Se non si esegue questo passaggio in maniera completa, alcune applicazioni potrebbero non essere in grado di decrittografare i dati.If you perform this step prematurely, some of those applications may not be able to decrypt the data.

Questo passaggio rimuove l'associazione tra la chiave master precedente della colonna e le chiavi di crittografia della colonna da essa protetti.This step removes an association between the old column master key and the column encryption keys it protects.
Complete-SqlColumnMasterKeyRotationComplete-SqlColumnMasterKeyRotation

In alternativa, è possibile usare Remove-SqlColumnEncryptionKeyValueAlternatively, you can use Remove-SqlColumnEncryptionKeyValue
NoNo Yes
Passaggio 10.Step 10. Rimuovere i metadati dalla chiave master precedente della colonna dal databaseRemove the old column master key metadata from the database Remove-SqlColumnMasterKeyRemove-SqlColumnMasterKey NoNo Yes

Rotazione di una chiave master della colonna con la separazione dei ruoli (esempio di certificato Windows)Rotating a Column Master Key with Role Separation (Windows Certificate Example)

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.The below script is an end-to-end example for generating a new column master key that is certificate in Windows Certificate store, rotating an existing (current) column master key, to replace it with the new column master key. 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.The script assumes, the target database contains the column master key, named CMK1 (to be rotated), which encrypts some column encryption keys.

Parte 1: Amministratore di databasePart 1: DBA

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

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$connection.ConnectionString = $connStr
$connection.Connect()
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
$database = $server.Databases[$databaseName]

# 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 sicurezzaPart 2: Security Administrator

# 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 databasePart 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>"
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$connection.ConnectionString = $connStr
$connection.Connect()
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
$database = $server.Databases[$databaseName]

# 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 colonnaRotating a Column Encryption Key

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.Rotating a column encryption key involves decrypting the data in all columns, encrypted with the key to be rotated, and re-encrypting the data using the new column encryption key. Questo flusso di lavoro di rotazione richiede accesso alle chiavi e al database e quindi non può essere eseguito con la separazione dei ruoli.This rotation workflow requires access to both the keys and the database, and therefore cannot be performed with role separation. Si noti che 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.Note that, rotating a column encryption key can take a very long time, if the tables containing columns encrypted with the key, being rotated, are large. Pertanto, l'organizzazione deve pianificare con molta attenzione una rotazione della chiave di crittografia della colonna.Therefore, your organization needs to plan a column encryption key rotation very carefully.

È possibile ruotare una chiave di crittografia della colonna usando un approccio online o offline.You can rotate a column encryption key using an offline or an online approach. Il primo metodo è probabilmente il più veloce, ma le applicazioni non possono scrivere nelle tabelle interessate.The former method is likely to be faster, but your applications cannot write to the impacted tables. Il secondo approccio richiederà probabilmente più tempo, ma è possibile limitare l'intervallo di tempo durante il quale le tabelle interessate non sono disponibili per le applicazioni.The latter approach will likely to take longer, but you can limit the time interval, during which the impacted tables are not available to applications. Per altre informazioni, vedere Configurare la crittografia della colonna tramite PowerShell e Set-SqlColumnEncryption .Please, see Configure Column Encryption using PowerShell and Set-SqlColumnEncryption for more details.

AttivitàTask ArticoloArticle Accede alle chiavi di testo non crittografato o all'archivio chiaviAccesses plaintext keys/keystore Accede al databaseAccesses database
Passaggio 1.Step 1. Avviare un ambiente PowerShell e importare il modulo SqlServer.Start a PowerShell environment and import the SqlServer module. Importare il modulo SqlServerImport the SqlServer module NoNo NoNo
Passaggio 2.Step 2. Connettersi al server e a un database.Connect to your server and a database. Connessione a un databaseConnecting to a Database NoNo Yes
Passaggio 3.Step 3. Eseguire l'autenticazione in Azure, se la chiave master della colonna che protegge la chiave di crittografia della colonna da ruotare è archiviata nell'insieme di credenziali delle chiavi di Azure.Authenticate to Azure, if your column master key (protecting the column encryption key, to be rotated), is stored in Azure Key Vault. Add-SqlAzureAuthenticationContextAdd-SqlAzureAuthenticationContext Yes NoNo
Passaggio 4.Step 4. Generare una nuova chiave di crittografia della colonna, crittografarla con la chiave master della colonna e creare i metadati della chiave di crittografia della colonna nel database.Generate a new column encryption key, encrypt it with the column master key and create column encryption key metadata in the database. New-SqlColumnEncryptionKeyNew-SqlColumnEncryptionKey

Nota: usare una variante del cmdlet che genera internamente e crittografa una chiave di crittografia della colonna.Note: Use a variation of the cmdlet that internally generates and encrypts a column encryption key.
Questo cmdlet genera automaticamente l'istruzione CREATE COLUMN ENCRYPTION KEY (Transact-SQL) per creare i metadati della chiave.Under the covers this cmdlet issues the CREATE COLUMN ENCRYPTION KEY (Transact-SQL) statement to create the key metadata.
Yes Yes
Passaggio 5.Step 5. Trovare tutte le colonne crittografate con la chiave di crittografia precedente della colonna.Find all columns encrypted with the old column encryption key. Guida alla programmazione di SMO (SQL Server Management Objects)SQL Server Management Objects (SMO) Programming Guide NoNo Yes
Passaggio 6.Step 6. Creare un oggetto SqlColumnEncryptionSettings per ogni colonna interessata.Create a SqlColumnEncryptionSettings object for each impacted column. SqlColumnMasterKeySettings è un oggetto presente in memoria (PowerShell).SqlColumnMasterKeySettings is an object that exists in memory (in PowerShell). Specifica lo schema di crittografia di destinazione per una colonna.It specifies the target encryption scheme for a column. In questo caso, l'oggetto deve specificare che la colonna interessata deve essere crittografata con la nuova chiave di crittografia della colonna.In this case, the object should specify the impacted column should be encrypted using the new column encryption key. New-SqlColumnEncryptionSettingsNew-SqlColumnEncryptionSettings NoNo NoNo
Passaggio 7.Step 7. Crittografare nuovamente le colonne, identificate nel passaggio 5, usando la nuova chiave di crittografia della colonna.Re-encrypt the columns, identified in step 5, using the new column encryption key. Set-SqlColumnEncryptionSet-SqlColumnEncryption

Nota: questo passaggio può richiedere molto tempo.Note: This step may take a long time. Le applicazioni non potranno accedere alle tabelle durante l'intera operazione o una parte di essa, a seconda dell'approccio (online o offline) selezionato.Your applications mill not be able to access the tables through the entire operation or a portion of it, depending on the approach (online vs. offline), you select.
Yes Yes
Passaggio 8.Step 8. Rimuovere i metadati per la chiave di crittografia precedente della colonna.Remove the metadata for the old column encryption key. Remove-SqlColumnEncryptionKeyRemove-SqlColumnEncryptionKey NoNo Yes

Esempio: rotazione di una chiave di crittografia della colonnaExample - Rotating a Column Encryption Key

Lo script seguente illustra la rotazione di una chiave di crittografia della colonna.The below script demonstrates rotating a column encryption key. 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 nell'insieme di credenziali delle chiavi di Azure).The script assumes, the target database contains some columns encrypted with a column encryption key, named CEK1 (to be rotated), which is protected using a column master key, named CMK1 (the column master key is not stored in Azure Key Vault).

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

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$connection.ConnectionString = $connStr
$connection.Connect()
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
$database = $server.Databases[$databaseName]

# 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 successiviNext Steps

Risorse aggiuntiveAdditional Resources