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.
However the restoration is failing with the error:
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)
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:
But then the same error appears with database backup - asking for the "old" certificate again:
I decrypted the database and re-encrypted. The decryption was successful only on second attempt: