Migrating SQL Server Databases that use Database Master Keys

There’s a lot of things to consider when migrating databases between SQL Server instances. I want to provide some tips for dealing with Database Master Keys, and in particular the case when those keys are encrypted by the Service Master Key.

Before getting into the details, let’s take a quick overview of Service Master Keys, Database Master Keys and how they interact.

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.

Service Master Keys can be manually regenerated using this statement:

alter service master key regenerate

This will decrypt and re-encrypt all secrets encrypted with the key.

The Service Master Key is backed up with the master database, but can be backed up and restored independently:

backup service master key to file = N'<filepath and filename>'
encryption by password = '<password>'
go

restore service master key from file = N'<filepath and filename>'
decryption by password = '<password>'
go

The Service Master Key properties are available by running the following query in the master database:

use master
go
select * from sys.symmetric_keys

Database Master Key: This is a database scoped symmetric key that is encrypted by the Service Master Key and stored in the database. It can be used to encrypt certificates and/or asymmetric keys within the database. Unlike the Service Master Key which is generated automatically, a Database Master Key must be created with DDL:

use <database>
go
create master key encryption by password = '<password>'

The Database Master Key can also be examined in the system catalog:

use <database>
go
select * from sys.symmetric_keys

Database Master Keys are backed up and restored with the database, but can also be backed up and restored independently.

use <database>
go
backup master key to file = N'<filepath and filename>'
	encryption by password = '<file password>'

restore master key from file = N'<filepath and filename>'
	decryption by password = '<file password>'
	encryption by password = '<new encryption password>'

There is one Database Master Key per database and it must be protected by a password. The Database Master Key can be encrypted by multiple passwords and any of these can be used to de-crypt the key. By default the Database Master Key is also encrypted by the Service Master Key, this can be switched off if needed.

Migrating Databases: If a database makes use of a Database Master Key, and that key is encrypted by the Service Master Key, then this encryption will need to be regenerated on the destination instance after any migration. Here are the basic steps.

  1. Firstly verify whether there are any Database Master Keys encrypted by the Service Master Key:
    select name
    from sys.databases
    where is_master_key_encrypted_by_server = 1
  2. (Optional) If you don’t know a valid password for the Database Master Key you can create a new one. (Remember that multiple passwords can encrypt the DMK)
    use <database>
    go
    alter master key 
    add encryption by password = 'migration_password'
    go
  3. Drop the encryption by the Service Master Key:
    use <database>
    go
    alter master key drop encryption by service master key
    go
  4. Migrate the database using either backup and restore, or detach and attach.
  5. Open the Database Master Key with a password (this could be the password created at step 2) and re-activate the encryption by Service Master Key – this will be mapped to the SMK on the new SQL instance:
    use <database>
    go
    open master key decryption by password = '<Password>'
    alter master key add encryption by service master key
    go
  6. (Optional) If you created a password specifically for the migration in step 2, then you should drop it:
    use <database>
    go
    alter master key 
    drop encryption by password = 'migration_password'
    go

Conclusion: The encryption, by default, of the Database Master Key by the Service Master Key means that it can easily go unnoticed by a DBA with sys admin privileges. My intention with this post was to provide a brief overview of the two keys, provide some statements used to modify and examine the keys, and outline the steps involved in migrating a database with a Database Master Key encrypted by the Service Master Key.

About these ads
This entry was posted in Database options, Disaster Recovery, Security, SQL Server, T-SQL and tagged , , . Bookmark the permalink.

13 Responses to Migrating SQL Server Databases that use Database Master Keys

  1. cinofilo says:

    This is the best post I ever read about backup/restore and migration of a sql server db.
    Thank you so much

  2. testt2Bright says:

    Excellent article, complex encryption topic explained very clearly. Bravo, and keep up the good work!

  3. Federico says:

    Hi: this is definitely extremely useful. I am wondering why nothing is said about backing up and restoring the Symmetric Key. I have to pull 5 symmetric keys, certificates, database master key and a service master key, from a production environment an install/restore to a similar test environment; I haven’t come across any post that indicates the backing up of the Symmetric Key and its restore process… The point is that when I create the symmetric key over from scratch, it is not the same key as was used originally to encrypt the fields I need to decrypt and it doesn’t work.

  4. David Mayorga says:

    Thank you for the article! Just one thing that’s bugging me, why did you have to drop the Service Master Key? What happens if I omit that step? If I wanted to duplicate my database to another server for testing purposes I would not want to drop the Service Key on the production server.

  5. matt.bowler says:

    Hi David,

    I don’t drop the service master key, I drop the encryption by the service master key. I do this because I’m moving the database to a new server which will have a new service master key, and I want the database to be encrypted by that key. I’ve seen the results of not doing this – and you get errors when trying to read the encrypted data because the decryption fails because the SMK is different. (If this wasn’t the case – breaking encryption would be as simple as restoring a database backup onto a SQL instance that we had sa privileges on!)

    These steps are for a database migration – when dealing with situations like mirroring or log-shipping things are a bit more complicated. In that case one option is to leave the encryption by service master key in place and restore that key to the destination server. This works but comes with its own set of risks/considerations…

  6. David Mayorga says:

    Sorry, my bad, I did mean to say that you dropped the encryption by SMK.
    So, my guess is that when you backup your production database there must be something in there that indicates whether or not the Database Master Key is encrypted by the SMK?. If there was an encryption by SMK and I restore the database to another server then obviously that server’s SMK would not be able to open the DMK, all this avoided by dropping encryption by SMK before backing up.
    But would it work just as well if on the server were I’m restoring I dropped the encryption by SMK and then re-added it? For this I’d open the DMK with the password.

  7. matt.bowler says:

    You are right – dropping and re-adding the encryption by SMK after the restore will have the same net effect. BUT – you must remember to at least add a password encryption before you take the backup or you just won’t be able to decrypt the database at all after the restore. IMHO its just a bit tidier at this point to drop the SMK encryption as well.

  8. David Mayorga says:

    Indeed, specially if you don’t know the password. Thank you!

  9. ET says:

    Best step by step guide i have read anywhere!!

  10. John says:

    In step 3, we will drop the encryption by the Service Master Key on the source server. Do we need to add it back? If yes, then when do we add it back? and how to add it back on the source server? Sorry I am new to all these encryption/decryption + migration things on different servers . Thanks a lot really for your helps.

    • matt.bowler says:

      The idea here is to sever the dependency between the database and the server, so that the database we backup (or detach and attach) is a ‘self contained’ entity. Once the database is attached to a new server then we can re-encrypt with the service master key. If you are keeping the source database in place then you can re-encrypt after you’ve taken a backup.

  11. Pingback: Шифрование резервных копий в SQL Server 2014 |

  12. Extremely well written guide on a complex subject. Thank you!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s