Rotate a SQL Server TDE Certificate (Replacing an Expiring Database Encryption Certificate)
Introduction
SQL Server certificates are an essential part of database security. They are commonly used for:
- Transparent Data Encryption (TDE)
- Backup Encryption
- Service Broker
- Database Encryption Keys
- Protecting sensitive data
Like SSL certificates, SQL Server certificates also have an expiration date. However, an expired TDE certificate does NOT stop the database from working. The certificate is still valid for encrypting and decrypting the Database Encryption Key (DEK). Organizations usually rotate certificates to meet security or compliance requirements.
Since SQL Server does not allow modifying an existing certificate, the correct approach is to create a new certificate and update the database to use it.
This article explains the complete process.
Step 1 – Check Existing Certificates
Run the following query to list all certificates and verify their expiration dates.
USE master;
GO
SELECT
name AS CertificateName,
certificate_id,
subject,
issuer_name,
expiry_date,
pvt_key_encryption_type_desc
FROM sys.certificates;
GO
Review the certificate that is currently being used for TDE.
Step 2 – Check Which Databases Are Encrypted
SELECT
DB_NAME(database_id) AS DatabaseName,
encryption_state,
encryptor_thumbprint
FROM sys.dm_database_encryption_keys;
GO
To identify the certificate associated with the thumbprint:
SELECT
name,
thumbprint
FROM sys.certificates;
GO
Step 3 – Backup the Existing Certificate
Always back up the certificate before making any changes.
BACKUP CERTIFICATE TDECert_2022
TO FILE = 'D:\SQLBackup\TDECert_2022.cer'
WITH PRIVATE KEY
(
FILE = 'D:\SQLBackup\TDECert_2022.pvk',
ENCRYPTION BY PASSWORD = 'StrongPassword'
);
GO
Important
Keep the following files in a secure location:
Certificate (.cer)
Private Key (.pvk/.key)
Password used during backup
Without these files, you cannot restore TDE encrypted database backups on another SQL Server.
Step 4 – Create a New Certificate
Create a replacement certificate.
USE master;
GO
CREATE CERTIFICATE TDECert_2025
WITH SUBJECT = 'SQL Server Database Encryption Certificate',
EXPIRY_DATE = '2030-12-31';
GO
You can use any meaningful naming convention such as:
TDECert_2025
SQLTDE_Prod
Company_TDE_2025
Step 5 – Rotate the Database Encryption Key
Update the database to use the new certificate.
USE YourDatabaseName;
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE TDECert_2025;
GO
What happens?
No downtime
Database remains online
Only the Database Encryption Key (DEK) is re-encrypted
Database data pages are NOT re-encrypted
The operation typically completes within a few seconds.
Step 6 – Verify the New Certificate
Verify that the database encryption is still active.
SELECT *
FROM sys.dm_database_encryption_keys;
GO
Verify the new certificate exists.
SELECT
name,
expiry_date
FROM sys.certificates;
GO
Step 7 – Backup the New Certificate
After successful rotation, immediately back up the new certificate.
BACKUP CERTIFICATE TDECert_2025
TO FILE = 'D:\SQLBackup\TDECert_2025.cer'
WITH PRIVATE KEY
(
FILE = 'D:\SQLBackup\TDECert_2025.pvk',
ENCRYPTION BY PASSWORD = 'AnotherStrongPassword'
);
GO
Store the certificate securely with your Disaster Recovery documentation.
Step 8 – Remove the Old Certificate (Optional)
Only remove the old certificate if:
All databases have been migrated to the new certificate.
Old encrypted backups are no longer required.
Certificate backups have been archived safely.
USE master;
GO
DROP CERTIFICATE TDECert_2022;
GO
Warning: Do NOT delete the old certificate if you still need to restore older TDE encrypted backups. SQL Server requires the original certificate to restore those backups.
Best Practices
Always back up certificates before making changes.
Store certificate backups in a secure location.
Test certificate rotation in a lower environment first.
Keep both old and new certificates until all backup retention periods have expired.
Document certificate names, passwords, and backup locations.
Verify encryption status after rotation.
Include certificate backups as part of your Disaster Recovery strategy.
Common Misconceptions
Myth: An expired TDE certificate stops SQL Server encryption.
Fact: No. SQL Server continues using the certificate even after expiration.
Myth: Rotating the certificate re-encrypts the entire database.
Fact: No. Only the Database Encryption Key (DEK) is re-encrypted.
Myth: The old certificate can be deleted immediately.
Fact: No. Keep the old certificate until all backups encrypted with it are no longer needed.
Summary
Rotating a SQL Server TDE certificate is a simple and safe process:
Check the existing certificate.
Back up the existing certificate and private key.
Create a new certificate.
Update the Database Encryption Key (DEK) to use the new certificate.
Verify encryption status.
Back up the new certificate.
Retain the old certificate until all encrypted backups have expired.
Following these steps ensures your SQL Server environment remains secure, compliant, and fully recoverable.