Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Database Master Key Error After Database Restore

By Derek Stanley,

Situation

After restoring a copy of the production database onto a test server, I would run into some strange issues relating to the database master key that look like this:

Msg 15581, Level 16, State 3, Line 6

Please create a master key in the database or open the master key in the session before performing this operation.

I do not claim to be an expert in security nor is this a reference for all things encryption, but if we start at a smaller scale for this issue, I hope it provides a platform to understanding when this error can be raised and why along with some basic knowledge in the application of encryption.

Understanding the Issue

At my current employer, we use symmetric encryption keys to encrypt and decrypt passwords in the database. To fully understand the above error, I needed to fully understand all the moving pieces with that functionality as well as how the master key mentioned in the error relates. Please note: there are a few ways that you can set this up, but for this article, assume the following:

The moving pieces that need to be understood are:

  1. The symmetric key: Used to perform the encryption and decryption of the passwords
  2. The certificate: Used to encrypt the symmetric key in this environment
  3. The database master key: Used to encrypt the certificate in this environment
  4. The service key: Used to encrypt the database master key.

In summary, the Database Master Key is encrypted by the service key and the service key is created during SQL Server setup and it is encrypted with the Windows Data Protection API. Basically, the master key is encrypted by the server instance so when you move that database master key (located in the database) to another server, you need to update it with the new service key of the new server.

The issue is caused because when I go to open the symmetric key; it is decrypted by the certificate which is encrypted by the database master key. Due to the copy of the database from one server to another, that database master key cannot be automatically decrypted because the service key is now invalid. To fix the issue, you need to manually open the database master key and add the new service master key. Let’s take a look at a mock up:

Setting up a Test Environment

On a test server, I created a database called TestDB (I know, very original…lol!). By default, there are no database master keys in the database so the 1st thing I need to do is create one:


USE testdb
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123!'

(Creating the database master key: http://technet.microsoft.com/en-us/library/ms174382.aspx)

I can verify its creation by running:

I can also verify that the master key was created and encrypted by the service key:

I now need to create a certificate. This will be used to encrypt the symmetric key I create after. Notice that the certificate is encrypted with the database master key I just created:

(Create Certificate: http://technet.microsoft.com/en-us/library/ms187798.aspx)

Now, I just need to create the symmetric key:

(Create Symmetric Key: http://technet.microsoft.com/en-us/library/ms188357.aspx)

This is the fun part; using this stuff! We will first create a table, insert a few test records, encrypt the data, and decrypt it. First, create the table and insert some records:

Next, we need to use the ENCRYPTBYKEY function to encrypt the data. Note that we need to open the symmetric key first:

Now of course, we want to make sure we can decrypt it by using the DecryptByKey function:

Behind the scenes, the database master key is automatically decrypted by the service key.

Reproducing the Error

To reproduce the error that I was originally getting:

Msg 15581, Level 16, State 3, Line 6

Please create a master key in the database or open the master key in the session before performing this operation.

I need to take a backup of my newly created TestDB database and restore it onto another server or instance. Once complete, I connect to the new instance, and run the following query:

This is the same error I was getting before. This is due to the service key being different on this instance\server than the original so it cannot automatically decrypt the database master key to open the TestSymKey01.

Resolving the Issue

All I need to do here is explicitly open the database master key and encrypt it using the new service key for this particular server\instance:

I can now open the symmetric key to decrypt the information in the table:

Conclusion

In this article, we took a look at a common issue relating to database master keys and how we can get errors when moving at database to another server. I explained how the symmetric key, certificate, database master key, and service key all relate to each other. To help understand, we stood up a test environment to show how to set up basic encryption. We then reproduced the error by restoring the TestDB database to another server and trying to decrypt information in the EncryptionTest table. Finally, we were able to fix the error by simply updating the database master key to the service key on the new server\instance.

Additional References

Overview: http://technet.microsoft.com/en-us/library/ms189586%28v=sql.105%29.aspx

Total article views: 3099 | Views in the last 30 days: 15
 
Related Articles
FORUM

Sharing MASTER KEY ENCRYPTION DB?

MASTER KEY ENCRYPTION, CERTIFICATE or SYMMETRIC KEY

ARTICLE

Administering Database Master Keys in SQL Server

A short piece that gives you the basics of how to administer the database master keys that form the ...

BLOG

Creating a Service Master Key in SQL Server

The basis for all the encryption in SQL Server is the Service Master Key, which is the top of the......

FORUM

SQL Server 2005 encryptation

SQL Server 2005 encryptation

FORUM

Create Login from master

Create login for a database from master

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones