How to bind database to new TDE Cert?

CharlieLor 556 Reputation points
2020-09-28T12:40:20.737+00:00

This is the code I use for the binding to new cert.

USE [AGplaceholder]
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE NEWTDECert;
GO

From this Updating an expired SQL Server TDE certificate article. However, it's not working when backup the database. I am getting this error.

Executing the query "BACKUP LOG [AGplaceholder] TO DISK = N'E:\Logs\A…”GPl… failed with the following error: "Cannot find the certificate 'OldServerTDECert', because it does not exist or you do not have permission.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Based on this error, it's still looking for the old cert that's why it failed. So, how do I make sure it's using the new cert? I ran the code below and it's showing that the databases are using the new cert.

USE [master]
GO
SELECT
DB_NAME(db.database_id) DbName, db.encryption_state
, encryptor_type, cer.name, cer.expiry_date, cer.subject
FROM sys.dm_database_encryption_keys db
JOIN sys.certificates cer 
ON db.encryptor_thumbprint = cer.thumbprint
GO

DbName encryption_state encryptor_type name expiry_date subject
AGplaceholder1 3 CERTIFICATE NEWTDECert 2021-12-31 00:00:00.000 My TDE DEK Certificate
AGplaceholder2 3 CERTIFICATE NEWTDECert 2021-12-31 00:00:00.000 My TDE DEK Certificate
AGplaceholder3 3 CERTIFICATE NEWTDECert 2021-12-31 00:00:00.000 My TDE DEK Certificate

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,688 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,625 questions
0 comments No comments
{count} votes

Accepted answer
  1. CharlieLor 556 Reputation points
    2020-09-28T17:54:01.257+00:00

    I figured it out. In the Property of Maintenance Plan, I need to put in the new TDECert that I just created in the BackupEncryptorName. Despite alerting the database to use the new cert, that did not make change to the property of the maintenance plan. It is something I have to change it manually.
    28861-screen-shot-2020-09-28-at-75305-am.png

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Jayson Sainsbury 26 Reputation points
    2020-09-28T17:45:06.457+00:00
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.