Updating an expired SQL Server TDE certificate

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


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.


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]

WITH SUBJECT = 'New TDE DEK Certificate',
EXPIRY_DATE = '20181231';

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]

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

USE [master]
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
DEK is bounded to new certificate

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]


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]

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

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]

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


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.

Database resume synchronization


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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Simon Liew 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

Comments For This Article

Wednesday, October 10, 2018 - 9:02:49 AM - Jose Gallardo Back To Top (77914)

Dear Simon Liew

I have a server whose certificate TDE has expired and does not allow me to take backups. The message that shows me is the following: The certificate specified for backup encryption has Expired. I would like to update the expiration date and continue to generate BACKUPS. If the current certificate expired how can I generate a new one? What is the best solution.

Thank you for your attention.

Monday, February 5, 2018 - 5:34:42 AM - Jomadar Back To Top (75100)

Thank you for the guide. Just  a short notice: in AG I would 1st copy the certs and then run the ALTER DATABASE ENCRYPTION KEY to completely avoid AG sync disruption. On the other hand your method helps to understand the topic better.


get free sql tips
agree to terms