How to Create a Database Master Key

2014-04-27 - Cryptography, Encryption Hierarchy, General, Security, Series

Introduction

The database master key is the root of the encryption hierarchy within a single database. It is a symmetric key that is stored in its database and that can be used to protect certificates and asymmetric keys. The database master key itself is protected with one or more passwords and additionally with the service master key.

Creating the Database Master Key

The CREATE MASTER KEY statement can be used to create the database master key for the current database. The statement requires a password to be specified. That password has to comply with the local password policies.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '************';

After the key is created with this statement, it is protected by the specified password. However, it is also automatically protected by the service master key. You cannot change this behavior, but you can alter the key after it was created to drop the encryption by the service master key.

Does the Database Master Key exist?

To check if the database master key exists, you can use the sys.symmetric_keys catalog view like this:

SELECT * 
  FROM sys.symmetric_keys AS SK
 WHERE SK.name = '##MS_DatabaseMasterKey##';

On my machine, this statement produces the following output:

The Database Master Key

Summary

The database master key is at the root of the encryption hierarchy within a single SQL Server database and can provide protection for asymmetric keys and certificates. The database master key itself is protected by the service master key.

Encryption Hierarchy Series

This article is part of the Encryption Hierarchy series. Check out The SQL Server Encryption Hierarchy for an overview of the hierarchy and a list of all posts that are part of the series.

Categories: Cryptography, Encryption Hierarchy, General, Security, Series
Tags: , , , ,

One Response to How to Create a Database Master Key

  1. Pingback: How to Back Up the Database Mater Key - sqlity.net

Leave a Reply