OPEN MASTER KEY DECRYPTION BY PASSWORD = Separate Passwords

If we needed to open this key, because we were accessing another key that was protected by the DMK, we could open it by specifying the password Once that is done, we can close the DMK with this code We do this with.ĪLTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY We can close this key, but first we need to remove the encryption by the SMK. This key is also opened automatically by the instance because the SMK has encryped it (as seen above in the hierarchy).

I can then move forward and create additional keys that are protected by the DMK. This password must conform to the security requirements of the Windows host.īy default, this master key is also protected by the Service Master Key. For example, I'll create a database below and then add a DMK.ĬREATE MASTER KEY ENCRYPTION BY PASSWORD = code creates the database I will use in this article (MySampleDB) and then creates a master key with a password. You can use the CREATE MASTER KEY syntax to do so.

You can change this and we will look at it below. You can, and need to in some cases, create a DMK in the master database for some operations.Īs the diagram shows above, your DMK will be protected by the Service Master Key (SMK) by default. You can even create master keys in any of the system databases, but I would not recommend doing so in model or tempdb as these could cause you issues at a later time. This means you can have a separate DMK in each database on your instance. The DMK is the basis for encryption inside each database. The basis is the DPAPI (Data Protection API) at the Windows layer and this continues through the various keys available in the databases. The encryption hierarchy inside SQL Server is in Books Online, the image of which is reproduced below. This article will examine the basics of the DMK, how it is used and how you can ensure you don't lose access to your data.

The basis for security in many of your databases is the Database Master Key (DMK). SQL Server includes a number of encryption features and capabilities that you can use to secure your systems.

Sql server master database