Updating an expired SQL Server TDE certificate

By: Simon Liew | Comments (2) | Related: 1 | 2 | > Encryption

Problem

If you read my first tip on expired TDE certificates, you know that a database can still work even after the certificate used for TDE has expired. Now you might be wondering how you can generate a new certificate to replace the previous certificate and the steps if the database is in an Availability Group.

Solution

If you want to replace the existing certificate used in TDE, you first need to create a new certificate. The command is the same as creating the TDE certificate for the first time, except you now provide a different certificate name.

USE [master]
GO

CREATE CERTIFICATE NewTDECert
WITH SUBJECT = 'New TDE DEK Certificate',
EXPIRY_DATE = '20181231';
GO

Next you need to issue an ALTER SYMMETRIC KEY command in the context of the user database (AGplaceholder in our example) to bind the newly created certificate to the Database Encryption Key (DEK).

If a database encryption key has been modified twice, a log backup must be performed before the database encryption key can be modified again. In our tip, it is the first time we are modifying the encryption key, so the command below will work fine to bind the DEK to the certificate.

USE [AGplaceholder]
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE NewTDECert;
GO

If you check the certificate binding now, you will see the DEK is now bound to the new certificate.

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

It is up to you whether you want to drop the old certificate from the SQL Server instance. You should always keep a backup of the old certificate in case you need to restore a TDE enabled database using an older backup that used the old key.

Here is the command to drop the old certificate.

USE [master]
GO

DROP CERTIFICATE TDECert;
GO

It is paramount to backup the TDE certificate after any certificate changes as this is required to restore the database to another SQL Server instance. We can issue a backup certificate command for the new certificate as shown below.

USE [master]
GO

BACKUP CERTIFICATE NewTDECert
TO FILE = '\\SQLP2\temp\NewTDECert.cer'
WITH PRIVATE KEY (FILE = '\\SQLP2\temp\NewTDECert.pvk',
ENCRYPTION BY PASSWORD = 'str0ngPa$$w0rd');
GO

TDE Databases in an Availability Group

If your database is involved in any HA or DR, the new certificate needs to be restored to all the secondary SQL Server instances. In our scenario, database [AGplaceholder] is involved in an Availability Group (AG). The AG dashboard will show the Synchronizing State as Not Synchronizing as soon as the new certificate was bound on the primary AG replica. You will not be able to RESUME the AG synchronization until the new certificate is restored to the secondary replicas.

Databases in secondary replicas not synchronizing

There is no RESTORE CERTIFICATE command per se. Restoring a certificate to another SQL Server instance means you create the certificate from a backup of the certificate.

We have already backed up the new certificate and the private key associated with the certificate on the primary AG replica. Now we will restore the certificate to the secondary SQL Server instance SQLP2 from the backup and manually resume the AG synchronization.

The command below is executed on the secondary AG replica SQLP2. We can either resume the AG from SSMS or issue a RESUME from a query window as shown below.

USE [master]
GO

CREATE CERTIFICATE NewTDECert
FROM FILE = '\\SQLP2\temp\NewTDECert.cer'
WITH PRIVATE KEY (FILE = '\\SQLP2\temp\NewTDECert.pvk',
DECRYPTION BY PASSWORD = 'str0ngPa$$w0rd');
GO

ALTER DATABASE [AGplaceholder] SET HADR RESUME;
GO

You do not need to perform a backup and restore of the database using TDE again, the synchronization can resume after the certificate is restored. You will not get a Warning message about certificate expiration, because the expiration date for the new certificate is in the future.

Conclusion

It is a good practice for an organization to have a policy in place to replace certificates used for TDE on a set interval as part of security risk mitigation. It is important to note that every generated certificate must be backed up and kept in line with the associated database backups. Should an old certificate backup be deleted and you want to restore a database backup associated with that older certificate, it would be impossible.

Next Steps

Check out the following resources:
- ALTER DATABASE ENCRYPTION KEY (Transact-SQL)
- Configure a SQL Server Database using Transparent Data Encryption and Database Mirroring

About the author

Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips