SQL Server 2008
Posted on 2014-01-24
We have disabled TDE and dropped Certificates/Master for few servers then the backups have been executed successfully except one server due to below error.
“Cannot find server certificate with thumbprint '0x0CE1D924785C160413399D79FD3594E158129976'..”
The issue is impacting backup failure for server and it is not impacting application/data retrivel.
Again After dropping certificate, we have reviewed the following steps successfully but the problem persists.
-- SELECT name, database_id, is_encrypted FROM sys.databases
This query will return all of the databases in your instance and reflect the encryption status. The value of "1" in the is_encrypted column indicates that TDE is enabled. A value of "0" indicates that TDE is not enabled.
--SELECT * FROM master.sys.certificates
This query will return the server certificates that have been created in the MASTER database. Upon execution of the DROP CERTIFICATE command the server certificate will no longer appear.
-- SELECT * FROM master.sys.symmetric_keys
This query will return the Database Master Keys (DMK) that have been created in the MASTER database. Upon execution of the DROP MASTER KEY command the DMK will no longer appear.
-- SELECT * FROM sys.dm_database_encryption_keys
This query will return all Database Encryption Keys (DEK) that have been created in your instance. Upon execution of the DROP DATABASE ENCRYPTION KEY command the DEK for the target database will no longer appear. If there are no other databases with TDE enabled on the instance, a restart of the instance will remove the DEK for the TEMPDB.
-- Query any table in the target database to show that data is successfully returned.
SQL Server version Details:
Microsoft SQL Server 2008 (SP1) - 10.0.2573.0 (X64) Feb 4 2011 11:27:06 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
Please help to resolve the issue.