The Database Master Key (DMK) in SQL Server forms the basis for encryption inside of a database. There is the Service Master Key (SMK) for the instance and the various symmetric and asymmetric keys inside a database as well, but those sit above the DMK in the encryption hierarchy.

This short article will look at the administrative options for the DMK. We will cover creating the keys, encrypting them, and the backup/restore process. Additional discussions of how to use these keys are outside the scope of this piece.

Creating a DMK

The T-SQL to create a DMK is similar to the DDL for many other objects. There is a CREATE MASTER KEY syntax, along with the corresponding DROP MASTER KEY syntax. Since each database can contain its own DMK, you must be in the database for which you want to manipulate the DMK.

I can create a DMK as follows:

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

This code can be run again, though it will return an error as each database can only contain one DMK. The error doesn't cause any issues, but you should trap for it in any automated scripts that create these keys. The error is 15578 and shown below.

Msg 15578, Level 16, State 1, Line 1

There is already a master key in the database. Please drop it before performing this statement.

To remove the DMK, I run:

DROP MASTER KEY;

If I try to run this again, I'll also get an error (15151), which is shown below. Again, this isn't an issue, but you should trap for this.

Msg 15151, Level 16, State 1, Line 1

Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.

Note that you cannot drop a master key that is being used to encrypt any other key (symmetric, asymmetric, or certificate). This is because you would lose access to those keys, and thus, any data encrypted by those keys.

Encrypting a DMK

You have two options for encrypting a DMK: the SMK or a password. You can choose to use either or these or both to encrypt your DMK. Since Microsoft wants to ensure you can always access your DMK, by default you are using both of these when you create a DMK. The SMK encryption occurs automatically, and as shown in the code for creating a DMK, you must supply a password.

If I wish to remove encryption by the SMK, thereby requiring a user or application to input a password before they can use any asymmetric keys or certificates, I can do so with this code:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;

This will remove the SMK encryption and I would need to use supply the password when I issued an OPEN MSATER KEY command. If I decided this was a bad idea and wanted to add SMK encryption back, perhaps after a restore, I would issue:

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

By default, a single password protects the DMK, but you can add additional passwords. These are useful when you want to allow multiple groups to access the DMK, but don't want to have them all sharing a single password. In this case, I can add additional passwords to protect with the DMK with this code:

ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = 'W3bD#veloper$8'

To revoke a password from being used to protect the DMK, I can issue this: 

ALTER MASTER KEY DROP ENCRYPTION BY PASSWORD = 'W3bD#veloper$8'

Backup and Restore

With encryption keys, your data is well protected. The encryption is difficult to break, which is good. However it's bad in that if you lose your keys, or the password that protect them, no one else can access the data. There are no back doors to help you.

As a result I stress to everyone that your encryption keys need to be protected, both with secure passwords, and also with those passwords stored securely in a way that ensures no one person or system can cause the loss of this information. 

A DMK can be backed up with the BACKUP MASTER KEY DDL, which will allow you to export your DMK to a file. You can do this by executing the following code:

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

The convention is to use a file with the .key extension, though you can choose any file name you want. The default path is the DATA directory default for your instance, not the BACKUP directory. This is because these files contain privileged information and should be controlled. The files are also owned by the service account for your instance and any copying/moving of the file will trigger a UAC dialog.

The analogue of the backup is a restore. As you might expect, the DDL is fairly simple and standard. Since this is a database level object, your session context must be the database in which you want to DMK to apply. You can use this code to restore a DMK.

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

In this case, the path again is the DATA default directory. The decryption statement must be the password used to back up the key originally. The ENCRYPTION BY password is the new password to apply to the DMK in this database.

Once the restore is complete, you can add/remove encryption as shown in the previous section.

Summary

The DMK is the basis for encryption inside a database. There are a few administrative tasks you can perform with the DMK, and this article has shown how to create, drop, encrypt, backup, and restore the keys. As noted above, please be sure that you not only back up your keys, but that you protect and manage your passwords for the keys and backups appropriately.