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

 

Updating an expired SQL Server TDE certificate


By:   |   Read Comments   |   Related Tips: 1 | 2 | More > Encryption

Attend these FREE MSSQLTips webcasts >> click to register


Problem

If you read my first tip on expired TDE certificates, you know that a database can still work even after the certificate used for TDE has expired. Now you might be wondering how you can generate a new certificate to replace the previous certificate and the steps if the database is in an Availability Group.

Solution

If you want to replace the existing certificate used in TDE, you first need to create a new certificate. The command is the same as creating the TDE certificate for the first time, except you now provide a different certificate name.

USE [master]
GO

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

Next you need to issue an ALTER SYMMETRIC KEY command in the context of the user database (AGplaceholder in our example) to bind the newly created certificate to the Database Encryption Key (DEK).

If a database encryption key has been modified twice, a log backup must be performed before the database encryption key can be modified again. In our tip, it is the first time we are modifying the encryption key, so the command below will work fine to bind the DEK to the certificate.

USE [AGplaceholder]
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE NewTDECert;
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
DEK is bounded to new certificate

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.

Here is the command to drop the old certificate.

USE [master]
GO

DROP CERTIFICATE TDECert;
GO 

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 

TDE Databases in an Availability Group

If your database is involved in any HA or DR, the new certificate needs to be restored to all the secondary SQL Server instances. In our scenario, database [AGplaceholder] is involved in an Availability Group (AG). The AG dashboard will show the Synchronizing State as Not Synchronizing as soon as the new certificate was bound on the primary AG replica. You will not be able to RESUME the AG synchronization until the new certificate is restored to the secondary replicas.

Databases in secondary replicas not synchronizing

There is no RESTORE CERTIFICATE command per se. Restoring a certificate to another SQL Server instance means you create the certificate from a backup of the certificate.

We have already backed up the new certificate and the private key associated with the certificate on the primary AG replica. Now we will restore the certificate to the secondary SQL Server instance SQLP2 from the backup and manually resume the AG synchronization.

The command below is executed on the secondary AG replica SQLP2. We can either resume the AG from SSMS or issue a RESUME from a query window as shown below.

USE [master]
GO

CREATE CERTIFICATE NewTDECert
FROM FILE = '\\SQLP2\temp\NewTDECert.cer'
WITH PRIVATE KEY (FILE = '\\SQLP2\temp\NewTDECert.pvk',
DECRYPTION BY PASSWORD = 'str0ngPa$$w0rd');
GO

ALTER DATABASE [AGplaceholder] SET HADR RESUME;
GO

You do not need to perform a backup and restore of the database using TDE again, the synchronization can resume after the certificate is restored. You will not get a Warning message about certificate expiration, because the expiration date for the new certificate is in the future.

Database resume synchronization

Conclusion

It is a good practice for an organization to have a policy in place to replace certificates used for TDE on a set interval as part of security risk mitigation. It is important to note that every generated certificate must be backed up and kept in line with the associated database backups. Should an old certificate backup be deleted and you want to restore a database backup associated with that older certificate, it would be impossible.

Next Steps


Last Update:


signup 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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools