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:
- Make sure we have the right tools installed
- Move the database over (schema and data, users, certificates, symmetric and asymmetric keys)
- 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:
- Open SQL Management Studio (Express) and connect to the database
- Right-click the database and select Tasks > Back Up …
- Under “Destination,” add a new File destination (this is where it will create the dump)
- Under the Options page, check off “Verify backup when finished”
- 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).
- Create the new (empty) database.
- Right-Click and choose Tasks > Restore > Database.
- Under “Source for Restore,” choose “From device:” and click on […]
- In the resulting pop-up, click on Add and choose your backed-up file.
- 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.
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.