Select the product you need help with

FIX: A database that has transparent data encryption enabled may be marked as "suspect" if the server certificate is dropped in SQL Server 2008 or in SQL Server 2008 R2

Article ID: 2463682 - View products that this article applies to.

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.

Back to the top | Give Feedback

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

(http://support.microsoft.com/kb/968382/en-US/ )

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

(http://support.microsoft.com/kb/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.

Back to the top | Give Feedback

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 Microsoft SQL Server 2008 Service Pack 3.

Back to the top | Give Feedback