“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.
• 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
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 taking the latest full backup of the database before implementing TDE.
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.
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.
Create the DEK which is a symmetric key to encrypt the actual database content and you can create using available AES algorithms.
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.
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.
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.
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.
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.
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.
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
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.
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 backup. The new certificate will only be used when you are restoring the databases which were backed up since the key rotation.
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.
Use the Feedback tab to make any comments or ask questions. You can also start a conversation with us.