Backing Up the Service Master Key and Database Master Keys

Back up the service master key before you need it, because a good database backup does not contain it. The key lives apart from your data, and so does your chance of reading encrypted data after a rebuild.

The restore that opened nothing

Picture a 3 AM server rebuild. The database restores fine. The backups are all there. Then the encrypted columns will not open, and someone asks, “Where is the key file?” Nobody wrote one down.

Two keys matter here. The service master key belongs to the whole instance and lives in master. A database master key belongs to one database. Let me create a demo database and look at both.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;
GO
SELECT DB_NAME() AS DatabaseName, name
FROM sys.symmetric_keys
WHERE name = N'##MS_DatabaseMasterKey##';

SELECT name, key_length, algorithm_desc
FROM master.sys.symmetric_keys
WHERE name = N'##MS_ServiceMasterKey##';

The first query returns nothing, because the new database has no master key yet. The second shows the service master key: ##MS_ServiceMasterKey##, 256 bits, AES_256. Every instance has one from the start, which is why people forget it exists.

Create a database master key and see who protects it

The demo password below is only for this demo. In real life, pick a long one and keep it in your password vault, not in a script.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'Demo!Passw0rd#2026';

SELECT s.name, k.crypt_type_desc
FROM sys.symmetric_keys AS s
JOIN sys.key_encryptions AS k ON k.key_id = s.symmetric_key_id
WHERE s.name = N'##MS_DatabaseMasterKey##'
ORDER BY k.crypt_type_desc;

You get two rows. One says ENCRYPTION BY MASTER KEY, which is the service master key opening it automatically. The other says ENCRYPTION BY PASSWORD V2, which is your password. That first copy is convenient on this server. It is not something you can carry to another one, so the password and the exported files matter.

Export both keys to files

This demo writes two key files into the default backup folder of your instance, so the SQL Server service account can already write there. The demo creates a database and two files and removes all of them at the end. If you prefer another folder, create it first and give the service account write access.

DECLARE @folder nvarchar(260) =
    CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultBackupPath')) + N'\';
DECLARE @smk nvarchar(300) = @folder + N'SqlAuthorityDemo_SMK.key';
DECLARE @dmk nvarchar(300) = @folder + N'SqlAuthorityDemo_DMK.key';
DECLARE @sql nvarchar(max);

SET @sql = N'BACKUP SERVICE MASTER KEY TO FILE = N''' + @smk
         + N''' ENCRYPTION BY PASSWORD = N''Demo!Passw0rd#2026'';';
EXEC (@sql);
SET @sql = N'BACKUP MASTER KEY TO FILE = N''' + @dmk
         + N''' ENCRYPTION BY PASSWORD = N''Demo!Passw0rd#2026'';';
EXEC (@sql);

SELECT N'Service master key file' AS Artifact, file_exists FROM sys.dm_os_file_exists(@smk)
UNION ALL
SELECT N'Database master key file', file_exists FROM sys.dm_os_file_exists(@dmk);

Both rows show file_exists 1. The files are encrypted with the password, so copy them off the server and store the password somewhere else. A file on the same disk as the server dies with the server.

Prove the file works by restoring it

A backup you never restored is a hope. So drop the database master key and bring it back from the file.

DROP MASTER KEY;
SELECT COUNT(*) AS KeysLeft
FROM sys.symmetric_keys
WHERE name = N'##MS_DatabaseMasterKey##';
GO
DECLARE @dmk nvarchar(300) =
    CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultBackupPath')) + N'\SqlAuthorityDemo_DMK.key';
DECLARE @sql nvarchar(max) = N'RESTORE MASTER KEY FROM FILE = N''' + @dmk
    + N''' DECRYPTION BY PASSWORD = N''Demo!Passw0rd#2026''
       ENCRYPTION BY PASSWORD = N''Demo!Passw0rd#2026'';';
EXEC (@sql);
SELECT COUNT(*) AS KeysLeft
FROM sys.symmetric_keys
WHERE name = N'##MS_DatabaseMasterKey##';

KeysLeft is 0 after the drop and 1 after the restore. The exported file really works. I never restore the service master key in a demo. That one changes the root of the whole key hierarchy, so test it only on a spare instance.

Clean up

Drop the key, delete the two files, and remove the database. The helper below deletes one named file, so it cannot touch anything else. You can also delete the files in Explorer.

DROP MASTER KEY;
GO
DECLARE @folder nvarchar(260) =
    CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultBackupPath')) + N'\';
DECLARE @smk nvarchar(300) = @folder + N'SqlAuthorityDemo_SMK.key';
DECLARE @dmk nvarchar(300) = @folder + N'SqlAuthorityDemo_DMK.key';
EXEC master.sys.xp_delete_files @smk;
EXEC master.sys.xp_delete_files @dmk;

SELECT N'Service master key file' AS Artifact, file_exists FROM sys.dm_os_file_exists(@smk)
UNION ALL
SELECT N'Database master key file', file_exists FROM sys.dm_os_file_exists(@dmk);
GO
USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Both file checks now return 0. For a real recovery plan, add the certificates that protect your encrypted data, your database backups and a restore you have actually tried. Keys alone are not a recovery plan.

Do this while the original server is still healthy.

A key file is not a recovery plan, it is one piece of the plan.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.