Updating an expired SQL Server TDE certificate


By:   |   Updated: 2017-08-08   |   Comments (2)   |   Related: 1 | 2 | More > 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
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]
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.

Database resume synchronization

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


Last Updated: 2017-08-08


get scripts

next tip button



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.

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 05, 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.

 



download





Recommended Reading

Does SQL Server TDE still work with an expired certificate

SQL Server Column Level Encryption Example using Symmetric Keys

Encrypting and Decrypting SQL Server Stored Procedures, Views and User-Defined Functions

Storing passwords in a secure way in a SQL Server database

Where Does SQL Server Store Its Certificates








get free sql tips
agree to terms


Learn more about SQL Server tools