Working with SQL Server Database Master Keys

SQL Server uses an encryption hierarchy to protect secrets such as credentials, asymmetric keys and certificates. At the database level, that hierarchy is anchored by the database master key (DMK).

Because all other encrypted objects depend on it, losing access to the DMK can render those objects unusable.

This post walks through how to:

• Check whether a database has a master key
• Create a database master key
• Open it manually when required
• Back it up safely
• Restore it when things go wrong

It also explains why saving the password and backing up the DMK are non-negotiable from an operational standpoint.

---

What the Database Master Key Is Responsible For

The DMK is a symmetric key stored in each database; it protects:

• Certificate private keys
• Asymmetric keys
• Database‑scoped credentials
• Other secrets that rely on SQL Server encryption

When you create a DMK, you supply a password. By default, SQL Server also encrypts the DMK using the instance’s service master key.

The dual encryption lets SQL Server open the DMK automatically during normal operation, while still giving you a way to recover it if the database is moved to another instance.

---

Checking Whether a Database Has a Master Key

Before creating anything, check what exists.

To see whether a database master key is encrypted by the service master key, query sys.databases:

-- Check whether a database master key is encrypted by the server
SELECT
    d.name,
    d.database_id,
    d.state_desc,
    d.is_read_only,
    d.is_auto_close_on,
    d.is_encrypted,
    d.is_master_key_encrypted_by_server,
    d.create_date
FROM sys.databases AS d
ORDER BY d.name;

A value of:

• 1 means the DMK exists and is encrypted by the service master key.
• 0 means the DMK exists but is not encrypted by the service master key.
• NULL means no DMK exists in that database.

You can also list keys directly. In each database, the DMK appears as ##MS_DatabaseMasterKey## in sys.symmetric_keys:

-- List symmetric keys in the database
-- The database master key appears as ##MS_DatabaseMasterKey##
USE [Database01];
GO
SELECT *
FROM sys.symmetric_keys;

If a database master key exists, it will appear as ##MS_DatabaseMasterKey##.

---

Creating a Database Master Key

If the database does not already have a DMK, create one using a strong password. Because the DMK is database‑scoped, you need to run this per database:

-- Create a database master key encrypted by a strong password
-- This password is critical and must be stored securely
USE [Database01];
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'StrongPasswordHere';

After creation, back up the DMK immediately (see below) and record the password in a secure password manager.

The password is your only guaranteed recovery mechanism if the service master key cannot decrypt the DMK (for example, after restoring the database to another instance).

Microsoft Docs: Create Master Key

---

Opening the Master Key Manually

When a DMK is encrypted by the service master key, SQL Server can open it automatically. However, you may need to open it manually:

• After restoring the database to a new server where the service master key doesn’t match.
• During troubleshooting of encryption‑related errors.
• When automatic decryption is disabled.

To open it, supply the original password:

-- Open the database master key using the password
USE [Database01];
GO
OPEN MASTER KEY
DECRYPTION BY PASSWORD = 'StrongPasswordHere';

Once opened, SQL Server can access protected objects until the key is closed or the session ends.

Microsoft Docs: OPEN MASTER KEY

---

Backing Up the Database Master Key

Backing up the database itself is not sufficient to protect encrypted data. The DMK must be backed up separately.

Create the backup while the master key is open. Use a different password to encrypt the backup file:

-- Back up the database master key to a secure location
-- The backup password should be different from the master key password
USE [Database01];
GO
BACKUP MASTER KEY
TO FILE = 'C:\SecureLocation\DatabaseMasterKey.bak'
ENCRYPTION BY PASSWORD = 'BackupPasswordHere';

This backup file, combined with its password, is what allows you to recover encrypted objects if the database is moved or restored.

Operational guidance:

• Store the backup off the database server
• Protect it like credentials or certificates
• Store the password securely and separately

Microsoft Docs: BACKUP MASTER KEY

---

Restoring a Database Master Key

If SQL Server cannot open the DMK automatically after a restore, you may need to restore it from a backup.

Provide:

• A new password to encrypt the restored DMK
• The backup password to decrypt the backup file

-- Restore the database master key from backup
-- The DECRYPTION password is the backup password
-- The ENCRYPTION password becomes the new master key password
RESTORE MASTER KEY
FROM FILE = 'C:\SecureLocation\DatabaseMasterKey.bak'
DECRYPTION BY PASSWORD = 'StrongPasswordHere'
ENCRYPTION BY PASSWORD = 'BackupPasswordHere';

This operation rewrites encryption for all dependent objects. Perform it deliberately and verify access to certificates and credentials afterward.

Microsoft Docs: RESTORE MASTER KEY

---

Re-Encrypting the Master Key with the Service Master Key

After restoring a DMK, it will no longer be encrypted by the current instance’s service master key.

To enable automatic opening, re-encrypt it. The DMK must be opened first:

-- Open database master key to complete & verify
OPEN MASTER KEY
DECRYPTION BY PASSWORD = 'StrongPasswordHere';

-- Encrypt the database master key by the Service Master Key
ALTER MASTER KEY
ADD ENCRYPTION BY SERVICE MASTER KEY;

Without this step, you would need to manually open the DMK using the password whenever encrypted objects are accessed.

---

Why the Password and Backup Matter

From an operational perspective, the DMK password and backup are not optional.

If you lose both:

• Encrypted credentials and certificates become unusable
• Features depending on encryption may fail silently or catastrophically
• Database migration or recovery may be impossible

This typically surfaces during rebuilds, availability group moves, or disaster recovery. When it does, there is no workaround.

Saving the password securely and backing up the DMK is part of owning the database.

---

Summary

The database master key sits at the root of SQL Server’s encryption hierarchy. As a DBA, you should always know:

• Whether a database has a DMK
• Whether it is encrypted by the service master key
• Where the backup is stored and who has the password

Treat the DMK seriously. If you do not, SQL Server will force the issue at the worst possible time.