Change Expiry Date of TDE Certificate Without Changing Thumbprint in SQL Server

backupsql servertransparent-data-encryption

I have been searching through internet on how to change expiry date of TDE certification of a user database at SQL Server 2014 SP2 and got so many articles and most of them are suggesting to drop and create another certificate with a different expiry date.

My concern is regarding backup of Database which was taken before changing Expiry Date. When I drop certificate and create a new one with different expiry date, thumbprint of certificate changes and hence unable to restore the backup which was taken prior to change.

Below is the error screen-shot:

TDE Restoration Error

Appreciate if I can get some expert advise on this issue with details.

Best Answer

Referencing the information in Updating an expired SQL Server TDE certificate, the post explains that it is easy to create a new certificate with an updated expiration date for use in TDE.

USE [master]
GO

CREATE CERTIFICATE NewTDECert
WITH SUBJECT = 'New TDE DEK Certificate',
EXPIRY_DATE = '20181231';
GO 

USE [YourDatabase]
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE NewTDECert;
GO

After executing the above commands, your database now uses the new certificate with regards to TDE.

However - the post makes it very clear that you still need to keep the old certificate in case you want to restore the database from a backup created when the old certificate was being used.

From that post (highlighting mine):

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.

Additonally (from that post),

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

Additional information can be found in Replacing an expiring SQL Server encryption key. (Highlighting mine).

To rotate the certificate for TDE, add the new certificate as above, then execute the command ALTER DATABASE ENCRYPTION KEY with the ENCRYPTION BY SERVER CERTIFICATE clause:

ALTER DATABASE ENCRYPTION KEY
    ENCRYPTION BY SERVER CERTIFICATE newCertificate;

SQL Server re-encrypts the database encryption key with the new certificate, and drops the encryption by the old certificate when it’s finished. And as before, the data itself isn’t re-encrypted, so the process finishes almost immediately.

One caution: Always keep at least one backup copy of every certificate you use. If you ever need to restore a database that used encryption, you’ll need the certificate that was in effect at the time the backup was created. Make a habit of creating a certificate backup immediately after creating it in SQL Server. Store the backup in a safe place; also keep a copy of the passphrase you use to encrypt the certificate backup, preferably in a different safe place for security. Retain these forever, or until the last database backup that may possibly use them has been purged.

Related Question