Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2008

Posted on 2014-01-24
4
Medium Priority
?
596 Views
Last Modified: 2014-02-09
Hi,

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.

Thanks,
Chandra
0
Comment
Question by:Chandra Mohan Kanithi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 17

Expert Comment

by:Kent Dyer
ID: 39805890
Question - why did you pull the certificate in the first place?  Was it expired?  It sounds like your certificate store may need to be repaired.  However, I would be absolutely sure your cert in question is not expired or set to expire.
0
 

Author Comment

by:Chandra Mohan Kanithi
ID: 39806113
Here the issue is we are unable to take backup of database after dropped the certificate and disabled the TDE.

Could you please solution for certification set to expire.
0
 

Author Comment

by:Chandra Mohan Kanithi
ID: 39811190
No, the certificate was not expired. As part of decryption, we had to remove the certificate. After that, backup won't work anymore. When the system was restarted SAP won't come up until the certificate was recreated.

Please help us.

Thanks,
Chandra
0
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 1500 total points
ID: 39811773
hi

The fix for this issue was first released in Cumulative Update 1 for SQL Server 2008 Service Pack 2. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
2289254 Cumulative update 1 for SQL Server 2008 Service Pack 2
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question