1

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.

1

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.

  • Thank you Scott, which clearly means that the older backup will stop working on the same database after we change expiry date and in case, we need older backups to work, first we have to load the older certificate(which will work because expiry date doesn't stop this from functioning) and then only we can restore them. Please correct me, if I am wrong. – Learning_DBAdmin Jul 29 '18 at 8:40
  • You need to keep the old certificate on the server to be able to restore older backups - there shouldn’t be any problems with having both the old and new certificates at the same time - just call the different names – Scott Hodgin Jul 29 '18 at 9:07
1

The old one will still work even if expired. So the best thing to do is to create a new certificate and keep the old one on the server so restores of the server before you change the encryption can be made.

 CREATE CERTIFICATE NEWCert 
 WITH SUBJECT = 'New Certificate',
 EXPIRY_DATE = '20280101';
 GO

Next you execute ALTER SYMMETRIC KEY in the context of the user database to bind the newly created certificate to the Database Encryption Key (DEK).

If you are making multiple changes to the certificate you need to make log backups after each change

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

If you check the certificate binding now, you will see the DEK is now bound to the new certificate.

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 

Then backup the certificate and make new backups of the database

Don't drop the old certificate as you might need to restore older versions of the database but if you want/need to run DROP CERTIFICATE OldCert;

As long as the old certificate exists on the server you can restore the database from old backups and you can copy the cert to your secondary server even if it has expired.

  • Thanks a lot for your explanation however If I have understood your answer correctly, we can't restore old backup of database on the same user database for which certification has been changed in terms of expiry date because, the moment we create a new certificate, the underlying hexcode(in sys.certificates table) i.e. thumbprint changes. Kindly let me know in case we are not on the same page. – Learning_DBAdmin Jul 29 '18 at 8:37
  • 1
    You will always have to create an new cert and keep the old. If you have both you can restore. But after encrypting with a new cert you will need to make a new full backup – Spörri Jul 29 '18 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.