FIX: Database with transparent data encryption enabled is marked as "suspect" if the server certificate is dropped in SQL Server 2008 or in SQL Server 2008 R2
- On a server that is running SQL Server 2008 or SQL Server 2008 R2, you create a server certificate in the master database.
- You enable transparent data encryption on a user database whose database encryption key (DEK) is secured by using the server certificate.
- You drop the server certificate.
- You start a new transaction against the database.
- This problem persists even if you restore the certificate from a backup.
- When this problem occurs, SQL Server does not respond to a Stop request. You can shut down SQL Server only by executing the Shutdown command together with the NOWAIT option.
- Under certain scenarios, an error that resembles the following may be logged in the SQL Server error log:date time spid5s Error: 33111, Severity: 16, State: 3.
date time spid5s Cannot find server certificate with thumbprint '0xF9384BBA39E82B87D07A8D9AEBD58DDF55B715A3'.
date time spid175 Error: 15507, Severity: 16, State: 1.
date time spid175 A key required by this operation appears to be corrupted.
date time spid175 Error: 3314, Severity: 21, State: 4.
date time spid175 During undoing of a logged operation in database 'database name', an error occurred at log record ID (10637:3496:70). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
date time spid18s Error: 9001, Severity: 21, State: 5.
date time spid18s The log for database 'database name' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Service pack information for SQL Server 2008To resolve this problem, obtain the latest service pack for SQL Server 2008. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
Service pack information for SQL Server 2008 R2To resolve this problem, obtain the latest service pack for SQL Server 2008 R2. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
This problem was first corrected in SQL Server 2008 R2 Service Pack 1 for SQL Server 2008 R2.
This problem was first corrected in SQL Server 2008 Service Pack 3.
- A database is encrypted.
- The same database is decrypted.
- The certificate that encrypts the database key is dropped, but the database key is not dropped.
Cannot find server certificate with thumbprint
After Service Pack 3 (SP3) is applied to SQL Server 2008, the following error is triggered when the certificate drop is made before the database key drop:
The certificate 'MyServerCert' cannot be dropped because it is bound to one
or more database encryption keys.
To fix this problem, follow these steps to decrypt the database:
- Re-create the certificate from backup.
- Bring the database online.
- Run a DROP DATABASE ENCRYPTION KEY command in the encrypted database.
- Drop the certificate if that's what you want to do in this case.
Αναγνωριστικό άρθρου: 2463682 - Τελευταία αναθεώρηση: 08/13/2013 16:33:00 - Αναθεώρηση: 6.0
- kbqfe kbfix kbexpertiseinter kbprb kbsurveynew KB2463682