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 1^st thing I need to do is create one:

(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