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.
How to bind database to new TDE Cert?
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
-
CharlieLor 556 Reputation points
2020-09-28T17:54:01.257+00:00
1 additional answer
Sort by: Most helpful
-
Jayson Sainsbury 26 Reputation points
2020-09-28T17:45:06.457+00:00 What msg number are you getting?
Is it related to this issue?