Does SQL Server TDE still work with an expired certificate

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


Problem

I have a SQL Server user defined database encrypted using Transparent Data Encryption (TDE). I have created a certificate with an expiry date and the certificate used in TDE will be expiring soon. Once the certificate expires, will the database stop working?

Solution

Do not panic, a certificate used in TDE will continue to work even after its expiration date. This is because the Database Encryption Key (DEK) in the user database is the key that encrypts the data at rest. DEK is the symmetric key stored in the user database boot record. The certificate which is stored in the master database is used to secure and protect the DEK. Hence, this explains why data at rest encrypted with TDE will still work even after the certificate used in TDE has expired.

We will go through an exercise to show that an expired TDE certificate still operates without issue.

Create an Expired Certificate in SQL Server

To prove that an expired certificate does not impact the data at rest, we will create a certificate with an expiry date in the past. Note that SQL Server will not allow you to create a certificate dated in the past. So, we will manually back date the server date to 1 Jan 2016, and then create a certificate with an expiry date as at 2 Feb 2016.

The EXPIRY_DATE argument is optional and can be specified in any format that can be converted to a date and time.

USE master;
GO 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StR0ngPassw0rd!'; 
GO 
CREATE CERTIFICATE TDECert  
   WITH SUBJECT = 'TDE DEK Certificate',  
   EXPIRY_DATE = '20160202'; 
GO

Once the command above is executed and successful, we will set the server date and time back to current.

When we query today’s date and time from SQL Server and query the certificate expiry; clearly the certificate that we have just created has expired. But we will attempt to use this certificate in our process to encrypt using TDE.

SELECT GETDATE() TodayDateTime
GO
SELECT
name, pvt_key_encryption_type_desc
, issuer_name, subject, expiry_date
FROM sys.certificates
WHERE name = 'TDECert'
GO
Certificate Expired

We will be enabling TDE on a user database name [AGplaceholder]. We will query DMV sys.dm_database_encryption_keys in the context of user database [AGplaceholder] to make sure TDE is not turned on yet. The query does not return any rows, so we are good to go to the next step.

USE [AGplaceholder]
GO
SELECT * FROM sys.dm_database_encryption_keys
GO
Database not encrypted in TDE

Enabling SQL Server TDE with an Expired Certificate

We will now turn on TDE on database [AGplaceholder] using the expired TDECert certificate. When we query DMV sys.dm_database_encryption_keys again, we can confirm that TDE is turned on without any issue.

USE [AGplaceholder]
GO 
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert; 
GO 
ALTER DATABASE [AGplaceholder] 
SET ENCRYPTION ON; 
GO 
WAITFOR DELAY '00:00:05'
GO
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
Database encrypted in TDE

Any DDL or DML issued against database [AGplaceholder] will run just fine.

DDL and DML executes successfully

Backup Certificates in SQL Server

Make sure you have a policy to back up a TDE certificate after any certificate creation as this is required to restore the database to another SQL Server instance.

Here is the T-SQL to create a backup.

USE [master]br />GO
BACKUP CERTIFICATE TDECert
TO FILE = '\\SQLP2\temp\TDECert.cer'
WITH PRIVATE KEY (FILE = '\\SQLP2\temp\TDECert.pvk',
ENCRYPTION BY PASSWORD = 'str0ngPa$$w0rd');
GO

When you create a certificate on another SQL Server based on the backup of this expired certificate, you will get a Warning message stating the certificate has expired. But this will still allow you to restore your TDE database to that other SQL Server instance successfully despite the Warning message.

Certificate expired warning message - Description: This message appears when you try to restore the expired certificate to another SQL Server instance

Conclusion

This tip highlights a technical fact that the certificate used in TDE does not honor the certificate expiry date and TDE will continue to work.

This does not mean you should completely ignore the message and you should replace the certificate periodically. The next part of my tip will demonstrate how you can periodically replace a certificate used in TDE on databases involved in HADR.

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, January 11, 2023 - 6:22:16 PM - Simon Liew Back To Top (90823)
Hi Edward, yep, the cert backup can be used even after expiry.

Wednesday, January 11, 2023 - 5:36:49 AM - Edward O Farrell Back To Top (90820)
Thank you for this article .. Very useful and well written. I just wanted to confirm that backups taken before the certificate expiry can still be
restored after the cert has expired ?














get free sql tips
agree to terms