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.
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.
Update Certificate
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.
Next you need to issue an ALTER SYMMETRIC KEY command in the context of the user database 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.
Always On
If your database is involved in any HA or DR, the new certificate needs to be restored to all the secondary SQL Server instances. 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.
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.
Sources:
https://www.mssqltips.com/sqlservertip/5009/updating-an-expired-sql-server-tde-certificate
Comments