You may fail to backup log or restore log after TDE certification/key rotation

 When TDE is enabled, all the records written to transaction log files are encrypted as well. During the TDE certification/key rotation, these log records are not touched.

On changing certificate/keys, the current active VLF file, which is encrypted by old key, will be closed. New log records will be encrypted by new key and write to next available VLF or a new created VLF. At this stage, the transaction log file has both log records encrypted by old certificate, and the log records encrypted by new certificate.

When you do the log backup, the log records that have been encrypted by old certificate, may be decrypted and then encrypted by new certificate to the backup file, or just be flushed to the backup file without doing any decryption/encryption, depending on how you run the log backup.

This behavior may cause two different issues if the old certificate is removed.

1.When you do log backup with ‘COMPRESSION+MAXTRANSFERSIZE’ combination , SQL Server will decrypt records, which was encrypted by old certificate, then encrypt the data by new key and save to file.

If you remove the old key after certificate rotation before the log backup, the log backup fails with error 33111

Cannot find server %S_MSG with thumbprint ‘%.*ls’.

2.When you do regular log backup without ‘COMPRESSION+MAXTRANSFERSIZE’ combination , like : backup log dbName to disk=’xxx’.

SQL Server just grabs the data from transaction log and save to log backup file, it skip the decrypt and encryption. The backup log goes well regardless of the old certificate.

You may be not aware of that you have a log backup file that has two kind of data, data encrypted by old certificate and data encrypted by new certification.

If you trying to restore the log file, you need to make sure both certificates exist. Else error 33111 is raised.

So Backup certificate is always suggested. Restore the certificate if case you run into this issue.

Here is the reproduce step of first scenario:

1.Create database , and enable TDE by using the oldCertificate

create database dbName
go
use dbName
go
backup database dbName to disk='dbName.bak'
go
backup log dbName to disk='dbName.trn'
go

use master
go
create certificate oldCertificate with subject='oldCertificate'
go
create certificate newertificate with subject='newertificate'
go

USE dbName
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE oldCertificate;
GO
ALTER DATABASE dbName SET ENCRYPTION ON;

2.Populate some data into the database,

Use dbName
go
create table ta(id int)
go
insert ta 
select 1 from sys.columns

3.DMV sys.dm_db_log_info will show us the encryption thumbprint used by each VLF.(vlf_encryptor_thumbprint is added in SQL 2019)

use dbname
go
select vlf_encryptor_thumbprint,c.name, l.*From sys.dm_db_log_info(db_id()) l 
left join master.sys.certificates c on l.vlf_encryptor_thumbprint=c.thumbprint

4.Do the certification rotation

ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE newertificate

5.Check the encryption thumbprint. The new certificate is used in new VLF.

use master
go
select d.name as DbName,c.name as CertName from sys.dm_database_encryption_keys k 
inner join sys.databases d on k.database_id=d.database_id
inner join sys.certificates c on c.thumbprint=k.encryptor_thumbprint

6.At this stage, the transaction log file has both log records encrypted by old certificate, and the log records encrypted by new certificate.

use dbname
go
select vlf_encryptor_thumbprint,c.name, l.*From sys.dm_db_log_info(db_id()) l 
left join master.sys.certificates c on l.vlf_encryptor_thumbprint=c.thumbprint

7.Delete the old certificate

Use master
drop certificate oldCertificate

8.Take log backup

BACKUP log dbname TO DISK = 'dbName_AfterRotation.trn' 
WITH COMPRESSION, MAXTRANSFERSIZE = 1048576

Here is the reproduce step of second scenario:

Here is a reproduce step of second scenario(step 1~5 are same to steps in the first scenario):

1.Create database , and enable TDE by using the oldCertificate

create database dbName
go
use dbName
go
backup database dbName to disk='dbName.bak'
backup log dbName to disk='dbName.trn'
go

use master
go
create certificate oldCertificate with subject='oldCertificate'
create certificate newertificate with subject='newertificate'
go

USE dbName
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE oldCertificate;
GO

ALTER DATABASE dbName
SET ENCRYPTION ON;

go

2.Populate some data into the database,

Use dbName
go
create table ta(id int)
insert ta 
select 1 from sys.columns

3.DMV sys.dm_db_log_info will show us the encryption thumbprint used by each VLF.(vlf_encryptor_thumbprint is added in SQL 2019)

use dbname
go
select vlf_encryptor_thumbprint,c.name, l.*From sys.dm_db_log_info(db_id()) l 
left join master.sys.certificates c on l.vlf_encryptor_thumbprint=c.thumbprint

4.Do the certification rotation

ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE newertificate

5.Check the encryption thumbprint. The new certificate is used in new VLF.

use master
go
select d.name as DbName,c.name as CertName from sys.dm_database_encryption_keys k 
inner join sys.databases d on k.database_id=d.database_id
inner join sys.certificates c on c.thumbprint=k.encryptor_thumbprint

6.Delete the old certificate

Use master
drop certificate oldCertificate

5.Take database backup

BACKUP database dbname to disk='dbname_AfterRotatin.bak'

6.Take log backup.

BACKUP log dbname TO DISK = 'dbName_AfterRotation1.trn' 

Let’s try to restore the database backup and log backup to a new server with the new certificate.(or restore the backup in the same instance)

Restore database dbname from disk='dbname_AfterRotatin.bak' with norecovery
Restore log dbname from disk='dbname_AfterRotatin1.trn' with norecovery

The restore log will fail with 33111 error.

This is the related KB https://support.microsoft.com/en-us/help/4534430/fail-to-backup-transaction-log-after-tde-certificate-key-rotation