How to enable TDE on Availability Group

Transparent Data Encryption(TDE) encrypts SQL and azure database files by encrypting data at rest. In a situation where your physical media such as data, log and backup files get stolen the malicious party can restore/attach the database and retrieve data. TDE protects this by not letting the database restored/attached without the associated certificate and key.

Note: When enabling TDE, make sure to backup the certificate and the key associated with the certificate. Without the certificate, you will never be able to restore/attach the database to a different server. The certificate should be available even if the TDE is disabled, part of the transaction log may still remain protected and the certificate may be required until a full backup is performed.

Enabling TDE isn’t as straightforward as it is for a database outside of an availability group. Databases that are in the availability group requires certain considerations and precautions to enable TDE which is explained step by step as follows:

1.Here we have a 3 node cluster and as you can see we only have 1 test database on the primary replica and it’s not part of the availability group yet. When enabling TDE on AlwaysOn, you want to make sure that your databases have been removed from the AG or else the database on the secondary nodes will change to SUSPECT MODE.

ON THE PRIMARY NODE – Verify that the primary node has a Database Master Key (DMK) in the master database.

USE master
GO

SELECT * FROM
sys.symmetric_keys
WHERE name = ‘##MS_DatabaseMasterKey##’

2. As shown above, the Master Key doesn’t exist. In this case, we’ll have to create the Master Key.

USE master;
GO

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = ‘P@$$word1234’;
GO

Note: Make sure to use a complex password and store it in a password vault to avoid any risk of compromisation.

3. Create the Certificate for the test database

USE master;
GO

CREATE CERTIFICATE test_Cert WITH SUBJECT = ‘test Encryption Certificate’;
GO

4. Run the following script to check if the certificate was created

SELECT name, pvt_key_encryption_type_desc
FROM sys.certificates WHERE name = ‘test_Cert’
GO

5. Backup the certificate on a shared location where all 3 node has access and keep it in a secure place

BACKUP CERTIFICATE test_Cert
TO FILE=’\\10.10.XX.XXX\sqlbackup\Database Certificates\test_Cert.certbak’
WITH PRIVATE KEY (FILE=’\\10.10.XX.XXX\sqlbackup\Database Certificates\test_Cert.pkbak’, ENCRYPTION BY PASSWORD=’P@$$word1234′)
GO

6. Create AES_256 encryption using the certificate

USE test;
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE test_Cert;
GO

7. Enable the encryption on the database

USE test;
GO

ALTER DATABASE test
SET ENCRYPTION ON;
GO

8. Check to verify the database was encrypted

SELECT name, is_encrypted, compatibility_level
FROM sys.databases where name = ‘test’
GO

How to find details about each database that is encrypted
SELECT * FROM sys.dm_database_encryption_keys
GO

9. Take a full backup of the database. Do copy-only if it’s a part of a backup maintenance plan so that you don’t break the backup chain if you need to revert back.

BACKUP DATABASE [test]
TO DISK = N’\\10.10.XX.XXX\sqlbackup\Full_Backup_Cert\test_Cert.bak’ WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N’test-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 1
GO

10. Take a log backup(To make it a part of an AG with TDE enabled a log backup is required)

BACKUP LOG [test]
TO DISK = N’\\10.10.XX.XXX\sqlbackup\Log_Backup_Cert\test_Cert.trn’ WITH NOFORMAT, NOINIT, NAME = N’test-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 1
GO

11. ON THE SECONDARY NODES – Create the same Database Master Key (DMK) in the master database that was created on Node 1 for Node 2 and Node 3.

Run this script on all secondary nodes, both Node 2 and Node 3
USE master
GO

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = ‘P@$$word1234’
GO

12. Transfer the certificate from the certificate backup on both the secondary nodes, Node 2 and Node 3

CREATE CERTIFICATE test
FROM FILE=’\\10.10.XX.XXX\sqlbackup\Database Certificates\test_Cert.certbak’
WITH PRIVATE KEY(FILE=’\\10.10.XX.XXX\sqlbackup\Database Certificates\test_Cert.pkbak’,
DECRYPTION BY PASSWORD=’P@$$word1234′)
GO>

13. Restore the full backup followed by the log backup of the encrypted test database with No-Recovery mode. We want the database to be in restoring mode so we can join it later to the Availability Group via script. On both the secondary nodes, we are using the backups we took earlier from the primary node.

USE [master]
GO
RESTORE DATABASE [test]
FROM DISK = N’\\10.10.XX.XXX\sqlbackup\Full_Backup_Cert\test_Cert.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 1
GO

USE [master]
GO
RESTORE DATABASE [test]
FROM DISK = N’\\10.10.XX.XXX\sqlbackup\Log_Backup_Cert\test_Cert.trn’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 1
GO

BACK ON THE PRIMARY NODE

14. Go back to the Primary node and add the database to the availability group. Refresh all of the nodes and you’ll see that the test database has been successfully added to the AG.

Use master
GO

ALTER AVAILABILITY GROUP [TEST-DBCLAG] ADD DATABASE test;