Does SQL Server TDE still work with an expired certificate
By: Simon Liew | Updated: 2017-07-25 | Comments | Related: 1 | 2 | More > Encryption
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?
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.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StR0ngPassw0rd!';
CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE DEK Certificate',
EXPIRY_DATE = '20160202';
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
, issuer_name, subject, expiry_date
WHERE name = 'TDECert'
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.
SELECT * FROM sys.dm_database_encryption_keys
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.
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;
ALTER DATABASE [AGplaceholder]
SET ENCRYPTION ON;
WAITFOR DELAY '00:00:05'
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
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');
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.
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.
- Transparent Data Encryption (TDE)
- CREATE CERTIFICATE (Transact-SQL)
- Implementing Transparent Data Encryption in SQL Server 2008
Last Updated: 2017-07-25
About the author
View all my tips