SQL Server Database master keys and certificates for database encryption

 

SQL Server – Database master key and Certificates

Database Master Key and Password Protected Certificates

Things to remember about master Keys

-->Each sql server instance can have only 1 service master key and each database can have only 1 database master key

-- > we cannot rename master key as we want to...

--> this will be listed under master database

Syntax to check if the master key exists or not, if Not then create master key: 

IF (select Count(*) from master.sys.symmetric_keys where name like '%DatabaseMasterKey%') = 0

BEGIN

CREATE MASTER KEY  ENCRYPTION BY PASSWORD = 'test1234pwd'

END

Note: you need to be sysadmin or login with sa credentials to execute these queries

Once you created the master key you can view them under the security section of master database.

Why do we need master keys:

Certificates created with the CREATE CERTIFICATE statement are protected by the database master key. To be able to execute that statement, the database master key has to exist. If the database master key does not exist or if it cannot be opened, the CREATE CERTIFICATE statement will fail.

Alternatively, if you do not want the private key to be protected by the database master key, you can provide a password using the ENCRYPTION BY PASSWORD clause:

 ENCRYPTION BY PASSWORD = '**********'   WITH SUBJECT = ' SQLserverpwd112233';