Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Does SQL Server TDE still work with an expired certificate


By:   |   Last Updated: 2017-07-25   |   Comments   |   Related Tips: 1 | 2 | More > 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


Last Updated: 2017-07-25


next webcast button


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools