Key Rotation for SQL Server TDE in an Always On Availability Group Environment

Introduction:

Managing data security in SQL Server goes beyond enabling Transparent Data Encryption (TDE)—it also requires proper lifecycle management of encryption keys, especially in high-availability environments. In an Always On Availability Group setup, performing a TDE key rotation isn't as simple as running a command on a standalone instance. It involves coordinating key changes across replicas while maintaining encryption consistency and ensuring minimal downtime.

In this blog, I’ll walk you through how to safely rotate the Database Encryption Key (DEK) and the Certificate protecting it in a SQL Server Always On environment. You'll learn the prerequisites, steps involved, potential pitfalls, and how to verify that your key rotation was successful across all nodes in the availability group.

1. Key rotation in SQL Server

In SQL Server, when you create a certificate, you have the option to specify an expiration date using the EXPIRY_DATE attribute. This date defines when the certificate will expire.

However, it's important to note that for TDE (Transparent Data Encryption), the certificate's expiration date doesn't affect the encryption of the database. TDE will continue to function and protect the database even if the certificate has expired. The expiration date is more relevant for other security scenarios where the validity of the certificate is critical.

We cannot directly modify or extend the expiration date of an existing certificate or key. Instead, you need to create a new certificate or key with the desired expiration date and use it to replace the existing one.

1.1. Key Points About Certificate Expiry Date for TDE

1. Purpose of Expiry Date: The expiry date helps manage the lifecycle of certificates and reminds administrators when they need to renew or replace them as part of their security practices.
2. TDE Functionality: TDE will continue to encrypt and decrypt the database regardless of the certificate's expiration date. The encryption key is used internally by SQL Server, and the certificate’s expiry doesn’t impact this process.
3. Best Practices: Although TDE doesn't rely on the certificate’s expiration date, it's still a good practice to manage certificates properly:
   • Regularly back up certificates and their private keys.
   • Plan to renew certificates before they expire to maintain good security hygiene.
   • Keep track of certificate expiration dates and have a renewal process in place.
4. Certificate Renewal: If you need to renew a certificate, you can create a new certificate and re-encrypt the database encryption key with the new certificate. Here’s a brief outline of the steps:
   • Create a new certificate.
   • Back up the new certificate and private key.
   • Use the new certificate to protect the database encryption key.
   • Optionally, drop the old certificate after ensuring the new certificate is properly configured.

1.2. Create a New Certificate on the primary replica 

The command is the same as creating the TDE certificate for the first time, except you now provide a different certificate name and expiry date.

USE master; GO CREATE CERTIFICATE NewTestCertificate WITH SUBJECT = 'New Certificate used for TDE in the Advanture database', EXPIRY_DATE = '2026-02-01'; GO -- Check the certificate is created select name, subject, start_date, expiry_date FROM sys.certificates where name='NewTestCertificate';

Here I created the NewTestCertificate.

1.3. Backup the New Certificate:

To protect the new certificate, back it up along with its private key.

use Master GO BACKUP CERTIFICATE NewTestCertificate TO FILE = 'F:\CertBackup\NewTestCertificate.cer' WITH PRIVATE KEY ( FILE = 'F:\CertBackup\NewTestCertificate.pvk', ENCRYPTION BY PASSWORD = 'UseAStrongPasswordHereToo!£$7Eclipsys' );

1.4. Restore the New Certificate on Secondary Replicas

Since each replica needs the same certificate, manually restore it on each secondary replica.

Copy the backed-up certificate created in section 6.3 to the location accessible by each secondary replica.

Create a certificate on each secondary replica using the backup of the certificate and the corresponding private key, which were copied from the primary replica.

USE master; GO CREATE CERTIFICATE NewTestCertificate FROM FILE = 'F:\CertBackup\NewTestCertificate.cer' WITH PRIVATE KEY (FILE = 'F:\CertBackup\NewTestCertificate.pvk', DECRYPTION BY PASSWORD = 'UseAStrongPasswordHereToo!£$7Eclipsys'); GO

1.5- Rotate the Database Encryption Key (DEK) on the Primary Replica  

The following query shows the certificate used to encrypt each database in SQL Server.

USE master; GO SELECT DB_NAME(db.database_id) DbName, db.encryption_state , encryptor_type, cer.name, cer.expiry_date, cer.subject FROM sys.dm_database_encryption_keys db JOIN sys.certificates cer ON db.encryptor_thumbprint = cer.thumbprint GO

Here is the output of the above query on the primary replica SQLNODE1\TEST instance.

Bind the new certificate to the Database Encryption Key (DEK) by executing an ALTER SYMMETRIC KEY command on the primary replica SQLNODE1\TEST instance.

USE [Test] GO ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE NewTestCertificate; GO

Check that the old certificate is replaced by the new certificate using the follwoing query.

USE [master] GO SELECT DB_NAME(db.database_id) DbName, db.encryption_state , encryptor_type, cer.name, cer.expiry_date, cer.subject FROM sys.dm_database_encryption_keys db JOIN sys.certificates cer ON db.encryptor_thumbprint = cer.thumbprint GO

On the primary instance SQLNODE1\TEST

On the secondary instance SQLNODE2\TEST

2.  Optionally Drop the Old Certificate:

IMPORTANT NOTE: Do not drop the old certificate before taking a transaction log backup of the database. If you need to drop the old certificate, first ensure that you have successfully taken a transaction log backup. Only then should you proceed with dropping the old certificate.

IMPORTANT NOTE: Do not delete the backup files of the old certificate before taking both a full and transaction log backup of your database. If any issues arise, you may need these certificate backup files for recovery.

Use the following command on both the primary and secondary instances. Since the certificate is created at the instance level, not the database level, this change will not be propagated to the secondary replica if executed only on the primary replica.

2.1- On the primary replica SQLNODE1\TESTUSE master; GO DROP CERTIFICATE TestCertificate ; GO

Use the following command to verify that the old certificate has been dropped.

USE master; GO SELECT name AS CertificateName, subject AS Subject, expiry_date AS ExpiryDate FROM sys.certificates WHERE name = 'TestCertificate'; -- Replace with your certificate namee

Note: Repeat steps up to 2.1 on the second replica, SQLNODE2.

Properly rotating TDE encryption keys in an SQL Server Always On environment is a critical task that enhances data security and ensures compliance with internal and external regulations. While the process requires careful planning and coordination across all replicas, following the right steps minimizes risk and avoids service disruption. By implementing regular key rotation as part of your database maintenance and security strategy, you strengthen the overall protection of sensitive data across your high-availability SQL Server infrastructure.