Cannot restore log of MS SQL database that was previously TDE encrypted (but is not currently encrypted)

Question

I've run into an odd problem with MS SQL Server. I'm working on automating deployment of TDE and want to be able to make sure that I can still do all the things I expect to be able to do with an encrypted database - one of which is decrypting it and not having to keep the certificate around for future operations.

So I've got a simple script that replicates the challenge I'm facing: I can't restore the log anywhere that doesn't have the Cert even after the database is fully decrypted and has had its cert removed.

The script to reproduce the error is below. Note that it mucks with your master database master key so don't run this on an existing Server you like.

-- SETUP ENCRYPTION CERT --
USE MASTER
IF (select Count(*) from sys.symmetric_keys where name like '%DatabaseMasterKey%') = 0
BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password12345!';
END

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password12345!'

CREATE CERTIFICATE [TestDatabase_EncryptionCertificate]
WITH SUBJECT = 'Encryption certificate for database TestDatabase';
GO

-- SETUP TEST DATABASE --
DROP DATABASE IF EXISTS [TestDatabase]
CREATE DATABASE [TestDatabase] 
ALTER DATABASE [TestDatabase]  SET RECOVERY FULL;
BACKUP DATABASE [TestDatabase] TO DISK = N'c:\temp\TestDatabaseFullBackup.bak'

GO

USE [TestDatabase]
CREATE TABLE Example (ExampleColumn NVARCHAR(50))
GO

USE [TestDatabase]
SELECT 'Test Database Created...'
INSERT INTO Example VALUES ('Success')
SELECT * FROM Example
GO

-- ENCRYPT AND DECRYPT TEST DATABASE --
USE [TestDatabase]
CREATE DATABASE ENCRYPTION KEY
    WITH Algorithm = AES_128
    ENCRYPTION BY Server Certificate [TestDatabase_EncryptionCertificate]
;

GO

USE master
ALTER DATABASE [TestDatabase] SET ENCRYPTION ON
WAITFOR DELAY '00:00:05'
GO

USE [TestDatabase]
SELECT 'Test Database Encrypted...'
SELECT * FROM Example

GO

USE master
ALTER DATABASE [TestDatabase] SET ENCRYPTION OFF
WAITFOR DELAY '00:00:05'
GO

-- DELETE CERTIFICATE --
USE [TestDatabase]
DROP DATABASE ENCRYPTION KEY
USE master
DROP CERTIFICATE [TestDatabase_EncryptionCertificate]

GO

USE [TestDatabase]
SELECT 'Test Database Decrypted...'
SELECT * FROM Example
GO

-- Random crap tried to flush the encryption out of the log
USE master
BACKUP DATABASE [TestDatabase] TO DISK = N'c:\temp\TestDatabaseFullBackup.bak'
CHECKPOINT
GO

-- BACKUP, DROP, AND RESTORE --
USE master
BACKUP DATABASE [TestDatabase] TO  DISK = N'c:\temp\TestDatabase.bak' WITH COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5 
BACKUP LOG [TestDatabase] TO  DISK = N'c:\temp\TestDatabase.trn' WITH COPY_ONLY, NOFORMAT, INIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
DROP DATABASE [TestDatabase]

GO

RESTORE DATABASE [TestDatabase] FROM  DISK = N'c:\temp\TestDatabase.bak' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [TestDatabase] FROM  DISK = N'c:\temp\TestDatabase.trn' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
WAITFOR DELAY '00:00:05'

GO

USE [TestDatabase]
SELECT 'Test Database Restored...'
SELECT * FROM Example

This script succeeds until it reaches the RESTORE LOG step, where it fails with

Msg 33111, Level 16, State 3, Line 79
Cannot find server certificate with thumbprint '0x9C2778AD59EC99D38F870ACE18D40FD4A259872C'.
Msg 3013, Level 16, State 1, Line 79
RESTORE LOG is terminating abnormally.
Msg 927, Level 14, State 2, Line 84
Database 'TestDatabase' cannot be opened. It is in the middle of a restore.

So what do I do? We use RESTORE LOG to set up seeded HADR AGs. I don't have a specific use-case where I'll need to decrypt a database and then add it to an AG, but I'm worried that the logfiles are forever poisoned with the certificate.

Answer 1

The issue is you dropped the certificate that was used to encrypt the database prior to the restore. Portions of log were encrypted using the certificate and moving the drop certificate

USE master
DROP CERTIFICATE [TestDatabase_EncryptionCertificate]

to end of the script will allow the restore to complete.

Answer 2

found it, thanks to @Mainecoon21

Adding a backup to the main backup chain including logs fixed things.

-- Random crap tried to flush the encryption out of the log
IF(DATABASEPROPERTYEX('$DatabaseName', 'Recovery') = 'FULL')
        BEGIN
            BACKUP DATABASE [$DatabaseName] 
            TO DISK = N'$DatabaseName.bak' WITH NOFORMAT, NOINIT, 
            NAME = N'$DatabaseName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 --Settings from SSMS, no I don't know why.
            ;
            BACKUP LOG [$DatabaseName] 
            TO DISK = N'$($DatabaseName)_log.trn' WITH NOFORMAT, NOINIT, 
            NAME = N'$DatabaseName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 --Settings from SSMS, no I don't know why.
        END