SQL Server Always Encrypted

Nel precedente articolo SQL Server Transparent Data Encryption, il secondo di questa serie di articoli dedicati alle funzionalità di Encryption di SQL Server, abbiamo descritto come per proteggere i dati crittografandoli a livello di I/O, quando sono a riposo.

Questo articolo presenta una introduzione ad un’altra funzionalità di crittografia, nota in un primo momento con il nome di Column Encryption, e successivamente rinominata in Always Encrypted.

Always Encrypted è una funzionalità progettata per proteggere i dati riservati, come i numeri di carte di credito, l’appartenenza ad un partito politico, le informazioni sullo stato di salute di una persona, etc. archiviati in SQL Server, on-premise oppure in cloud con Azure SQL. Always Encrypted consente di crittografare i dati sensibili all’interno delle applicazioni client e non rivelare mai le chiavi di crittografia all’Engine di SQL Server. Questa tecnologia fornisce una separazione tra coloro che possiedono i dati e possono visualizzarli e coloro che li gestiscono ma che non dovrebbero avere accesso a tali informazioni: ci riferiamo agli amministratori di database locali, operatori di database cloud o altri utenti non autorizzati con privilegi elevati. Always Encrypted consente quindi di archiviare in sicurezza i propri dati sensibili (anche in cloud) riducendo il rischio che tali informazioni vengano accedute da malintenzionati in possesso di credenziali con privilegi elevati.

Creazione del database AlwaysEncryptedDB

Gli esempi riportati di seguito illustrano come iniziare a usare Always Encrypted, in particolare vedremo come crittografare le colonne riferite a dati sensibili e come eseguire query su tali colonne crittografate. Useremo il database AlwaysEncryptedDB e la tabella dbo.Person che memorizza dati sensibili nelle colonne SocialSecurityNumber, CreditCardNumber e Salary.

Il seguente script T-SQL effettua:

• Il setup del database AlwaysEncryptedDB
• La creazione della tabella dbo.Person
• L’inserimento di alcuni dati di prova

USE [master];
GO

-- Drop sample database
IF (DB_ID('AlwaysEncryptedDB') IS NOT NULL)
BEGIN
  ALTER DATABASE [AlwaysEncryptedDB]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

  DROP DATABASE [AlwaysEncryptedDB];
END;
GO

CREATE DATABASE [AlwaysEncryptedDB]
 ON  PRIMARY 
 (
   NAME = N'AlwaysEncryptedDB'
   ,FILENAME = N'C:\SQL\DBs\AlwaysEncryptedDB.mdf'
   ,SIZE = 8192KB
   ,FILEGROWTH = 65536KB
 )
 LOG ON 
 (
   NAME = N'AlwaysEncryptedDB_log'
   ,FILENAME = N'C:\SQL\DBs\AlwaysEncryptedDB_log.ldf'
   ,SIZE = 8192KB
   ,FILEGROWTH = 65536KB
  )
GO

USE [AlwaysEncryptedDB];
GO

CREATE TABLE dbo.Person
(
  ID INTEGER IDENTITY(1, 1) NOT NULL
  ,FirstName NVARCHAR(64) NOT NULL
  ,LastName NVARCHAR(64) NOT NULL
  ,SocialSecurityNumber CHAR(11) NOT NULL
  ,CreditCardNumber CHAR(19)
  ,Salary MONEY NOT NULL
);
GO

INSERT INTO dbo.Person (FirstName, LastName, SocialSecurityNumber, CreditCardNumber, Salary)
VALUES
  ('Rob', 'Walters', '795-73-9838', '1111-2222-3333-4444', $31692)
  ,('Gail', 'Erickson', '311-23-4578', '5555-6666-7777-8888', $40984);

INSERT INTO dbo.Person (FirstName, LastName, SocialSecurityNumber, CreditCardNumber, Salary)
VALUES
  ('Janice', 'Galvin', '327-89-2514', '9999-1111-2222-3333', $38115);
GO

SELECT * FROM dbo.Person;
GO

Il contenuto della tabella dbo.Person appare come quello illustrato nella figura seguente.

Il database AlwaysEncryptedDB è ospitato in una istanza on-premise di SQL Server 2022.

Crittografare le colonne che memorizzano dati sensibili

SQL Server Management Studio offre una procedura guidata che consente di configurare facilmente Always Encrypted per le colonne che memorizzano dati sensibili configurando:

• Una chiave master della colonna
• Una chiave di crittografia della colonna
• La crittografia per le colonne selezionate

Per avviare il processo di crittografia usando SSMS (si consiglia l’ultima versione rilasciata), raggiungere la tabella dbo.Person del database AlwaysEncryptedDB, fare clic con il tasto desto del mouse sulla tabella, scegliere Encrypt Columns e avviare la procedura guidata di Always Encrypted come illustra la figura seguente.

Dopo aver selezionato Avanti nella finestra di introduzione della procedura guidata sarà possibile selezionare le colonne che si desidera criptare. Per questo esempio sceglieremo la crittografia deterministica per le colonne SocialSecurityNumber e CreditCardNumber; sceglieremo la crittografia dinamica (non deterministica) per la colonna Salary. La chiave di crittografia sarà quella di default CEK-Auto1 per entrambe le colonne e verrà generata automaticamente dalla procedura guidata. La figura seguente illustra la configurazione da applicare.

La procedura guidata segnala che la COLLATE delle colonne SocialSecurityNumber e CreditCardNumber verrà modificata da SQL_Latin1_General_CP1_CI_AS a Latin1_General_BIN2 perché al momento Always Encrypted non è supportato per le colonne di testo (varchar, char, etc) con COLLATE diverse dalla famiglia di COLLATE binary-code point (_BIN2) quando si utilizza la crittografia deterministica. Premendo Avanti verrà visualizzata la finestra di configurazione della Master Key.

La finestra di configurazione della Master Key permette di configurare una nuova chiave e soprattutto permette di configurare la modalità di archiviazione scegliendo tra le opzioni:

• Azure Key Vault: tipologia consigliata se il database si trova in Azure
• Windows certificate store

Per questo esempio useremo Windows certificate store come illustra la figura seguente.

Premendo Avanti verrà visualizzata la finestra che notifica che la tabella dbo.Person verrà bloccata durante il processo di criptazione e si consiglia di schedulare l’attività nelle fasce orarie dedicate alla manutenzione del sistema. La finestra Run Settings è illustrata nella figura seguente.

Premendo il tasto Avanti verrà visualizzata la finestra di riepilogo per esaminare la configurazione impostata e procedere con la criptazione delle colonne selezionate. Al termine del processo verrà visualizzata la finestra che riepiloga il risultato della procedura come illustra la figura seguente.

Eseguire query su colonne crittografate

Usiamo SQL Server Management Studio per simulare l’applicazione che gestire il database AlwaysEncryptedDB. Apriamo una nuova finestra Query e dopo aver specificato le credenziali per connetterci all’istanza SQL Server, selezioniamo la scheda Always Encrypted e accertiamoci che l’opzione Enable Always Encrypted (column encryption) sia disabilitata come illustra la figura seguente.

Eseguiamo la query per estrarre l’intero contenuto della tabella dbo.Person, con l’opzione Enable Always Encrypted (column encryption) disattivata, SQL Server Management Studio non potrà decriptare i dati archiviati nelle colonne crittografate, la query seguente restituirà i dati crittografati.

SELECT * FROM dbo.Person;
GO

Il contenuto della tabella dbo.Person è illustrato nella figura seguente.

Modifichiamo ora le impostazioni di connessione per consultare i dati decriptati. Dopo aver specificato le credenziali per connetterci al database AlwaysEncryptedDB, selezioniamo la scheda Always Encrypted per attivare l’opzione Enable Always Encrypted (column encryption) come illustra la figura seguente.

Ripetiamo la query per estrarre l’intero contenuto della tabella dbo.Person, poiché si è connessi con Always Encrypted abilitato, SQL Server Management Studio tenterà di decrittografare i dati archiviati nelle colonne crittografate con le chiavi crittografiche precedentemente create.

SELECT * FROM dbo.Person;
GO

Il contenuto della tabella dbo.Person è illustrato nella figura seguente.

Tentiamo ora l’inserimento di una nuova anagrafica nella tabella dbo.Person con il seguente statement di INSERT.

INSERT INTO dbo.Person (FirstName, LastName, SocialSecurityNumber, CreditCardNumber, Salary)
VALUES
  ('Janice', 'Galvin', '327-89-2514', '9999-1111-2222-3333', $38115);
GO

Il comando fallisce, l’errore restituito è il seguente.

Msg 206, Level 16, State 2, Line 68

Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'AlwaysEncryptedDB') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

Quando una query inserisce dati in colonne crittografate o quando si tenta di filtrare i dati in funzione di una o più colonne crittografate (con crittografia deterministica), il passaggio di valori o variabili T-SQL corrispondenti alle colonne crittografate non è supportato. L’inserimento di valori nelle colonne crittografate è consentito solo attraverso l’utilizzo di parametri. Modifichiamo il comando di INSERT utilizzando parametri per le colonne crittografate e tentiamo di ripetere l’operazione.

DECLARE
  @SocialSecurityNumber CHAR(11) = '327-89-2514'
  ,@CreditCardNumber CHAR(19) = '9999-1111-2222-3333'
  ,@Salary MONEY = $38115;

INSERT INTO dbo.Person (FirstName, LastName, SocialSecurityNumber, CreditCardNumber, Salary)
VALUES
  ('Janice', 'Galvin', @SocialSecurityNumber, @CreditCardNumber, @Salary);
GO

SQL Server Management Studio presenterà la seguente finestra per chiedere all’utente se desidera attivare l’opzione Enable Parameterization for Always Encrypted, premendo il tasto Enable l’INSERT andrà a buon fine.

La figura seguente illustra il contenuto completo della tabella dbo.Person dopo l’inserimento della nuova anagrafica.

Il funzionamento dettagliato delle query che interessano colonne crittografate è descritto a questo link.

Rimuovere la criptazione Always Encrypted

Per rimuovere la criptazione da una o più colonne precedentemente criptare con Always Encrypted, ripetere la procedura guidata selezionando PlainText nella colonna Encryption Type come illustra la figura seguente.

Considerazioni finali

Always Encrypted è uno dei metodi più forti di criptazione disponibili al momento in SQL Server e Azure SQL. Si consiglia di criptare solo le colonne strettamente necessarie considerando i seguenti punti di attenzione:

• Scelta del tipo di criptazione più adeguato tra Deterministico e Random valutando pro e contro
• Analisi possibili impatti dovuti al cambio di COLLATE per le colonne criptate, maggiori dettagli qui
• Analisi delle Limitazioni in essere, maggiori dettagli qui
• Analisi dei possibili impatti sulle applicazioni client (query parametriche, connection string, driver supportati), maggiori dettagli qui

Per una panoramica completa sulle funzionalità di Encryption presenti in SQL Server, consiglio la sessione di Gianluca Hotz collegata sotto, le cui slide sono disponibili a questo link.

Buon divertimento con Always Encrypted!