SQL Server 2008 Open Master Key error upon physical server change over

Question

Hi, I copied a SQL Server database from one system to the next, identical setup, but completely different physical machine. I used Norton Ghost and recoverd files manually, for example, the entire SQL Server 2008 folder found in c:\Program Files after re-installing SQL Server 2008 Express.

One of my databases has AES_256 encryption enabled on a number of one of its tables, columns. I resetup my IIS7 and tried to run the app that access the database, upon retrieving the data, I get this error:

Server Error in '/' Application. Please create a master key in the database or open the master key in the session before performing this operation. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Please create a master key in the database or open the master key in the session before performing this operation.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

I've done some reading and found some links about how the AES encryption is linked with the machine key, but am at a loss as to how to copy it over to the new system. Or perhaps this even isn't the case.

NOTE: I've tried dropping the symmetric key, certificate and the master key and re-creating them. This gets rid of the error, but than the data that in encrypted via AES_256 does not show up. The columns that are NOT encrypted do, however.

Any help would be much appreciated. Thanks in advance!

Answer 1

The database master key is encrypted using the server master key, which is specific to the machine where SQL Server is installed. When you move the database to another server, you lose the ability to automatically decrypt and open the database master key because the local server key will most likely be different. If you can't decrypt the database master key, you can't decrypt anything else that depends on it (certificates, symmetric keys, etc).

Basically you want to re-encrypt the database master key against the new server key, which can be done with this script (using admin priviliges):

-- Reset database master key for server (if database was restored from backups on another server)
OPEN MASTER KEY DECRYPTION BY PASSWORD = '---your database master key password---'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO

Note that when you create a database master key, you should always provide a password as well so that you can open the key using the password in the scenario where the service master key cannot be used - hopefully you've got that password stored somewhere!

Alternatively you can restore a backup of the database master key - but you need one that was created for the target server, not the source server.

If you haven't got either a backup or a password then I'm not sure you will be able to recover the encrypted data on the new server, as you will have to drop and recreate the database master key with a new password, which will kill any dependent keys and data.