Condividi tramite

Configurare la crittografia delle colonne sul posto con Transact-SQL

  • Articolo

Si applica a: SQL Server 2019 (15.x) e versioni successive - Solo Windows Database SQL di Azure

Questo articolo descrive come eseguire operazioni di crittografia direttamente sulle colonne utilizzando Always Encrypted con un enclave sicuro con l'istruzione ALTER TABLE/ALTER COLUMN. Per informazioni di base sulla crittografia sul posto e sui prerequisiti generali, vedere Configurare la crittografia delle colonne sul posto usando Always Encrypted con enclave sicuri.

Con l'istruzione ALTER TABLE o ALTER COLUMN, è possibile impostare la configurazione della crittografia di destinazione per una colonna. Quando si esegue l'istruzione, l'enclave sicuro lato server crittografa, crittografa nuovamente o decrittografa i dati archiviati nella colonna, a seconda della configurazione della crittografia corrente e di destinazione, specificata nella definizione della colonna nell'istruzione.

  • Se la colonna non è attualmente crittografata, verrà crittografata se si specifica la clausola ENCRYPTED WITH nella definizione della colonna.
  • Se la colonna è attualmente crittografata, verrà decrittografata (convertita in una colonna di testo non crittografato), se non si specifica la clausola ENCRYPTED WITH nella definizione della colonna.
  • Se la colonna è attualmente crittografata, verrà crittografata nuovamente se si specifica la clausola ENCRYPTED WITH e il tipo di crittografia di colonna specificato o la chiave di crittografia di colonna è diversa dal tipo di crittografia o dalla chiave di crittografia di colonna attualmente usata.

Nota

Non è possibile combinare operazioni di crittografia con altre modifiche in una singola istruzione ALTER TABLE/ALTER COLUMN, tranne l'impostazione della colonna su NULL o NOT NULL oppure la modifica di una regola di confronto. Ad esempio, non è possibile crittografare una colonna E modificare un tipo di dati della colonna in un'unica istruzione Transact-SQL ALTER TABLE/ALTER COLUMN. Usa due istruzioni separate.

Come qualsiasi altra query che utilizza un enclave sicuro lato server, un'istruzione ALTER TABLE/ALTER COLUMN che attiva la crittografia sul posto deve essere trasmessa attraverso una connessione con Always Encrypted e i calcoli dell'enclave attivati.

Nel resto dell'articolo viene descritto come attivare la crittografia sul posto usando l'istruzione ALTER TABLE/ALTER COLUMN da SQL Server Management Studio. In alternativa, è possibile eseguire ALTER TABLE/ALTER COLUMN da Azure Data Studio o dall'applicazione.

Nota

Attualmente, il cmdlet Invoke-Sqlcmd nel modulo di PowerShell SqlServer e sqlcmd non supportano l'uso di ALTER TABLE/ALTER COLUMN per le operazioni di crittografia sul posto.

Eseguire la crittografia sul posto con Transact-SQL in SSMS

Prerequisiti

Passaggi

  1. Apri una finestra di query con Always Encrypted e i calcoli dell'enclave abilitati nella connessione al database. Per informazioni dettagliate, vedere Abilitazione e disabilitazione di Always Encrypted per una connessione di database.

  2. Nella finestra di query eseguire l'istruzione ALTER TABLE/ALTER COLUMN specificando la configurazione di crittografia di destinazione per una colonna da crittografare, decrittografare o crittografare di nuovo. Per crittografare o crittografare nuovamente la colonna, usare la ENCRYPTED WITHclausola. Se la colonna è una colonna di tipo stringa (ad esempio, char, varchar, nchar, nvarchar), potrebbe anche essere necessario modificare le regole di confronto impostando regole di confronto BIN2.

    Nota

    Se la chiave master della colonna è archiviata in Azure Key Vault, potrebbe essere richiesto di accedere ad Azure.

  3. Cancellare la cache dei piani per tutti i batch e le stored procedure che accedono alla tabella, in modo da aggiornare le informazioni di crittografia dei parametri.

    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

    Nota

    Se non si rimuove il piano per la query interessata dalla cache, la prima esecuzione della query dopo la crittografia potrebbe non riuscire.

    Per cancellare attentamente la cache dei piani, utilizzare ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE o DBCC FREEPROCCACHE, poiché ciò potrebbe comportare una temporanea riduzione delle prestazioni delle query. Per ridurre al minimo l'impatto negativo della cancellazione della cache, è possibile rimuovere selettivamente i piani solo per le query interessate.

  4. Chiamare sp_refresh_parameter_encryption per aggiornare i metadati per i parametri di ogni modulo (stored procedure, funzione, vista, trigger) memorizzati in sys.parameters e che potrebbero essere stati invalidati in seguito alla crittografia delle colonne.

Esempi

Crittografia di una colonna sul posto

L'esempio seguente presuppone che:

  • CEK1 è una chiave di crittografia di colonna abilitata per l'enclave.
  • La colonna SSN è testo non crittografato e usa attualmente le regole di confronto predefinite del database, come Latin1, con regole non-BIN2 (ad esempio, Latin1_General_CI_AI_KS_WS).

L'istruzione crittografa la colonna SSN usando la crittografia deterministica e la chiave di crittografia di colonna abilitata per l'enclave direttamente. Sovrascrive anche le regole di confronto del database predefinite con regole di confronto BIN2 corrispondenti (nella stessa pagina di codice).

L'operazione viene eseguita online (ONLINE = ON). Si noti anche la chiamata a ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, che ricrea i piani delle query interessate dalla modifica dello schema di tabella.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH
(ONLINE = ON);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Crittografare nuovamente una colonna sul posto per modificare il tipo di crittografia

L'esempio seguente presuppone che:

  • La colonna SSN è crittografata con la crittografia deterministica e una chiave di crittografia di colonna abilitata all'enclave, CEK1.
  • Le regole di confronto correnti, impostate a livello di colonna, sono Latin1_General_BIN2.

L'istruzione seguente crittografa nuovamente la colonna usando la crittografia casuale e la stessa chiave (CEK1)

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1]
, ENCRYPTION_TYPE = Randomized
, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Crittografare nuovamente una colonna direttamente per ruotare una chiave di crittografia della colonna

L'esempio seguente presuppone che:

  • La colonna SSN è crittografata usando la crittografia randomizzata e una chiave di crittografia di colonna abilitata per l'enclave, CEK1.
  • CEK2 è una chiave di crittografia per colonna abilitata per l'enclave (diversa da CEK1).
  • Le regole di confronto correnti, impostate a livello di colonna, sono Latin1_General_BIN2.

L'istruzione seguente crittografa nuovamente la colonna con CEK2.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK2]
, ENCRYPTION_TYPE = Randomized
, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Decrittografare una colonna sul posto

L'esempio seguente presuppone che:

  • La colonna SSN venga crittografata con una chiave di crittografia di colonna abilitata per l'enclave.
  • L'attuale collatione, impostata a livello di colonna, è Latin1_General_BIN2.

La dichiarazione seguente decrittografa la colonna e mantiene invariata la collazione. In alternativa, è possibile scegliere di modificare le regole di confronto. Sostituire ad esempio le regole di confronto con regole di confronto non BIN2 nella stessa istruzione.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
WITH (ONLINE = ON);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Passaggi successivi

Vedi anche