Does SQL Server TDE still work with an expired certificate

By: Simon Liew   |   Updated: 2017-07-25   |   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
   

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
   

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
   

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

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.

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

• Transparent Data Encryption (TDE)
• CREATE CERTIFICATE (Transact-SQL)
• Implementing Transparent Data Encryption in SQL Server 2008

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