What is DATABASE MASTER KEY in SQL?

In this article I am going to explain What is Database master key (DMK) in SQL.

*DMK = Database Master Key

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 we created DMK, it is encrypted by using the AES_256 algorithms and a user-supplied password.

Every database can have a separate DMK and each database can have only one DMK. 

Syntax

CREATE MASTER KEY ENCRYPTION BY PASSWORD='password' 

 

Now let’s create DMK for ASPMANTRA database.

Create DMK

use aspmantra 
go 
CREATE MASTER KEY ENCRYPTION BY PASSWORD='AspmantraDB@#2020' 

 

Now let’s check the DMK of database

use Aspmantra 
select * from sys.symmetric_keys

 

Alter & Close the DMK

We can close the created key, first we need to remove the encryption using below given query :

use Aspmantra 
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY  
CLOSE MASTER KEY

 

Open the DMK

We can open the DMK with the defined password and start the encryption by the SERVICE MASTER KEY(SMK). Run the below query:

use Aspmantra 
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'AspmantraDB@#2020' 
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY 

 

Regenerate the password for DMK

use Aspmantra 
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'AspmantraDB@#'

 

Drop DMK

use Aspmantra 
DROP MASTER KEY