Transparent Data Encryption (TDE) Setup in SQL Server

2018-09-26 · mssql, security, encryption

Every compliance checklist I get eventually asks for "encryption at rest", and TDE is the answer that needs no application changes. It encrypts the data and log files at rest. Anyone who copies the .mdf off the SAN, restores a stolen backup, or pulls a decommissioned disk out of the rack gets ciphertext. It does nothing about a user with SELECT permission, and it does not encrypt data in flight; those are separate problems. TDE is an Enterprise Edition feature. Unlike many other features, it did not come to Standard Edition with SQL Server 2016 SP1, so on 2016 and 2017 you still need Enterprise (or Developer for testing). You also need CONTROL SERVER and somewhere outside the server for the certificate backups; a separate, locked-down file share is the minimum.

The setup is four objects in a chain: a database master key in master, a certificate protected by it, a database encryption key protected by the certificate, and the encryption switch on the database. Losing the certificate means losing the database, so the backup is not optional.

Master key in master

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'M@sterKey!Pass2018#long';

If one already exists you get "Msg 15578: There is already a master key in the database." That is fine; move on.

The certificate

CREATE CERTIFICATE TDE_Cert
WITH SUBJECT = 'TDE certificate for ShopDB',
     EXPIRY_DATE = '2038-01-01';

The expiry date is informational only; TDE ignores it. Set it far in the future so it does not confuse a future colleague running certificate reports.

Back up the certificate now

Do this before encrypting anything. I never move on until I've seen the files on the share. Without this file and its password, no backup of the encrypted database can ever be restored anywhere else.

BACKUP CERTIFICATE TDE_Cert
TO FILE = 'E:\Keys\TDE_Cert.cer'
WITH PRIVATE KEY (
    FILE = 'E:\Keys\TDE_Cert.pvk',
    ENCRYPTION BY PASSWORD = 'Pr1vateKey!Backup#2018'
);

Copy both files plus the password to your secure store and remove them from the SQL Server host. Store the password separately from the files.

Encrypt the database

USE ShopDB;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;

ALTER DATABASE ShopDB SET ENCRYPTION ON;

Use AES_256; TRIPLE_DES_3KEY is deprecated and slower. The ALTER DATABASE returns immediately and a background encryption scan starts. On a large database it can take hours, but the database stays online and fully usable during the scan.

Watching the scan

SELECT DB_NAME(database_id) AS db, encryption_state, percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys;

encryption_state 2 is in progress, 3 is encrypted. You will also see tempdb in the list: as soon as any user database is encrypted, tempdb is encrypted as well, and it stays that way until the instance restarts with no encrypted databases.

Prove it with a restore

Take a backup and try to restore it on a different instance without the certificate:

RESTORE DATABASE ShopDB FROM DISK = 'E:\Backup\ShopDB.bak' WITH MOVE ...;

You should get "Msg 33111: Cannot find server certificate with thumbprint '0x...'". That is the proof that TDE is doing its job. Then restore the certificate on the target instance and repeat:

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Another!Str0ng#Pass';
CREATE CERTIFICATE TDE_Cert
FROM FILE = 'E:\Keys\TDE_Cert.cer'
WITH PRIVATE KEY (FILE = 'E:\Keys\TDE_Cert.pvk', DECRYPTION BY PASSWORD = 'Pr1vateKey!Backup#2018');

The restore now succeeds. Add this to your disaster recovery runbook; the certificate must be in place on any standby, log shipping secondary or Availability Group replica before the database gets there.

Odds and ends

Backups stopped compressing. Before SQL Server 2016, TDE-encrypted databases could not be backup-compressed at all. In 2016 and 2017 compression works, but you must specify MAXTRANSFERSIZE larger than 64 KB: BACKUP DATABASE ShopDB TO DISK = '...' WITH COMPRESSION, MAXTRANSFERSIZE = 131072;.

"Msg 33091: Cannot use certificate 'TDE_Cert', because its private key is not present or it is not protected by the database master key." The master key is not open automatically at startup. Run ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY; in master.

Certificate warning in the error log: "The certificate used for encrypting the database encryption key has not been backed up." Exactly what it says. Go back and back up the certificate.

Instant file initialisation seems slower. TDE does not affect it, but the encryption scan does generate a lot of I/O and full transaction logging on the first pass. Schedule the initial SET ENCRYPTION ON for a quiet period and run extra log backups during the scan.

Log file encryption lags behind. The log is encrypted from the current VLF onwards; older log records remain clear until the log wraps. Take a few log backups after the scan completes if this matters for your audit.

Store the certificate somewhere the database backups are not. I rotate it yearly with ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE NewCert; and keep every old certificate as long as any backup encrypted by it is still retained.