Configuring TDE with AlwaysOn Availability Group

Introduction

"This blog demonstrates the detailed steps needed to set up Transparent Data Encryption with AlwaysOn Availability Group."

The SQL Server provides Transparent Data Encryption (TDE) for encrypting the physical files to protect customer sensitive data. It was introduced with SQL Server 2008 as an Enterprise Edition feature.

 TDE is available with the following SQL Server Editions:

•     SQL Server 2008, 2008 R2, 2012, 2014, 2016, 2017 (Evaluation, Developer, Enterprise)
•     From SQL version 2019, TDE is available on most of the editions available. 
•     SQL Server 2019 - Standard, Evaluation, Developer, Enterprise

 Let’s explore how to Configure TDE with AlwaysOn Availability Group in the following scenarios.
1.    Adding TDE encrypted database to AG group.
2.    Configure TDE to the database that already exists in the AG group.
3.    Rotating Expired Certificate

Scenario: Adding TDE encrypted database to AG group.

We are using a two-node AG to set up the TDE and the following process explains the steps in detail. Follow the secondary steps on each of your secondary replicas (if you have more than 1 secondary)

•     Primary Replica: node1
•     Secondary Replica: node2
•     AG Group: TDE_AG

Tip: It is always recommended to run DBCC CHECKDB to ensure that the database is error-free and take the latest full backup of the database before implementing TDE. 

 Step 1: Primary Instance - Create a Master Key

If you are encrypting the TDE for the first time, then there should be no master key and you can use the following SQL that will give no result set.

Now create a master key in the master database using strong passwords.

Validate the master key:

Back up the Master Key to a secure location as a best practice. The password for the backup can be different from the Master Key password.

 Step 2: Primary Instance - Create a Certificate

Create a certificate to secure the database encryption keys. The default expiry date of the certificate is 1 Year.
Tip: It’s a best practice to set the expiry date for 5 years as it is not good to get this expires in one year

Validate using the below TSQL to confirm that the certificate has been created.

Step 3: Primary Instance - Creation of Database Encryption Key (DEK)

Create the DEK which is a symmetric key to encrypt the actual database content and you can create using available AES algorithms.

Step 4: Primary Instance - Backup the Certificate 

Back up the certificate and the private key as a good practice. With this, you can restore the database backup files or attach the database data files to another SQL Server instance.

Step 5: Secondary Instance - Create a Master Key 

You should create a database master key on all secondary replicas if it does not exist, this is like step 1 in the primary instance. The master key was already created on both the instances in step 1.

Step 6: Secondary Instance - Create Secondary Certificate

Copy the certificate from the primary replica to all secondary replicas and create a certificate on the secondary replica using the primary replica certificate.

You need to specify the decryption password that was used earlier to encrypt the backup on the primary replica.

Step 7: Primary Instance - Enabling TDE Encryption

Query the following command as a final step to enable the TDE in the required database.

Now, let's monitor the progress of the encryption process and make sure the state is 3 which describes the encryption is completed.

 The following query lists the databases with TDE enabled on the databases.

The above result shows that the TDE is enabled on the TDE_DB database, and the encryption state 3 means that the database is completely Encrypted. By default, the tempdb will be encrypted automatically when we encrypt using TDE on any user database. 

Step 8: Adding Database to the Availability Group 

Let’s add the encrypted database to the AG group.

Note: Adding TDE encrypted database to an Availability Group does not support GUI options in SSMS.

You need to use TSQL to add the database to the AG group. On the Primary Replica, take a full backup, transaction log backup for the database TDE_Test database, and copy it. You need to then restore it with NORECOVERY on secondary. 

Once the backup and restore is complete, run the following commands to add the database to the Availability Group.

Step 9: AG health Validation 

Now validate the AG health check status through the dashboard and a manual failover test to perform to make sure that our database, with TDE enabled on it is working fine.

Scenario: Configure TDE to the database which is already existing in the AG group. 

Follow the steps (which I had had discussed in our first scenario) to enable TDE when the database is already added to the AG group. 

Step 1: Primary Instance - Create a Master Key

Step 2: Primary Instance - Create a Certificate

Step 3: Primary Instance - Creation of Database Encryption Key (DEK)

Step 4: Primary Instance - Backup the Certificate

Step 5: Secondary Instance - Create a Master Key

Step 6: Secondary Instance - Create Secondary Certificate

Step 7: Primary Instance - Enabling TDE Encryption

Step 9: AG health Validation

As I had already created the master key and certificate on both replicas in our previous scenarios, we can skip steps 1,2,4,5,6,7. You just need to create DEK and enable the TDE from steps 3 and 7.

•    Primary replica: node1

•    Secondary replica: node2

•    AG Group:TDE_AG

•    AG database : Test_tde

•    TDE Certificate : TDE_AG2021

Step 3 and 7 – On Primary Instance

 Monitor the progress of the encryption process and make sure the state is 3 which describes the encryption is completed.

 Check TDE enabled database with the following query.

Validate the AG health check and do a failover test to make sure everything is working fine. 

 Scenario: Rotating Expired Certificate

When you notice that the TDE certificate is expiring soon, you need to rotate the certificate as a best practice even though the expired certificate will not cause any issues on Database regular operations. 

You can check the expiring date for our TDE certificates and follow the steps described to rotate the SQL TDE certificates

Step 1: Primary Instance - Create a New Certificate 

Step 2: Primary Instance - Backup the Certificate 

Step 3: Secondary Instance - Create Secondary Certificate 

Step 4: Primary Instance - Rotate the SQL TDE certificate

 Validate the Expiry date for the Test_tde database:

Step 5: AG health Validation

Save the expired TDE certificates for a while to restore any older backups. The new certificate will only be used when you are restoring the databases that were backed up since the key rotation.

Conclusion

The SQL Server provides Transparent Data Encryption (TDE) for encrypting the physical files to protect customer’s confidential information. In this blog, I have described various scenarios to configure TDE for the AlwaysOn availability group databases.