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.
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.
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.
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' );
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
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
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.
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.