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.