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

29,600

Solution 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 privileges):

-- 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.

Solution 2

I just had a similar situation, an server rebuild after the OS drives died. I reinstalled SQL and reconnected it to all my old databases on the untouched data drives. Everything worked except for my encrypted columns. But my issue was that the master service key was hosed. I was able to repair my master service key by going back to the same domain credential that had been my SQL server service account before the move.

This article gave me the fix (kudos to Matt Bowler for his excellent article). I knew the local machine key had changed, but my salvation was that I could use the same service account.

Service Master Key: At the top of the key hierarchy is the Service Master Key. There is one per SQL Server instance, it is a symmetric key, and it is stored in the master database. Used to encrypt Database Master Keys, Linked Server passwords and Credentials it is generated at first SQL Server startup.

There are no user configurable passwords associated with this key – it is encrypted by the SQL Server service account and the local machine key. On startup SQL Server can open the Service Master Key with either of these decryptions. If one of them fails – SQL Server will use the other one and ‘fix’ the failed decryption (if both fail – SQL Server will error). This is to account for situations like clusters where the local machine key will be different after a failover. This is also one reason why service accounts should be changed using SQL Server Configuration Manager – because then the Service Master Key encryption is regenerated correctly.

http://mattsql.wordpress.com/2012/11/13/migrating-sql-server-databases-that-use-database-master-keys/

Share:
29,600

Related videos on Youtube

Tomasz Iniewicz
Author by

Tomasz Iniewicz

Updated on June 19, 2020

Comments

  • Tomasz Iniewicz
    Tomasz Iniewicz over 3 years

    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!

  • Tomasz Iniewicz
    Tomasz Iniewicz almost 14 years
    HOLY CRAP! You are my savior! If I could click that up arrow a thousand times, I would! Thanks SO much!