Encrypt an Existing MS SQL Server 2019 Database in always on Environment using TDE
Amir Kordestani
Jun 10, 2024 5:49:00 PM
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.
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.
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.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';
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.