Rotate a SQL Server TDE Certificate (Replacing an Expiring Database Encryption Certificate)

 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.