SQL Server TDE Database Encryption

This is Data-at-rest encryption. TDE is a feature that encrypts data files to protect them at rest.

Databases to be encrypted: testdb, SAM

1) Create a Master Key

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '********';

2) Create a Certificate

CREATE CERTIFICATE SVR_CERTIFICATE WITH SUBJECT = 'TDE Cert for DB';

3) Create a Database Encryption Key and Enable Database Encryption

USE testdb;

CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE SVR_CERTIFICATE;

ALTER DATABASE testdb SET ENCRYPTION ON;

USE SAM;

CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE SVR_CERTIFICATE;

ALTER DATABASE SAM SET ENCRYPTION ON;

4) Backup Certificate

USE master;

BACKUP CERTIFICATE SVR_CERTIFICATE 

TO FILE = 'O:\BACKUP\SVR_CERTIFICATE.cer'

WITH PRIVATE KEY (file='O:\BACKUP\SVR_CERTIFICATE.PVK', ENCRYPTION BY PASSWORD = '********');

Script to verify the Certificate and Encryption status:

SELECT name, is_encrypted

FROM sys.databases

SELECT database_name=d.name,dek.encryptor_type,cert_name=c.name

FROM sys.dm_database_encryption_keys dek

LEFT JOIN sys.certificates c

ON dek.encryptor_thumbprint=c.thumbprint

INNER JOIN sys.databases d

ON dek.database_id=d.database_id;

SELECT FORMAT(pvt_key_last_backup_date,'yyyy-MM-dd'), db_name(dek.database_id) AS encrypted_database,

c.name AS Certificate_Name from sys.certificates c

INNER JOIN sys.dm_database_encryption_keys dek

ON c.thumbprint=dek.encryptor_thumbprint;

Note: When TDE is enabled on any database, tempdb is automatically encrypted to ensure that any temporary data derived from the encrypted database remains secure. The encryptor_type of tempdb in SQL Server is asymmetric because of the way encryption is handled for temporary data. 

TDE Database Encryption is completed successfully!