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

Article translations Article translations
Article ID: 2463682 - View products that this article applies to.
Expand all | Collapse all

On This Page

Symptoms

Consider the following scenario:
  • 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.
In this scenario, the new transaction is not completed. Specifically, the transaction waits indefinitely with the WRITELOG wait type. Additionally, the database may be marked as "suspect."

Notes
  • 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.

Resolution

Service pack information for SQL Server 2008

To 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:
968382 How to obtain the latest service pack for SQL Server 2008

Service pack information for SQL Server 2008 R2

To 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:
2527041 How to obtain the latest service pack for SQL Server 2008 R2
After applying the fix, a user is not allowed to drop the server certificate if certificate is used to secure a DEK.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
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.

More information

This fix also prevents error 33111 from occurring in the following scenario:
  1. A database is encrypted.
  2. The same database is decrypted.
  3. The certificate that encrypts the database key is dropped, but the database key is not dropped.
When the computer that's running SQL Server is restarted in this scenario, the following error is returned when the database is recovered:

Error: 33111, Severity: 16, State: 3
Cannot find server certificate with thumbprint
'0xA89FE581BD3D371CD3E41D48C9978163AB7055F7'

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:

Msg 3716, Level 16, State 15, Line 2
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:
  1. Re-create the certificate from backup.
  2. Bring the database online.
  3. Run a DROP DATABASE ENCRYPTION KEY command in the encrypted database.
  4. Drop the certificate if that's what you want to do in this case.
For more information about transparent data encryption (TDE), go to the following MSDN website:
Understanding Transparent Data Encryption (TDE)
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
824684 Description of the standard terminology that is used to describe Microsoft software updates

Properties

Article ID: 2463682 - Last Review: August 13, 2013 - Revision: 6.0
Applies to
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Workgroup
Keywords: 
kbqfe kbfix kbexpertiseinter kbprb kbsurveynew KB2463682

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com