How to: Decrypt SQL 2005/2008 database master keys on other servers

SQL 2005 and 2008 both have what’s referred to as an encryption hierarchy. The details of this are beyond the scope of this post, but in essence: we encrypt our data using a key. We need to protect our key somehow, and we don’t want to litter our stored procs with key passwords, so we use a certificate. We then protect the certificate with a database master key. This is in turn protected by the service master key which is finally protected by DPAPI, an operating system provided store.

The keys and certificates are stored within the database itself, but when we move the database to another server they can’t be accessed. This is because the new server doesn’t know how to decrypt the database master key and in turn can’t decrypt the keys and certificates we need to use.

Usually…

The database master key is always protected by a password. You would have had to provide this when you first created the key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mypassword'

When you create the key like this, SQL generates the key value and encrypts it with the supplied password before storing it. It also makes a second copy which is encrypted using the service master key, and this is the copy that is normally used. When you move your database to another server, the service master key protected copy can’t be loaded but the password protected copy can be.

 OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword' 

With the key now decrypted and loaded into memory, we can ask the new server to make a copy that is protected using the service master key.

 ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY 

Finally, we close the key to take it out of memory.

 CLOSE MASTER KEY 

Voila, you can now access your keys and certificates on the new server with automatic key management (that is, with SQL automatically opening and closing the database master key for you as required).

Read-only databases

The approach described above is dependent upon the database being in a writable state as it makes modifications of the database master key itself. What happens when we want to work with a read-only version of a database such as a snapshot or a mirror?

With automatic key management, SQL Server will first attempt to decrypt the database master key using the service master key. In a read-only database, we are unable to create a copy of the key that is protected in this way.

After attempting that, SQL Server will look in the credential store (sys.credentials) for any credentials related to the master key. It will attempt each credential it finds.

Adding our credential to the store is easy:

USE [master]
GO

EXEC sp_control_dbmasterkey_password
    @db_name   = N'mydatabase',
    @password  = N'mypassword',
    @action    = N'add';

You can see evidence of the new credential in both sys.master_key_passwords and sys.credentials:

SELECT  d.name as database_name,
        c.*,
        mkp.family_guid

FROM    master.sys.credentials c

        INNER JOIN master.sys.master_key_passwords mkp
            ON c.credential_id = mkp.credential_id

        INNER JOIN master.sys.database_recovery_status drs
            ON mkp.family_guid = drs.family_guid

        INNER JOIN master.sys.databases d
            ON drs.database_id = d.database_id

Voila, just like above, you can now access your keys and certificates on the new server with automatic key management (that is, with SQL automatically opening and closing the database master key for you as required).

You might also notice that the sys.master_key_passwords view ties a master key password to a family id as opposed to a database id. A family id is assigned when a database is first created and it stays the same even if the database is detached, moved, reattached, mirrored, etc. As a result of this behavior, you could have multiple databases on the one server that share the same family id. In contrast, a database id is created every time a database is attached and is therefore unique for every database instance on the server. In the context of our master keys, the outcome is that adding a credential against one database will actually add it for all of the databases which have come from the same original instance. Even if you detach and reattach your databases, or drop and restart replication, the credential will still be kept in the store and work with the new database instance. Attaching any new instances which share the same family id will also automatically inherit the credential. (Unless of course you change the database master key password in which case the credential will still be attempted but will fail and cause the next one to be attempted instead.)