Updating an expired SQL Server TDE certificate
By: Simon Liew | Updated: 2017-08-08 | Comments (2) | Related: 1 | 2 | More > Encryption
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.
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
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.
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.
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.
- Check out the following resources:
Last Updated: 2017-08-08
About the author
View all my tips