SQL Server Transparent Data Encryption

Nel precedente articolo SQL Server Backup Encryption, il primo di questa serie di articoli dedicati alle funzionalità di Encryption di SQL Server, abbiamo descritto come implementare la crittografia nella propria strategia di backup. In questo articolo descriveremo un’altra funzionalità di crittografia dei dati, la Transparent Data Encryption.

Transparent Data Encryption (TDE) è stata introdotta in SQL Server 2008 per proteggere i dati crittografandoli a livello di I/O, si parla quindi di crittografica dei dati a riposo. Transparent Data Encryption crittografa i file fisici, sia i file di dati (.mdf, .ndf) che il file di log (.ldf) mentre i dati effettivi archiviati all’interno del database non vengono crittografati.

Fino a SQL Server 2017 compreso Transparent Data Encryption era disponibile, per gli ambienti di produzione, solo nell’edizione Enterprise come descritto qui. SQL Server 2019 estende questa funzionalità di sicurezza all’edizione Standard e Web senza limitazioni, come descritto qui.

Come suggerisce il nome, Transparent Data Encryption è stata progettata per rendere completamente trasparente l’intero processo di crittografia alle applicazioni che accedono al database. TDE utilizza Advanced Encryption Standard (AES) o Triple DES, le pagine dei file dati vengono crittografate a riposo e quindi decrittografate nel momento in cui vengono letto dal disco e spostate nella buffer pool. Questa tecnica azzera i problemi e le limitazioni che si hanno quando si interroga un database crittografato.

L’attivazione di Transparent Data Encryption produce backup crittografati by design per i database in cui è attiva. Come descritto nel precedente articolo, anche in questo caso il backup non potrà essere ripristinato senza la disponibilità del certificato e delle relative chiavi crittografiche. È quindi molto importante eseguire il backup del certificato, utilizzato per la crittografia, in un percorso diverso dal file di backup del database. Oltre al database crittografato, l’architettura di Transparent Data Encryption coinvolge il Sistema Operativo e l’istanza SQL Server. Le API di protezione dei dati a livello di sistema operativo permettono di decriptare la chiave master che si trova nel master database dell’istanza SQL Server. La chiave master viene usata per crittografare la chiave master del database. La chiave master del database viene utilizzata per creare il certificato nel database master.

Di seguito verranno descritti i passaggi per abilitare Transparent Data Encryption sul database di esempio AdventureWorks2022.

Se non già presente nel master database, è necessario creare la master key. La master key è una chiave simmetrica utilizzata per proteggere le chiavi private dei certificati e le chiavi asimmetriche presenti nel database. Per maggiori dettagli consultare SQL Server and Database Encryption Keys (Database Engine).

Il seguente frammento di codice T-SQL crea la master key nel database master.

USE [master];
GO
-- Create a database master key
-- The database master key is a symmetric key that is used to protect the private keys
-- of certificates and asymmetric keys that are present in the database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLProtectionEncryption2023';
GO

Dopo aver creato la master key è necessario creare un certificato da utilizzare per la crittografia dei file del database. Per aumentare il livello di sicurezza si possono creare certificati diversi per i diversi database in cui si desidera abilitare Transparent Data Encryption.

Il seguente frammento di codice T-SQL crea il certificato AdventureWorks2022TDECertificate specifico per il database AdventureWorks2022.

USE [master];
GO
-- Create a certificate for transparent data encryption
CREATE CERTIFICATE [AdventureWorks2022TDECertificate]
  WITH SUBJECT = 'Certificate for database transparent data encryption';
GO

Dopo aver creato la master key e il certificato da utilizzare per la crittografia, eseguiamo il backup di quest’ultimo che viene salvato all’interno di un’area riservata. Il backup del certificato sul file system ha lo scopo di proteggere i dati da eventuali failure del server SQL. Eseguiamo il backup del certificato utilizzando il comando BACKUP CERTIFICATE, il nome del certificato per il database AdventureWorks2022 è AdventureWorks2022TDECertificate, il backup verrà salvato in una posizione protetta su unità remota. Esporteremo anche il file della chiave privata (.key) che crittografa il file del certificato, il tutto protetto da password.

USE [master];
GO
-- pvt_key_last_backup_date = NULL
-- Export the backup certificate to a file
BACKUP CERTIFICATE [AdventureWorks2022TDECertificate]
  TO FILE = 'X:\SQL\DBs\Backup\Certificate\AdventureWorks2022TDECertificate.cert'
  WITH PRIVATE KEY (
                     FILE = 'X:\SQL\DBs\Backup\Certificate\AdventureWorks2022TDECertificate.key'
                     ,ENCRYPTION BY PASSWORD = 'royalbreeze489'
                   );
GO

Spostiamoci sul database AdventureWorks2022 per creare la chiave di encryption utilizzando il certificato AdventureWorks2022TDECertificate.

USE [AdventureWorks2022];
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
  ENCRYPTION BY SERVER CERTIFICATE [AdventureWorks2022TDECertificate];
GO

Siamo ora in grado di attivare Transparent Data Encryption sul database AdventureWorks2022.

-- Enable TDE
-- It is asynchronous process, no blocking are required!
ALTER DATABASE [AdventureWorks2022] SET ENCRYPTION ON;
GO

La DMV sys.dm_database_encryption_keys restituisce informazioni sullo stato di crittografia di un database e sulle chiavi di crittografia a esso associate. Sarà interessante eseguire la DMV sull’istanza SQL Server che ospita il database AdventureWorks2022 per osservarne il risultato. La prima volta che ho eseguito la DMV dopo aver attivato Transparent Data Encryption sul database AdventureWorks2022 mi aspettavo restituisse una sola riga con lo stato della crittografia di quel database ma le righe restituite sono state due! La seconda riga era relativa al database di sistema tempdb. Nonostante TDE non sia disponibile per i database di sistema master, model e msdb, tempdb viene crittografato automaticamente quando un database utente abilita TDE, tuttavia non può essere crittografato direttamente.

Lo stato di crittografica di un database può essere interrogato con il seguente frammento di codice T-SQL.

-- Query the DMV sys.dm_database_encryption_keys
SELECT
  DB_NAME(database_id) DBName
  ,encryption_state
  ,encryption_state_desc
  ,percent_complete
  ,*
FROM
  sys.dm_database_encryption_keys;
GO

Concludiamo questo articolo su Transparent Data Encryption con alcune considerazioni circa TDE scan.

Per abilitare TDE su un database, SQL Server deve eseguire un’analisi di crittografia. La scansione legge ogni pagina dai file di dati nel buffer pool e quindi riscrive le pagine crittografate sul disco. Per istanze con grandi quantitativi di RAM e Terabyte di dati, SQL Server 2019 introduce i comandi ENCRYPTION SUSPEND e ENCRYPTION RESUME per offrire un maggiore controllo sull’analisi della crittografia. L’analisi di crittografica può essere sospesa mentre il carico di lavoro sul sistema è alto o durante le ore critiche di lavoro per l’azienda e quindi riprendere in un secondo momento.

Per maggiori dettagli potete consultare la documentazione ufficiale di Transparent Data Encryption.

Buon divertimento!