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 sul posto sulle colonne che usano Always Encrypted con enclave sicuri 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. Usare due istruzioni separate.

Come qualsiasi query che usa un enclave sicuro lato server, un'istruzione ALTER TABLE/ALTER COLUMN che attiva la crittografia sul posto deve essere inviata tramite una connessione con Always Encrypted e i calcoli dell'enclave abilitati.

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. Aprire una finestra di query con Always Encrypted e i calcoli dell'enclave abilitati nella connessione di 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 per aggiornare le informazioni di crittografia dei parametri.

    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

    Nota

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

    Usare ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE o DBCC FREEPROCCACHE per cancellare la cache dei piani con attenzione, perché ciò potrebbe comportare una temporanea riduzione del livello 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) persistente in sys.parameters e che potrebbe risultare invalidato in seguito alla crittografia delle colonne.

Esempi

Crittografia di una colonna sul posto

L'esempio seguente presuppone che:

  • CEK1 sia una chiave di crittografia di colonna abilitata per l'enclave.
  • La colonna SSN sia di testo non crittografato e usi attualmente le regole di confronto del database predefinite, come Latin1 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 sul posto. Sovrascrive anche le regole di confronto del database predefinite con regole di confronto BIN2 corrispondenti (nella stessa tabella codici).

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 sia crittografata con la crittografia deterministica e una chiave di crittografia di colonna abilitata per l'enclave, CEK1.
  • Le regole di confronto correnti, impostate a livello di colonna, siano 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 sul posto per ruotare una chiave di crittografia di colonna

L'esempio seguente presuppone che:

  • La colonna SSN venga crittografata usando la crittografia casuale e una chiave di crittografia di colonna abilitata per l'enclave, CEK1.
  • CEK2 sia una chiave di crittografia di colonna abilitata per l'enclave (diversa da CEK1).
  • Le regole di confronto correnti, impostate a livello di colonna, siano 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 un colonna sul posto

L'esempio seguente presuppone che:

  • La colonna SSN venga crittografata con una chiave di crittografia di colonna abilitata per l'enclave.
  • Le regole di confronto correnti, impostate a livello di colonna, siano Latin1_General_BIN2.

L'istruzione seguente decrittografa la colonna e mantiene invariate le regole di confronto. 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