Database Master Key (DMK)

Note: Is is the Database Master Key (DMK). It is not the Service Master Key (SMK).

The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. When it is created, the master key is encrypted by using the AES_256 algorithm and a user-supplied password.

Database master key is stored in both the database where it is used and in the master system database. The copy of the DMK stored in the master system database is silently updated whenever the DMK is changed.

The database master key is used to encrypt other keys and certificates inside a database. If it is deleted or corrupted, SQL Server may be unable to decrypt those keys, and the data encrypted using them will be effectively lost. For this reason, you should back up the database master key and store the backup in a secure off-site location.

The master key must be open and, therefore, decrypted before it is backed up. If it is encrypted with the service master key, the master key does not have to be explicitly opened. But if the master key is encrypted only with a password, it must be explicitly opened.

We recommend that you back up the master key as soon as it is created, and store the backup in a secure, off-site location.

When the master key is restored, SQL Server decrypts all the keys that are encrypted with the currently active master key, and then encrypts these keys with the restored master key. This resource-intensive operation should be scheduled during a period of low demand. If the current database master key is not open or cannot be opened, or if any of the keys that are encrypted by it cannot be decrypted, the restore operation fails.

If any one of the decryptions fails, the restore will fail. You can use the FORCE option to ignore errors, but this option will cause the loss of any data that cannot be decrypted.

If the master key was encrypted by the service master key, the restored database master key will also be encrypted by the service master key.

If there is no master key in the current database, RESTORE MASTER KEY creates a master key. The new master key will not be automatically encrypted with the service master key.

Create a Database Master Key

-- Creates the database master key.
  -- The key is encrypted using the password "23987hxJ#KL95234nl0zBe".  
  CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';

Backup a Database Master Key

-- Creates a backup of the "AdventureWorks2012" master key. Because this master key is not encrypted by the service master key, a password must be specified when it is opened.  
USE AdventureWorks2012;   
GO  

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'sfj5300osdVdgwdfkli7';   

BACKUP MASTER KEY TO FILE = 'c:\temp\exportedmasterkey'   
    ENCRYPTION BY PASSWORD = 'sd092735kjn$&adsg';   
GO

Restore a Database Master Key

-- Restores the database master key of the AdventureWorks2012 database.  
USE AdventureWorks2012;  
GO  

RESTORE MASTER KEY   
    FROM FILE = 'c:\backups\keys\AdventureWorks2012_master_key'   
    DECRYPTION BY PASSWORD = '3dH85Hhk003#GHkf02597gheij04'   
    ENCRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD';  
GO