Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. A TDE-enabled database may not recover in SQL Server 2008 or in SQL Server 2008 R2

A TDE-enabled database may not recover in SQL Server 2008 or in SQL Server 2008 R2

View products that this article applies to.

Symptoms

In Microsoft SQL Server 2008 and in Microsoft SQL Server 2008 R2, a database that is enabled for transparent database encryption (TDE) may not recover. And, the following error message may be logged in the SQL Server error log:
2012-01-14 22:16:26.47 spid20s Error: 15581, Severity: 16, State: 3.
2012-01-14 22:16:26.47 spid20s Please create a master key in the database or open the master key in the session before performing this operation.

In some cases, the instance of SQL Server may appear unresponsive. If you query sys.dm_exec_requests dynamic management view, you notice that the LogWriter thread and other threads that are performing DML operations are waiting indefinitely with WRITE_LOG wait_type. Other sessions may also be waiting while they try to obtain locks.

↑ Back to the top

Cause

This issue occurs when service master key encryption for the database master key in the master database is removed when the following command is run:

Use master
go
alter master key drop encryption by service master key
The database master key is used to encrypt the certificate that is used by the database master key. Any attempt to use the TDE-enabled database requires access to the database master key in the master database. A master key that is not encrypted by the service master key must be opened by using the OPEN MASTER KEY statement together with a password on each session that requires access to the master key. Because this command cannot be run on system sessions, recovery cannot be completed on TDE-enabled databases.

↑ Back to the top

Resolution

To resolve the issue, enable automatic decryption of the master key. To do this, run the following commands:

Use master
go
open master key DECRYPTION BY PASSWORD = 'password'

alter master key add encryption by service master key
Use the following query to determine whether automatic decryption of the master key by the service master key was disabled for the master database:

select is_master_key_encrypted_by_server from sys.databases where name = 'master'
If this query returns a value of 0, automatic decryption of the master key by the service master key was disabled.

↑ Back to the top

More Information

For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on versions of the SQL Server product, see the following table:
Rule softwareRule titleRule descriptionProduct versions against which the rule is evaluated
Microsoft System Center AdvisorSQL Server database master key for the master database is not encrypted by the service master key.System Center Advisor checks if the instance of SQL Server has any databases that use TDE. It also checks to make sure that the database master key of the master database is encrypted by the service master key. If this is not the case, System Center Advisor generates a warning.SQL Server 2008
SQL Server 2008 R2
SQL Server 2012

↑ Back to the top

Keywords: kb

↑ Back to the top

Article Info
Article ID : 2666213
Revision : 1
Created on : 1/7/2017
Published on : 2/13/2012
Exists online : False
Views : 267