Transparent Data Encryption (TDE) 

Transparent Data Encryption (TDE) in SQL Server protects data at rest by encrypting database data and log files on disk. It works transparently with existing applications, so they don’t need to be changed when TDE is enabled. TDE uses real-time encryption at the page level. In SQL Server 2019, TDE is available as a Standard Edition feature, eliminating the need to upgrade to Enterprise Edition just for TDE.

 

Prerequisites

1. SQL Server Edition: TDE is available in SQL Server Enterprise Edition and Standard Edition (starting from SQL Server 2019). Make sure you’re using a compatible edition.

2. Always On Availability Group (AG): Set up an AG with at least one primary replica and one secondary replica, and Ensure the AG is synchronized and healthy.

3. A database in Availability Group (AG) Environment: I restored the database AdventureWorks2019.bak in Restore a database in the MS SQL Server Always On Availability Group, I will use this database to apply the TDE process.

 

Encrypting the Database

1. On the Primary Database:

1.1.     Create a master key (if not already created): A master key is essential for TDE. You can create it using the following SQL Command:

USE master; 
GO 

-- step 1 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Amir123/*-Password123/*-'; 
GO 

-- check master key is created 
SELECT name, symmetric_key_id, algorithm_desc, create_date 
FROM sys.symmetric_keys 
WHERE symmetric_key_id = 101;

1.2. You’ll need a certificate to protect the Database Encryption Key (DEK). You can create one using the following SQL Command:

CREATE CERTIFICATE AdvantureCertificate 
WITH SUBJECT = 'Certificate used for TDE in the Advanture database', 
EXPIRY_DATE = '2024-10-01'; 
GO 

-- Check the certificate is created 
select name, subject, start_date, expiry_date 
FROM sys.certificates 
where name='AdvantureCertificate';

Note: If you do not set the expiry date, the default expiry date will be one year.

1.3. Backup the certificate, This certificate is critical to you being able to access data encrypted by TDE, so you should take a backup of it

CREATE CERTIFICATE AdvantureCertificate 
BACKUP CERTIFICATE AdvantureCertificate 
TO FILE = 'C:\CertBackup\AdvantureCertificate.cer' 
WITH PRIVATE KEY 
( 
   FILE = 'C:\CertBackup\AdvantureCertificate.pvk', 
   ENCRYPTION BY PASSWORD = 'UseAStrongPasswordHereToo!£$7Amir' 
);

1.4.    Create a Database Encryption Key (DEK): Use the certificate created in the previous step to protect the DEK  

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

1.5.    Copy the certificate and the private key backup files (created in step 1.3) to the secondary replicas. You should copy the above two files to the location on the replicas that will be specified in Step 7 below.

 

2. On the Secondary Database:

2.1.    On each secondary replica, create the master key (if not already created) and import the certificate.

USE master; 
GO 

-- step 1 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Amir123/*-Password123/*-'; 
GO 

-- check master key is created 
SELECT name, symmetric_key_id, algorithm_desc, create_date 
FROM sys.symmetric_keys 
WHERE symmetric_key_id = 101;

2.2.     Create a certificate on all the secondary replicas using the backup of the certificate and the private key created on the primary replica, and I copied them to the secondary replica step 1.5

CREATE CERTIFICATE AdvantureCertificate 
FROM FILE = 'C:\CertBackup\AdvantureCertificate.cer' 
WITH PRIVATE KEY (FILE = 'C:\CertBackup\AdvantureCertificate.pvk', 
DECRYPTION BY PASSWORD = 'UseAStrongPasswordHereToo!£$7Amir'); 
GO 
select name, subject, start_date, expiry_date 
FROM sys.certificates 
where name='AdvantureCertificate';

 

3. On the Primary Replica: 

Enable TDE on all databases in the Always On Availability Group using the following command.

ALTER DATABASE [AdventureWorks2019] SET ENCRYPTION ON;

You can monitor the encryption process and its progress using the following query in the primary or secondary replicas.

SELECT DB_NAME([database_id]) AS 'Database Name', 
   [encryption_state], 
   [percent_complete], 
   [encryption_state_desc], 
   [encryption_scan_state], 
   [encryption_scan_state_desc], 
   [encryption_scan_modify_date], 
   [create_date], 
   [regenerate_date], 
   [key_algorithm], 
   [key_length], 
   [modify_date], 
   [set_date], 
   [opened_date], 
   [encryptor_thumbprint], 
   [encryptor_type] 
FROM [sys].[dm_database_encryption_keys]

on sqlnode1

on sqlnode2

Now, you can see that the AdventureWorks2019 database is encrypted in both primary and secondary replicas.