How to Enable Transparent Data Encryption for Databases in Your Availability Group

Transparent data encryption is the built-in method for at-rest data encryption in SQL Server. Taking steps towards security has always been important, but it is becoming even more important every day as there is a looming cyber threat industry hungry for easy high-profit data. We should also remember that not all threats are from the outside. Setting up TDE will ensure only those with access to the certificate (It shouldn’t necessarily be your DBA) can restore the database to another location. This protection extends to backups as well as the data and log files in use, which helps provide some peace of mind in the cases of a stolen hard drive, or those with access to the file locations from taking and using it elsewhere.

Combining transparent data encryption (TDE) with Availability Groups can be a bit tricky; this is a simple guide for the steps I used to set it up in my own environment.

Setting up a Test Environment

I recently set up a SQL testing lab with a Domain controller, 3 SQL nodes (2 subnets) and a network share using this guide.

After this was configured, I got to work setting up the primary node with the test database. I used Wide World Importers sample database from the Microsoft documentation.

Setting Up Transparent Data Encryption on a Database

First, I needed to set up the master key, which will be used to protect other keys within the master database. You should choose a strong password to increase security.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1ns2rt $tr0ng P@$Sw0Rd H3r3!';
GO

Second, I needed to create a certificate that will be used to encrypt the database, using a helpful name to identify it later if needed:

USE master;
GO
CREATE CERTIFICATE TestLabCert WITH SUBJECT = 'TestLabCert';
GO

Third, I choose to back up the certificate immediately to a file with a password-protected private key. Without this certificate you cannot restore this database anywhere else, so make sure it is put in a secure location and that the password is safe!

USE master
GO
BACKUP CERTIFICATE TestLabCert
TO FILE = '\\labshare\SQLBackups\SecureBackup\TestLabCert.crt'
WITH PRIVATE KEY (file='\\labshare\SQLBackups\SecureBackup\TestLabCert_Private.pvk',
ENCRYPTION BY PASSWORD='C3rTif1c@te P@$SW0rD')

Fourth, I configured the database encryption key using the previously created certificate using the AES_256 algorithm, which is the most current encryption standard SQL can use.

USE WideWorldImporters_TDE
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TestLabCert;
GO

Lastly, I enable encryption for the database using the ALTER DATABASE command. This will kick off the process of encrypting all the data with the newly created key.

ALTER DATABASE WideWorldImporters_TDE
SET ENCRYPTION ON;
GO

For verification, this script can be used to check the encryption state of the database and provide additional information about the configuration, as well as how long it takes until the encryption process completes. For my test instance, it took a few minutes to encrypt 3Gb of data. You may notice that TempDB is also encrypted. That is by design so that work being done in the encrypted databases stays encrypted, even when being worked on in TempDB.

SELECT 
	 DB_NAME(database_id) AS DatabaseName
	,encryption_state_desc AS EncryptionState
	,percent_complete as Progress
	,key_algorithm AS Algorithm
	,key_length AS KeyLength
FROM sys.dm_database_encryption_keys
GO

Setting Up the Other Replicas for Use in an Availability Group

The above works on standalone as well as in Replicas of an AG. To ensure that the database is encrypted with TDE on both nodes, it is easiest to do so when the database is not in the AG yet. I have modeled the process below:

First, create a master key on the secondary server using a strong password. Again, this master key is used to protect other keys within the master database, including the certificate that will be created in the next step.

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1ns2rt C0mPl3x P@$Sw0Rd H3r3!'

Second, we create a copy of the certificate from the primary node using the certificate and private key files we backed up previously. I needed to grant explicit access on the files to the SQL Server engine domain account to get this to work:

CREATE CERTIFICATE TestLabCert
FROM FILE = '\\labshare\SQLBackups\SecureBackup\TestLabCert.crt'
WITH PRIVATE KEY (file='\\labshare\SQLBackups\SecureBackup\TestLabCert_Private.pvk',
DECRYPTION BY PASSWORD='C3rTif1c@te P@$SW0rD')

Without explicit permission, I got this error:

This process can be copied on as many replicas as needed.

Adding the Database to the Availability Group

Now that the certificate is prepared, the final steps are to add the database into the AG using your preferred method; mine is Automatic Seeding, followed by verifying that the TDE is set up on the secondary replica as well.

It is simple to add your database to the AG:

USE master 
GO
ALTER AVAILABILITY GROUP TestAg1 ADD DATABASE [Wideworldimporters_TDE]

Using auto seeding, you will see this error when you check the second node if your certificate isn’t set up correctly.

If you did everything correctly, you should see all green on the dashboard:

You can run the encryption verification script again, just to see that encryption is there and is configured correctly. You typically won’t see a green dashboard until the certificates are configured correctly, though:

-- Check TDE on Secondary

SELECT 
	 DB_NAME(database_id) AS DatabaseName
	,encryption_state_desc AS EncryptionState
	,percent_complete as Progress
	,key_algorithm AS Algorithm
	,key_length AS KeyLength
FROM sys.dm_database_encryption_keys
GO

And there you have it. Testing a failover should work fine. For mine, I needed to kick off data movement post-failover on the new secondary but got all green shortly afterward:

In conclusion, Transparent Data Encryption (TDE) is an essential tool to enable at-rest data encryption in SQL Server, especially in today’s environment of increasing cyber threats. Transparent Data Encryption helps to secure backups as well as the data and log files in use, providing partial peace of mind in case of data theft. While combining Transparent Data Encryption with Availability Groups can be a bit complicated, the process can be streamlined by following the steps outlined above. By setting up Transparent Data Encryption on your Availability Group and ensuring the database is encrypted on all nodes, you can take a significant step towards ensuring data protection and compliance with industry standards.