Rotating the TDE Certificate

A TDE-protected database can be backed up perfectly and still be impossible to restore without its key chain. Rotating the TDE certificate is a recovery operation as much as a security change.

Know Which Key Protects What

Transparent Data Encryption encrypts database files using a database encryption key. That key is protected by a server certificate or other supported protector. Rotating the certificate that protects the database encryption key does not automatically rewrite every data page. It changes the protection around the key. Other operations, such as rotating the database encryption key algorithm, have different effects and costs.

I explain this distinction before anyone schedules a huge maintenance window. The certificate operation still deserves care because recovery depends on the certificate and private key. A fast command can create a slow disaster if the new certificate backup is forgotten. Map the current protector before changing it.

Inventory Current Protection

Query sys.dm_database_encryption_keys to see encrypted databases, state, and encryptor information. Join to sys.databases for readable names. Record the current certificate thumbprint and the database list. Check availability group and restore targets that need the certificate. A database can be encrypted but temporarily show a state associated with a transition, so read the documented numeric state rather than a single bit.

I take the inventory on every relevant instance. A restore server can need certificates from more than one generation of backups. Keep old protectors until the retention and restore plan says they are no longer needed. The catalog snapshot supports that decision later.

SELECT d.name AS database_name,
       dek.encryption_state,
       dek.encryptor_type,
       dek.encryptor_thumbprint
FROM sys.dm_database_encryption_keys AS dek
JOIN sys.databases AS d
  ON d.database_id = dek.database_id
ORDER BY d.name;

Understand Certificate Expiration Before Rotating the TDE Certificate

A certificate’s expiration date is part of its metadata, but an expired TDE certificate does not automatically stop an already encrypted database from working. Restore requirements and organizational policy still demand active management. Do not confuse the date with a simple on-off switch. Check the current documentation and your security policy for how certificates are governed.

I plan rotation before anyone loses track of the private key or backup location. A calendar reminder tied to an owner is more useful than discovering the expiration during an audit. The key question is whether the team can restore every retained backup under the current recovery plan. Test that answer.

Create and Back Up the New Protector

Create the new certificate in master under the approved key hierarchy, then back it up with its private key to a protected location. Use a strong password for the private-key backup and store it separately under the credential policy. Protect the files from broad access and test that the target recovery instance can import them. Do this before switching any database to the new protector.

I treat the backup as a required step, not a cleanup item after the change. The example illustrates syntax with placeholder paths and password. Replace them with reviewed Windows locations and secure handling. Never leave a real password in a script saved for general access.

USE master;
CREATE CERTIFICATE [TDE_Rotation_2026]
WITH SUBJECT = 'TDE protector rotation';

BACKUP CERTIFICATE [TDE_Rotation_2026]
TO FILE = 'D:\SecureBackup\TDE_Rotation_2026.cer'
WITH PRIVATE KEY
(
    FILE = 'D:\SecureBackup\TDE_Rotation_2026.pvk',
    ENCRYPTION BY PASSWORD = 'ReplaceWithSecurePassword'
);

Switch the Database Protector

ALTER DATABASE ENCRYPTION KEY changes which certificate protects the database encryption key. Execute it in the target database context after the new certificate and backup are verified. Plan for appropriate permissions and confirm the database list so no encrypted database is omitted. Repeat for each intended database and verify the encryptor thumbprint afterward.

I make this a scripted, reviewed change. The command is short, but a wrong target or missing certificate backup can affect recovery. Keep the old certificate during the transition. The example names a placeholder database. Replace it only after the inventory and backup steps are complete.

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

Preserve Older Recovery Paths When Rotating the TDE Certificate

Older backups can require the old certificate and private key. Keep them through the full backup retention period and any archive requirements. Do not drop the old certificate merely because current databases point to the new one. Test a restore from a backup taken before the rotation and one taken afterward on an isolated recovery instance.

I have seen key archives treated as clutter because no current database referred to an old thumbprint. Backup history still did. The restore test settles that question. Record which certificate belongs to which backup era and where its protected private-key copy lives. Recovery documentation should make the relationship obvious to the next DBA.

Check High Availability and Copies When Rotating the TDE Certificate

Availability replicas, log shipping targets, and disaster recovery servers can need the protector before they can restore or join encrypted databases. Inventory every location that receives encrypted backups or databases. Import the certificate and private key through the approved secure process, then test the operation. Do not distribute private keys more widely than required.

I include failover and restore paths in the plan for rotating the TDE certificate. A primary database staying online is necessary but insufficient. The point of encryption is to protect stored data; the point of recovery is to make authorized data available after failure. Both goals need testing on the actual target environment.

Audit the Change and Key Storage

Record certificate name, thumbprint, creation date, backup location, private-key custody, databases switched, and restore-test results. Restrict access to the key archive and monitor changes. A key that exists only on one server is a single point of recovery failure. A key copied to every shared folder is a security failure. Find the controlled middle path.

I ask a second person to locate the certificate backup using the documented procedure, without exposing the secret. If they cannot, the procedure is incomplete. The ability to recover should not depend on the memory of whoever ran the rotation.

Close Only After a Restore Test

Verify the new protector in metadata and restore a recent encrypted backup using the new certificate. Also verify an older backup with the old certificate if it remains in retention. Keep the old protector until those requirements expire and policy authorizes removal. Set the next review date before closing the change.

What backup would you need to restore if the primary server failed tomorrow? Use that file in the test. Rotating the TDE certificate is successful when current encryption continues and the documented recovery path still works. The catalog change alone proves only part of the job.

Related reading on this blog: Transparent Data Encryption and Frequently Asked Questions and AlwaysOn AG (Availability Group) and TDE Error: Please Create a Master Key.

A TDE certificate rotation is not complete when the command succeeds, it is complete when restores still work.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.