Configurare la crittografia della colonna tramite PowerShellConfigure Column Encryption using PowerShell

QUESTO ARGOMENTO SI APPLICA A: sìSQL Server (a partire dalla versione 2016)sìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Questo articolo descrive la procedura per la configurazione Always Encrypted di destinazione per le colonne del database tramite il cmdlet Set-SqlColumnEncryption nel modulo di PowerShell SqlServer .This article provides the steps for setting the target Always Encrypted configuration for database columns using the Set-SqlColumnEncryption cmdlet (in the SqlServer PowerShell module). Il cmdlet Set-SqlColumnEncryption modifica sia lo schema del database di destinazione che i dati archiviati nelle colonne selezionate.The Set-SqlColumnEncryption cmdlet modifies both the schema of the target database as well as the data stored in the selected columns. I dati archiviati in una colonna possono essere crittografati, crittografati nuovamente o decrittografati, a seconda delle impostazioni di crittografia di destinazione specificate per le colonne e la configurazione di crittografia corrente.The data stored in a column can be encrypted, re-encrypted or decrypted, depending the specified target encryption settings for the columns and the current encryption configuration. Per altre informazioni sul supporto di Always Encrytped nel modulo di PowerShell SqlServer, vedere Configurare Always Encrypted tramite PowerShell.For more information about Always Encrypted support in the SqlServer PowerShell module, please see Configure Always Encrypted using PowerShell.

PrerequisitiPrerequisites

Per impostare la configurazione di crittografia di destinazione, è necessario assicurarsi che:To set the target encryption configuration, you need to make sure:

  • nel database sia configurata una chiave di crittografia della colonna (se si vuole crittografare o crittografare nuovamente una colonna).a column encryption key is configured in the database (if you are encrypting or re-encrypting a column). Per i dettagli, vedere Configure Always Encrypted Keys using PowerShell(Configurare le chiavi Always Encrypted tramite PowerShell).For details, see Configure Always Encrypted Keys using PowerShell.
  • sia possibile accedere alla chiave master di ogni colonna che si vuole crittografare, crittografare nuovamente o decrittografare dal computer che esegue i cmdlet di PowerShell.you can access the column master key for each column you want to encrypt, re-encrypt, or decrypt, from the computer running the PowerShell cmdlets.

Considerazioni sulle prestazioni e sulla disponibilitàPerformance and Availability Considerations

Per applicare le impostazioni di crittografia di destinazione specificate per il database, il cmdlet Set-SqlColumnEncryption scarica in modo trasparente tutti i dati dalle colonne contenenti le tabelle di destinazione, ricarica i dati in un set di tabelle temporanee (con le impostazioni di crittografia di destinazione) e infine sostituisce le tabelle originali con le nuove versioni delle tabelle.To apply the specified target encryption settings for the database, the Set-SqlColumnEncryption cmdlet transparently downloads all data from the columns containing the target tables, uploads the data back to a set of temporary table (with the target encrypted settings), and finally replaces the original tables with the new versions of the tables. Il provider di dati .NET Framework per SQL Server sottostante crittografa e/o decrittografa i dati durante il download e/o il caricamento, a seconda della configurazione di crittografia corrente della colonna di destinazione e delle impostazioni di crittografia di destinazione specificate per le colonne di destinazione.The underlying .NET Framework Data Provider for SQL Server encrypts or/and decrypts data on download or/and upload, depending on the current encryption configuration of the target column is and the specified target encryption settings for the target columns. L'operazione per spostare i dati potrebbe richiedere molto tempo, a seconda delle dimensioni dei dati nelle tabelle interessate e della larghezza di banda di rete.The operation to move the data may take a long time, depending on the size of the data in impacted tables and the network bandwidth.

Il cmdlet Set-SqlColumnEncryption supporta due approcci per l'impostazione della configurazione di crittografia di destinazione: approccio online e approccio offline.The Set-SqlColumnEncryption cmdlet supports two approaches for setting up the target encryption configuration: online and offline.

Con l'approccio offline, le tabelle di destinazione (e tutte le tabelle correlate alle tabelle di destinazione, ad esempio, tutte le tabelle che presentano relazioni di chiave esterna con una tabella di destinazione) non sono disponibili per la scrittura di transazioni per tutta la durata dell'operazione.With the offline approach, the target tables (and any tables related to the target tables, e.g. any tables a target table have foreign key relationships with) are unavailable to write transactions throughout the duration of the operation. La semantica dei vincoli di chiave esterna (CHECK o NOCHECK) viene sempre mantenuta quando si usa l'approccio offline.The semantics of foreign key constraints (CHECK or NOCHECK) are always preserved when using the offline approach.

Con l'approccio in linea (richiede la versione del modulo SqlServer PowerShell 21.x o versione successiva), l'operazione di copia e la crittografia, decrittografare o crittografare nuovamente i dati viene eseguita in modo incrementale.With the online approach (requires the SqlServer PowerShell module version 21.x or later), the operation of copying and encrypting, decrypting or re-encrypting the data is performed incrementally. Le applicazioni possono leggere e scrivere i dati da e verso le tabelle di destinazione per tutta la durata dell'operazione di spostamento dei dati, ad eccezione dell'ultima iterazione, la cui durata è limitata dal parametro MaxDownTimeInSeconds, che è possibile definire.Applications can read and write data from and to the target tables throughout the data movement operation, except the very last iteration, the duration of which is limited by the MaxDownTimeInSeconds parameter (you can define). Per rilevare ed elaborare le modifiche che le applicazioni possono eseguire durata la copia dei dati, il cmdlet abilita il rilevamento delle modifiche nel database di destinazione.To detect and process the changes, applications can make while the data is being copied, the cmdlet enables Change Tracking in the target database. Per questo motivo, è probabile che l'approccio online utilizzi più risorse sul lato server rispetto a quello offline.Because of that, the online approach is likely to consume more resources on the server side than the online approach. L'operazione potrebbe richiedere anche molto più tempo con l'approccio online, soprattutto se è in esecuzione un carico di lavoro con un'intensa attività di scrittura nel database.The operation may also take much more time with the online approach, especially if a write-heavy workload is running against the database. L'approccio online può essere usato per crittografare una tabella alla volta e la tabella deve avere una chiave primaria.The online approach can be used to encrypt one table at a time and the table must have a primary key. Per impostazione predefinita, i vincoli di chiave esterna vengono ricreati con l'opzione NOCHECK per ridurre al minimo l'impatto sulle applicazioni.By default, foreign key constraints are recreated with the NOCHECK option to minimize the impact on applications. È possibile forzare il mantenimento della semantica dei vincoli di chiave esterna specificando l'opzione KeepCheckForeignKeyConstraints.You can enforce preserving the semantics of foreign key constraints by specifying the KeepCheckForeignKeyConstraints option.

Di seguito sono riportate le linee guida per la scelta tra gli approcci offline e online:Here are the guidelines for choosing between the offline and online approaches:

Usare l'approccio offline:Use the offline approach:

  • Per ridurre al minimo la durata dell'operazione.To minimize the duration of the operation.
  • Per crittografare/decrittografare/ricrittografare le colonne in più tabelle contemporaneamente.To encrypt/decrypt/re-encrypt columns in multiple tables at the same time.
  • Se la tabella di destinazione non ha una chiave primaria.If the target table does not have a primary key.

Usare l'approccio online:Use the online approach:

  • Per ridurre al minimo i tempi di inattività o indisponibilità del database per le applicazioni.To minimize the downtime/unavailability of the database to your applications.

Considerazioni sulla sicurezzaSecurity Considerations

Il cmdlet Set-SqlColumnEncryption , usato per configurare la crittografia per le colonne del database, gestisce sia le chiavi Always Encrypted che i dati archiviati nelle colonne del database.The Set-SqlColumnEncryption cmdlet, used to configure encryption for database columns, handles both Always Encrypted keys and the data stored in database columns. È quindi importante eseguire il cmdlet in un computer protetto.Therefore, it is important you run the cmdlet on a secure computer. Se il database è in SQL Server, eseguire il cmdlet da un computer diverso da quello che ospita l'istanza di SQL Server.If your database is in SQL Server, execute the cmdlet from a different computer than the computer hosting your SQL Server instance. Poiché l'obiettivo principale di Always Encrypted è di garantire la sicurezza dei dati sensibili crittografati anche se il sistema di database viene compromesso, eseguire uno script di PowerShell che elabora le chiavi e/o i dati sensibili nei computer SQL Server può ridurre o annullare i vantaggi della funzionalità.Because the primary goal of Always Encrypted is to ensure encrypted sensitive data is safe even if the database system gets compromised, executing a PowerShell script that processes keys and/or sensitive data on the SQL Server computer can reduce or defeat the benefits of the feature.

AttivitàTask ArticoloArticle Accede a chiavi di testo non crittografato/archivio chiaviAccesses plaintext keys/key store 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 al databaseConnect to your server and database Connessione a un databaseConnecting to a Database NoNo Yes
Passaggio 3.Step 3. Eseguire l'autenticazione ad 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 AzureAuthenticate 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. Costruire una matrice di oggetti SqlColumnEncryptionSettings, uno per ogni colonna del database che si vuole crittografare, crittografare nuovamente o decrittografare.Construct an array of SqlColumnEncryptionSettings objects - one for each database column, you want to encrypt, re-encrypt, or decrypt. In PowerShell SqlColumnMasterKeySettings è un oggetto presente in memoriaSqlColumnMasterKeySettings is an object that exists in memory (in PowerShell). che specifica lo schema di crittografia di destinazione per una colonna.It specifies the target encryption scheme for a column. New-SqlColumnEncryptionSettingsNew-SqlColumnEncryptionSettings NoNo NoNo
Passaggio 5.Step 5. Impostare la configurazione di crittografia desiderata, specificata nella matrice di oggetti SqlColumnMasterKeySettings creata nel passaggio precedente.Set the desired encryption configuration, specified in the array of SqlColumnMasterKeySettings objects, you created in the previous step. Una colonna viene crittografata, crittografata nuovamente o decrittografata a seconda delle impostazioni di destinazione e la configurazione di crittografia corrente della colonna stessa.A column will be encrypted, re-encrypted or decrypted, depending on the specified target settings and the current encryption configuration of the column. 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 will 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

Crittografare colonne con l'approccio offline - EsempioEncrypt Columns using Offline Approach - Example

L'esempio riportato di seguito illustra l'impostazione della configurazione di crittografia di destinazione per due colonne.The below example demonstrates setting the target encryption configuration for a couple of columns. Se una colonna non è ancora crittografata, verrà crittografata.If either column is not already encrypted, it will be encrypted. Se una colonna è già crittografata con una chiave e/o un tipo di crittografia diverso, verrà decrittografata e quindi crittografata nuovamente con la chiave o il tipo di destinazione specificato.If any column is already encrypted using a different key and/or a different encryption type, it will be decrypted and then re-encrypted with the specified target key/type.

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

# Encrypt the selected columns (or re-encrypt, if they are already encrypted using keys/encrypt types, different than the specified keys/types.
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.SSN" -EncryptionType "Deterministic" -EncryptionKey "CEK1"
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.BirthDate" -EncryptionType "Randomized" -EncryptionKey "CEK1"
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .

Crittografare colonne con l'approccio online - EsempioEncrypt Columns using Online Approach - Example

L'esempio riportato di seguito illustra l'impostazione della configurazione di crittografia di destinazione per due colonne usando l'approccio online.The below example demonstrates setting the target encryption configuration for a couple of columns using the online approach. Se una colonna non è ancora crittografata, verrà crittografata.If either column is not already encrypted, it will be encrypted. Se una colonna è già crittografata con una chiave e/o un tipo di crittografia diverso, verrà decrittografata e quindi crittografata nuovamente con la chiave o il tipo di destinazione specificato.If any column is already encrypted using a different key and/or a different encryption type, it will be decrypted and then re-encrypted with the specified target key/type.

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

# Encrypt the selected columns (or re-encrypt, if they are already encrypted using keys/encrypt types, different than the specified keys/types.
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.SSN" -EncryptionType "Deterministic" -EncryptionKey "CEK1"
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.BirthDate" -EncryptionType "Randomized" -EncryptionKey "CEK1"
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -UseOnlineApproach -MaxDowntimeInSeconds 180 -LogFileDirectory .

Decrittografare colonne - EsempioDecrypt Columns - Example

L'esempio riportato di seguito illustra come decrittografare tutte le colonne crittografate in un database.The following example shows how decrypt all columns that are currently encrypted in a database.

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

# Find all encrypted columns, and create a SqlColumnEncryptionSetting object for each column.
$ces = @()
$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) {
            $threeColPartName = $tables[$i].Schema + "." + $tables[$i].Name + "." + $columns[$j].Name 
            $ces += New-SqlColumnEncryptionSettings -ColumnName $threeColPartName -EncryptionType "Plaintext" 
        }
    }
}

# Decrypt all columns.
Set-SqlColumnEncryption -ColumnEncryptionSettings $ces -InputObject $database -LogFileDirectory .

Risorse aggiuntiveAdditional Resources