SQL Server includes a number of encryption features and capabilities that you can use to secure your systems. The basis for security in many of your databases is the Database Master Key (DMK). This article will examine the basics of the DMK, how it is used and how you can ensure you don't lose access to your data.

The Encryption Hierarchy

The encryption hierarchy inside SQL Server is in Books Online, the image of which is reproduced below. The basis is the DPAPI (Data Protection API) at the Windows layer and this continues through the various keys available in the databases.

The DMK is the basis for encryption inside each database. This means you can have a separate DMK in each database on your instance. You can even create master keys in any of the system databases, but I would not recommend doing so in model or tempdb as these could cause you issues at a later time. You can, and need to in some cases, create a DMK in the master database for some operations.

As the diagram shows above, your DMK will be protected by the Service Master Key (SMK) by default. You can change this and we will look at it below.

Creating a DMK

Creating a DMK is easy. You can use the CREATE MASTER KEY syntax to do so. For example, I'll create a database below and then add a DMK.

CREATE DATABASE MySampleDB;
GO
USE MySampleDB;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Always*seR#@llyStr0ngP3sswo%ds5';

This code creates the database I will use in this article (MySampleDB) and then creates a master key with a password. This password must conform to the security requirements of the Windows host.

By default, this master key is also protected by the Service Master Key. I can then move forward and create additional keys that are protected by the DMK.

This key is also opened automatically by the instance because the SMK has encryped it (as seen above in the hierarchy). We can close this key, but first we need to remove the encryption by the SMK. We do this with.

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;

Once that is done, we can close the DMK with this code

CLOSE MASTER KEY;

If we needed to open this key, because we were accessing another key that was protected by the DMK, we could open it by specifying the password

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Always*seR#@llyStr0ngP3sswo%ds5';

Separate Passwords

Suppose that you wanted to ensure that multiple people could access encryption keys, but you didn't want a single password that they all knew. You can use multiple passwords to encrypt the DMK, allowing separate access for each of them. I can add encryption with a new password as follows:

ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = 'Jo#D3v';

Now that DMK can be opened with either of the two passwords shown in the code snippets above.

If I needed to revoke access for a password, I can use similar code to do that.

ALTER MASTER KEY DROP ENCRYPTION BY PASSWORD = 'Jo#D3v';

This would allow me to grant access in a more granular fashion. Everyone that knew a password could open the DMK, but by using multiple passwords for different groups, I can better revoke access if need be.

Note: At last one method of encryption for the DMK must be maintained. This can be through the SMK or through a password, or both.

Backup and Restore

The DMK is included in a database backup and will be restored just like any other object. There are, however, a few potential issues with the DMK and as a result, you can perform a backup and restore of this object in a database. We will examine the backup process and then the restore situations and process.

Backing up a master key uses the BACKUP MASTER KEY command. The process requires you to specify a file location and a password. This is because the key must be protected and a password is used to perform encryption of the backup. To back up our master key, we could run this:

  BACKUP MASTER KEY TO FILE = 'MySampleDBMasterKey.key'
   ENCRYPTION BY PASSWORD = 'AD!ffer#ntStr0ngP@ssword5';

Note that I have not included a path. The backups of encryption keys are stored in the data directory of SQL Server by default and secured with permissions to the SQL Server instance service account. You can see my backup below in that folder:

The .key extension is by convention, but you can choose any extension you like. Just don't forget what it is.

If I examine the properties of the file, I get a message that I don't have default access.

If I click through the Advanced button, I can see that the service account is the owner, and although I have access as an administrator, any movement or copying of the file would require me to acknowledge a UAC dialog. This is to provide additional protection for this file from non-administrators.

The use of the key in a restore situation requires that it can be decrypted. This means either the password is needed to open the DMK, or the SMK must be able to decrypt the DMK. After I restore a database, the DMK may be decrypted by the SMK if the SMK is the same one that existed when the backup was made, and if the DMK was encrypted by the SMK. I know it sounds confusing, but if you restore to the same instance and there haven't been any service account changes since the backup was made, this is probably the situation you find yourself in.

If the database was restored to a different instance, then the SMK likely cannot decrypt the DMK. However, if we know the password that encrypted the DMK (or any of them), we can open the master key (using the code above that provides the password) and then run this:

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

This would allow the SMK to encrypt the DMK and automatically open the DMK for users.

If I did not know the password for the DMK in a restore, then I have two choices. The first is to restore the master key with the RESTORE MASTER KEY command. I would provide the password and also provide a new password that encrypts the restored DMK. 

RESTORE MASTER KEY FROM FILE = 'MySampleDBMasterKey.key'
 DECRYPTION BY PASSWORD = 'AD!ffer#ntStr0ngP@ssword5'
 ENCRYPTION BY PASSWORD = 'ANe@P2ssWo%d';

I could then optionally add SMK encryption with the command above.

If I didn't have a backup of the DMK, I could regenerate it. The regeneration process attempts to decrypt all the keys that the master key protects and re-encrypt them with the new key. If this isn't possible, the command fails. It is possible to use the FORCE option to allow the errors to be bypassed, but this potentially means that there are keys you can no longe decrypt, which potentially means you cannot decrypt some data.

Since being unable to decrypt data can lead to a Resume Generating Event (RGE), I strongly suggest that you back up your DMK and log the passwords in a secure manner.

Keep It Simple

The whole process of encryption is fairly simple, essentially calling a function to either encrypt, or decrypt, your data. However the danger lies in the fact that if you lose a key, you likely have no way to decrypt the data. This means that managing passwords and backups of your keys is important to a long and successful career.

This article has shown you how to create, backup, and restore DMKs as well as add additional password encryption. Since the DMKs provide the basis for much of your column level encryption, it is important that you ensure you have backups of these keys and the appropriate passwords. I would suggest you use some sort of password manager, as well as limiting the number of passwords you use to encrypt DMKs. The minimum number of groups you need to give access (and potentially revoke access from) is the number you should use.

I would also suggest you name your backups for the instance and database to ensure you can match them up correctly. I didn't do that above and in testing for this article, I managed to confuse two backups from two separate instances. Let that be a lesson: name your backups appropriately. I would also suggest that as you rotate keys, add a end date of usefulness to your last few key backups to ensure that you know these only apply up until a certain date.

Encryption isn't hard to implement and use, but the management of the keys and passwords can quickly become complex, so practice and make sure you have multiple backups.