1

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.

2 Answers 2

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.

6
  • 1
    Yes, that's what I'm trying to avoid -- practically does this mean I have to keep every certificate forever even if I back up a database unencrypted in order to be able to restore it properly?
    – Pxtl
    Dec 20, 2022 at 2:48
  • 1
    @Pxtl, from my point of view and based on your script login you may need a bit different process scripted. Log backup which uses encryption should be separated by additional log backup from non-encrypted part. If both of them are part of the same backup file you need a certificate to restore. If you drop a certificate, create full db backup and log backup to clear the encrypted part of the log and than create full db backup and a log backup again to use it fro a restore it may help - or I also miss something. Dec 20, 2022 at 12:57
  • @Mainecoon21 -- I thought I did do the "full DB backup"? See the middle of the script where I try random crap in an attempt to flush the log. The full order of this script is (1) take initial backup to start backup chain. (2) encrypt and decrypt DB, deleting cert, (3) take full backup, (4) backup, drop, and restore DB & Logs.
    – Pxtl
    Dec 20, 2022 at 18:48
  • In order to restore a backup file either trn or bak that has a portion encrypted by TDE you will need the certificate. If you are removing TDE and want to move forward with the certificate removed. You can set the database to SIMPLE recovery and then back to FULL recovery. Then, take a fresh full backup and you will be able to recover point-in-time from there without the certificate. However, you would need the certificate for any recovery prior to setting the database to SIMPLE recovery.
    – KenWilson
    Dec 20, 2022 at 20:18
  • @Pxtl, yes, but as you need a full restore with no data loss you need another backup chaine. 1st full to start a chaine, 2nd full and 1st log backup - to cut the encryption. After that new full and 2nd log as this log backup doesn‘t have any information about encryption in it. And you need 3rd full to get a clear transaction chain. Dec 21, 2022 at 14:33
0

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

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.