4

I'm trying to restore the backup of database which has Transparent Data Encryption enabled. I have verified the certificate thumbprint in the backup file is the same as TDE certificate in [master] database.

TDEThumbprint

TDE certificate:

enter image description here

However the restoration is failing with the error:

enter image description here

It is requesting the old certificate. Note that we changed TDE certificate some time ago and the database master key was encrypted with the new certificate using the alter database command:

USE [db];
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE [New_Certificate];
GO

The old certificate was still in [master] database when the backup was made and it was removed some time after.

This looks like a bug.

Any explanations, suggestions?

version: Microsoft SQL Server 2016 (SP3-OD) (KB5006943) - 13.0.6404.1 (X64)

[EDIT1]:

I restored the "old" certificate from backup and the database restore was successful. Checked the encryption status - database was protected with the "new" certificate as expected and I dropped the "old" certificate: enter image description here

But then the same error appears with database backup - asking for the "old" certificate again:

enter image description here

[EDIT2]:

I decrypted the database and re-encrypted. The decryption was successful only on second attempt:

enter image description here

Improve this question
3
  • Hi! I edited the question and added more info.
    – Mar71ns
    Commented Feb 21, 2022 at 15:20
  • 2
    It's not a bug. There are still log items protected by the old cert which is why you need it.
    – Sean Gallardy
    Commented Feb 21, 2022 at 20:23
  • Why would SQL Server allow me to drop a certificate if it is still protecting some data?
    – Mar71ns
    Commented Feb 21, 2022 at 21:50

1 Answer 1

Reset to default
2

Looks like the problem was caused by some transaction log records encrypted with the "old" and dropped certificate. I could not truncate the transaction log until I decrypted an re-encrypted the database. I assume this is a bug, because SQL Server should not allow to drop a certificate if still being used anywhere.

Improve this answer
4
  • 1
    Still not a bug, no matter how much you want it to be. It's in the documentation. learn.microsoft.com/en-us/troubleshoot/sql/admin/…
    – Sean Gallardy
    Commented Feb 22, 2022 at 14:51
  • 1
    Oh, nice, a bug is not considered a bug if you document it :D I would call it "known bug".
    – Mar71ns
    Commented Feb 22, 2022 at 15:30
  • Calling something a bug because it doesn't work the way you want it to does not make it a bug. I would urge you to understand the actual working of the technology before calling things bugs, otherwise it shows your ignorance.
    – Sean Gallardy
    Commented Feb 23, 2022 at 14:30
  • Yeah, let's call it a feature, not the bug, right? :D A feature, which could lead to lost database. But, of course, it would be DBA's fault, because - always backup your keys and keep them forever :)
    – Mar71ns
    Commented Feb 23, 2022 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.