Encrypting Your Valuable Data With SQL Server 2005: Part 1

Database Master Key (DMK)

The Database Master Key is the next level in the SQL Server 2005 encryption hierarchy. Unlike the SMK, the DMK will not automatically be created when the database is created. Where a database server has only one SMK, it can have many DMKs — one for each database, if created.

You need CONTROL permission on the database to perform operations with the DMK except for CLOSE DMK. CONTROL permission confers owner-like permissions that effectively grant all defined permissions to the object and all objects in its scope, including the ability to grant other grantees permissions.

The DMK is a symmetric key used to protect the private keys of certificates and asymmetric keys (which we will discuss in detail in the next article) that are present in the database. When the DMK is created, it is encrypted using the Triple DES algorithm with a password that was supplied while creating it.

USE EncryptionDB;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'encrypt10N4DMK';
GO

You cannot create the DMK again for the same database without dropping the existing one in both the database and the master database. To drop the DMK:

USE EncryptionDB;
DROP MASTER KEY;
GO

Just after the creation of the DMK, make sure that you back it up and store it in an off-site location like the SMK.

USE EncryptionDB;
BACKUP MASTER KEY TO FILE = 'c:\keys\DMK EncryptionDB'
ENCRYPTION BY PASSWORD = 'DMKPa$$w0rD';

After backing up the DMK, the next obvious operation should be restoring it. However, this is bit different from the SMK. For the DMK, you have to provide two passwords.

USE EncryptionDB;
RESTORE MASTER KEY
FROM FILE = 'c:\keys\DMKEncryptionDB'
DECRYPTION BY PASSWORD = 'DMKPa$$w0rD'
ENCRYPTION BY PASSWORD = 'ENC=DMKPa$$w0rD' FORCE;
GO

DECRYPTION BY PASSWORD is the password that you used to encrypt the DMK and ENCRYPTION BY PASSWORD specifies the password that is used to encrypt the DMK after it has been loaded into the database.

The FORCE parameter acts the same as with the SMK. Like the SMK, restoring the DMK will re-generate all the certificates and keys that were encrypted by the DMK. As this is a resource-intensive operation, you should schedule it accordingly.

If there is no master key in the current database, RESTORE MASTER KEY creates a new DMK. You also have the option of altering the DMK.

USE EncryptionDB;
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'encrypt10N4DMK';
GO

Rather than changing the password, you can add another password so you can use both passwords to open the DMK. However, you cannot use the same password twice.

USE EncryptionDB;
ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = 'S2encrypt10N4DMK'
GO

The new DMK will not be automatically encrypted with the SMK. But you can do this.

USE EncryptionDB;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO

If the DMK was encrypted with the SMK, it will be automatically opened when it is needed for decryption or encryption. In this case, it is not necessary to use the OPEN MASTER KEY statement.

Similarly, you can DROP this by using DROP ENCRYPTION BY SERVICE MASTER KEY.

Mind you, this is a resource intensive operation.

By executing the following code in the database, you can view information such as creation date and modified date for the DMK.

SELECT * FROM sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##'

Summary

Encryption, the last wall that hackers have to penetrate, has significant features in SQL Server 2005. This article has introduced Service Master Key and Database Master Key operations. The next article will describe implementation. All the encryption methods are based on the Service Master Key and the Database Master Key.