The Ghost of TDE Past: When a One-Year-Old Certificate Still Broke My Restore

I ran into one of the strangest SQL Server issues I've seen in a long time, and it ended up teaching me a lesson about TDE that I won't forget.

The Setup

About a year ago, the environment rotated from an old TDE certificate (TDECert_2024) to a newer certificate (TDECert_2025). Everything appeared to be fine:

• Databases were online.
• Backups were running.
• sys.dm_database_encryption_keys showed the databases using TDECert_2025.
• The old certificate had seemingly been retired.

Fast forward a year.

I was restoring a database as part of a migration project and received this error:

Cannot find server certificate with thumbprint
'0xEB92...2A10'

The thumbprint belonged to the old 2024 certificate.

My first reaction was:

> "That can't be right. The database hasn't used that certificate in a year."

I was wrong.

First Stop: Check the Current TDE Certificate

I ran:

SELECT
    DB_NAME(dek.database_id) AS database_name,
    cert.name,
    cert.thumbprint
FROM sys.dm_database_encryption_keys dek
LEFT JOIN master.sys.certificates cert
    ON dek.encryptor_thumbprint = cert.thumbprint;

Everything pointed to TDECert_2025.

No sign of the old certificate.

Next Thought: Maybe the Backup Is Encrypted

I checked:

RESTORE HEADERONLY
FROM DISK = 'backupfile.bak';

The results showed:

EncryptorThumbprint = NULL
EncryptorType = NULL

The backup itself was not encrypted.

That made the situation even stranger.

Maybe I'm Restoring the Wrong File?

I considered:

• Wrong backup file
• Multiple backup sets in the media
• Old backup accidentally being used

I verified:

• WITH INIT
• Brand-new backup filename
• RESTORE VERIFYONLY

Everything checked out.

Still getting the same error.

Let's Change the Certificate Again

I created a brand-new certificate and ran:

ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE TDECert_2026;

Took another backup.

Restored again.

This time SQL Server complained that the 2026 certificate was missing.

Progress!

I restored the 2026 certificate to the target and tried again.

Then SQL Server immediately complained about the old 2024 certificate.

At this point I was convinced I had found a bug.

Nuclear Option: Remove TDE Entirely

I turned encryption off:

ALTER DATABASE Migration SET ENCRYPTION OFF;

Waited for decryption.

Dropped the DEK:

DROP DATABASE ENCRYPTION KEY;

Verified:

SELECT is_encrypted
FROM sys.databases
WHERE name = 'Migration';

Result:

is_encrypted = 0

Perfect.

Took another backup.

Restore still wanted the 2024 certificate.

Now I was officially losing my mind.

The Big Clue

During restore I noticed something interesting:

Processed xxxx pages for database 'Migration'
Processed xxxxxx pages for database 'Migration_log'

Cannot find server certificate with thumbprint
'0xEB92...2A10'

The restore was failing after reading the backup and during recovery.

That pointed me toward the transaction log.

Why Won't the Log Shrink?

I checked:

SELECT name, log_reuse_wait_desc
FROM sys.databases;

The answer:

REPLICATION

Interesting.

I tried:

EXEC sp_removedbreplication;

Still:

REPLICATION

I backed up the log.

Still:

REPLICATION

The error message finally gave it away:

The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture.

Bingo.

The Real Culprit

The database still had old log records hanging around because of replication/CDC.

Those old log records had been generated when the database was still protected by TDECert_2024.

Even though:

• The database was no longer encrypted.
• The DEK was gone.
• The current certificate was different.
• The TDE certificate swap had happened a year earlier.

Those ancient log records were still sitting in the active portion of the transaction log and were still dependent on the old certificate.

The Fix

Once I cleared the replication/CDC dependency and allowed the log to truncate, the old log records were finally removed.

I then:

1. Took a brand-new full backup.

2. Restored it.

3. Success.

No more dependency on TDECert_2024.

I then re-applied TDECert_2025 and moved on with the migration.

Lessons Learned

1. A TDE certificate swap does not instantly eliminate dependencies on old certificates.

2. The transaction log can preserve old TDE dependencies for a very long time.

In my case:

> One year.

3. sys.dm_database_encryption_keys only tells you the current protector.

It does not tell you about historical dependencies buried inside the transaction log.

4. A database can be completely decrypted and still require an old certificate during restore.

I certainly wasn't expecting that.

5. Never delete old TDE certificates until you've proven you no longer need them.

Even if:

• The database isn't encrypted.
• The DEK is gone.
• The certificate was rotated a year ago.

SQL Server may still have unfinished business with that certificate.

Final Thoughts

I've worked with TDE for many years and this one had me scratching my head for hours.

I never would have guessed that a one-year-old certificate dependency could still be lurking inside the transaction log, surviving certificate rotations, decryption, and even dropping the database encryption key.

SQL Server always has a way of humbling you.