Moving a SQL Server Database with Encrypted Content: SMK, DMK, and You

If you’ve ever had to move a SQL Server instance to a new location — especially when data is encrypted — the process can be quite a hassle. Moving the data is not a serious problem; the bigger problem is moving over the numerous keys used to encrypt and decrypt information.

But first things first. Let’s go over the entire process step by step. At a high level, we need to:

  1. Make sure we have the right tools installed
  2. Move the database over (schema and data, users, certificates, symmetric and asymmetric keys)
  3. Verify that everything works

So let’s dive into it!

Step 1: The Right Tools

To make this process smooth, make sure you have SQL Server Management Studio installed (comes with SQL Server 2008 R2). If you are using SQL Server 2008 express, you can download SQL Server Management Studio Express.

If you already installed SQL Server, and didn’t get Management Studio, just reinstall — run the installer again, and choose options to create a new instance. When the options appear, pick Management Tools. (SQL Sever won’t reinstall and add a new instance, it’ll just add Management Studio.)

Step 2: Move Data Over

Chances are that you’re working with a live database, and you can’t afford the downtime of detaching. If you can stomach the downtime, simply detach the database from the existing instance, and reattach it to the new instance, and you’re done!

Otherwise, failing that, use Management Studio to create a backup. (Be aware that any changes to the original database after you make the backup won’t be replicated.) To do this:

  1. Open SQL Management Studio (Express) and connect to the database
  2. Right-click the database and select Tasks > Back Up …
  3. Under “Destination,” add a new File destination (this is where it will create the dump)
  4. Under the Options page, check off “Verify backup when finished”
  5. Click OK, and wait.

Eventually, your backup will complete. Copy the file to the new machine, and launch SQL Management Studio (on the new machine).

  1. Create the new (empty) database.
  2. Right-Click and choose Tasks > Restore > Database.
  3. Under “Source for Restore,” choose “From device:” and click on […]
  4. In the resulting pop-up, click on Add and choose your backed-up file.
  5. Click OK, then click OK, then wait.

You should see a message indicating the backup succeeded. Browse through some tables and verify that everything looks OK (smoke test).

Now the tricky part: if you have an application, stored procedure, or function that decrypts data, run it against the new database. The decryption will fail.

Why?

Step 3: Decryption and SQL Server Keys

SQL Server has a hierarchy of keys that it uses to encrypt, erm, other keys, and also security certificates and data. Check out this handy diagram below (c/o Four Guys from Rolla):

There are several keys here. The hierarchy is:

  • Service Master Key (SMK): This key lies at the root of the hierarchy. As implied by the diagram, this key is created when you install a SQL Server instance. It’s encrypted using among other things, data local on the computer SQL Server is installed on. This means that two keys on two computers will necessarily differ. This is important, because if we encrypt on one computer and decrypt on another computer, we need to have the same Service Master Key. This will pose a problem when moving the database to another SQL Server instance.
  • Database Master Key (DMK): This key exists in the database; SQL Server uses it to create other secure keys (it’s a public-private key encryption scheme). This key is encrypted using the Service Master Key, which, again, means that the encryption uses machine-specific information.
  • Security Certificates, Symmetric, and Asymmetric Keys: These are used to encrypt and decrypt data. They’re encrypted via the Database Master Key (DMK).

Now, when you encrypt or decrypt information in SQL Server, it uses a security certificate or symmetric key. Let’s say you have a certificate called FirstCert, which you use to create the symmetric key RedKey. To decrypt the encrypted Password column on the MyUsers table, you would write SQL like:


OPEN SYMMETRIC KEY RedKey DECRYPTION BY CERTIFICATE FirstCert
Convert(nvarchar, DecryptByKey([Password])) AS DecryptedPassword FROM MyUsers
CLOSE SYMMETRIC KEY StarKey

The first line says “Open this symmetric key, and decrypt it with this certificate; subsequent queries can use it to decrypt data.” Similarly, the last line closes the symmetric key. Without this, you’ll get the infamous SQL error (and likely you will also see):


Please create a master key in the database or open the master key in the session before performing this operation.

Yikes! What happened? We need to open our master key (DMK) before we can perform any cryptographic operations.

And here lies the problem: The current machine DMK cannot be used on data encrypted with another SMK. It will fail to decrypt, because the SMK has changed.

You can technically change the SMK and re-generate all the encrypted certificates, keys, etc. at the cost of some one-off CPU usage. But, the SMK is encrypted with a password; unless you know the password, changing it is impossible.

So what’s the alternative? Well, if you don’t have any encrypted information on your new database, you can backup and restore the key. But since this changes the SMK, the DMK and subsequent keys won’t be decryptable; so only do this if you don’t have any existing encrypted information in your target system.

Backing it up is described in this MSDN article as:


BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD = 'password'

This will generate an encrypted file (you wouldn’t want to expose your SMK for even one second, would you?). Copy it over to the target machine, and restore it as per this MSDN article:


RESTORE SERVICE MASTER KEY
FROM FILE = 'path_to_file'
DECRYPTION BY PASSWORD = 'password';

Tada, that’s it! Now re-run your decryption SELECTs, and everything should work as they did before. You may need to specify FORCE to obliterate the old key and go ahead with restoring the SMK.

About Ashiq Alibhai, PMP

Ashiq has been coding C# since 2005. A desktop, web, and RIA application developer, he's touched ASP.NET MVC, ActiveRecord, Silverlight, NUnit, and all kinds of exciting .NET technologies. He started C# City in order to accelerate his .NET learning.
This entry was posted in Tools, Web and tagged , , . Bookmark the permalink.

7 Responses to Moving a SQL Server Database with Encrypted Content: SMK, DMK, and You

  1. so clear for the steps…really appreciate for your sharing.

  2. Edward says:

    I read somewhere, msdn I think that all that is necessary is to restore the database from backup then open the master key and resync it with the Service Master key. No other changes required.

  3. Daniel Valero says:

    Hi,
    All you need to do is to restore a backup of the Database Master Key from the original server on the new server. When you restore the DMK it will automatically be reencrypted with the SMK of the new server. If you do not have Access to the original server but you know the password of the DMK you can open the DMK manually on the new server, create a backup and restore it over the DMK.

    You can see this on http://technet.microsoft.com/en-us/library/ms186336.aspx on the “Remarks” section

    I hope this help.

    Daniel Valero- SQL Server MS PFE

  4. Panos says:

    I think I have read that if we are restoring a Service Key to a new Server, the windows user credencials should be the same? Is this correct?

  5. Dave says:

    You Sir, are my hero of the week.

  6. Will says:

    Excellent article. You saved the day – many thanks!

  7. Jeff says:

    Thank you, this was a big help! At least in my case, I had to restore the database backup AFTER restoring the SMK backup. Then I was able to decrypt.

Leave a Reply

Your email address will not be published. Required fields are marked *