Database Master Key (DMK)

One database can have only one Database Master Key at a time.

Like Service Master Key, Database Master Key is also a symmetric key.

Database Master Key is created using CREATE MASTER KEY statement. It doesn’t generated automatically as like Service Master Key. The syntax to create a DMK is:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

For example:

USE TestDataBase 

GO 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLReality_1' 

In default settings a Database Master Key can be decrypted either by Service Master Key or by password that was used to create it. It helps in reducing the key administration task. Because when a database that has Database Master Key, is restored on same server, its DMK is automatically decrypted by Service Master Key without coming into the notice. In such cases password that is specified while creating the DMK are not used. But it doesn’t mean that password is just a value to create the DMK and through away.

Password that is used while creating a DMK plays its crucial role when database is restored on different server. Because the Service Master Key of new server can not decrypt the DMK, after restore the database we will have to decrypt the DMK with password as below:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

For example:

USE TestDataBase 

GO 

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SQLReality_1' 

Once decrypted by password we can alter it to decrypt by Service Master Key of new server so that it can automatically decrypt in future. For that execute following statement on new server:

USE TestDataBase 

GO 

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

We can also use the ALTER MASTER KEY statement to re-set the password of Database master key for security purpose or if forget.