SQL Server – BACKUP MASTER KEY Failing Due to Missing Symmetric Master Key

sql servertransparent-data-encryption

I'm trying to backup the master key for a TDE database, but SQL Server says there isn't one. It's a bit weird, but I'm sure I'm just doing something wrong. I'm sysadmin on the server, so I should be able to see everything.

This is the statement that is failing:

USE [my_db];
BACKUP MASTER KEY
TO FILE = 'C:\master_key'
ENCRYPTION BY PASSWORD = 'some_killer_password';

And the error message returned:

Msg 15151, Level 16, State 1, Line 11
Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.

I've used the following to see the details about the database encryption key, and the associated certificate, however the certificate details from the sys.certificates table is empty.

USE [my_db];
SELECT DatabaseName = d.name
    , ddek.encryptor_type
    , ddek.opened_date
    , c.name
    , c.cert_serial_number
    , c.pvt_key_encryption_type_desc
    , c.subject
FROM sys.dm_database_encryption_keys ddek
    INNER JOIN sys.databases d ON ddek.database_id = d.database_id
    LEFT JOIN sys.certificates c ON ddek.encryptor_thumbprint = c.thumbprint
WHERE d.name <> 'tempdb' /* tempdb is auto-encrypted by SQL Server */

╔══════════════╦════════════════╦═════════════════════════╦══════╦════════════════════╦══════════════════════════════╦═════════╗
║ DatabaseName ║ encryptor_type ║ opened_date             ║ name ║ cert_serial_number ║ pvt_key_encryption_type_desc ║ subject ║
╠══════════════╬════════════════╬═════════════════════════╬══════╬════════════════════╬══════════════════════════════╬═════════╣
║ my_db        ║ CERTIFICATE    ║ 2017-09-20 11:24:13.590 ║ NULL ║ NULL               ║ NULL                         ║ NULL    ║
╚══════════════╩════════════════╩═════════════════════════╩══════╩════════════════════╩══════════════════════════════╩═════════╝

So, I can see the database encryption key in [my_db], and I can see it's encrypted by a certificate, but the certificate doesn't exist?

Best Answer

The certificate used by TDE to encrypt the database encryption key is actually stored in the master database, which is in turn encrypted by the database master key stored in the master database.

The query to see which certificate is used to decrypt the my_db TDE-encrypted database should be:

SELECT DatabaseName = d.name
    , ddek.encryptor_type
    , ddek.opened_date
    , c.name
    , c.cert_serial_number
    , c.pvt_key_encryption_type_desc
    , c.subject
FROM sys.dm_database_encryption_keys ddek
    INNER JOIN sys.databases d ON ddek.database_id = d.database_id
    LEFT JOIN master.sys.certificates c ON ddek.encryptor_thumbprint = c.thumbprint
WHERE d.name <> 'tempdb' /* tempdb is auto-encrypted by SQL Server */

Note, the only change is to reference sys.certificates in the master database.

The results of that query shows:

╔══════════════╦════════════════╦═════════════════════════╦════════════════════╦════════════════════╦══════════════════════════════╦═══════════════════════════╗
║ DatabaseName ║ encryptor_type ║ opened_date             ║ CertName           ║ cert_serial_number ║ pvt_key_encryption_type_desc ║ Certsubject               ║
╠══════════════╬════════════════╬═════════════════════════╬════════════════════╬════════════════════╬══════════════════════════════╬═══════════════════════════╣
║ my_db        ║ CERTIFICATE    ║ 2017-09-20 11:24:13.590 ║ db_encryption_cert ║ <redacted>         ║ ENCRYPTED_BY_MASTER_KEY      ║ DB Encryption Certificate ║
╚══════════════╩════════════════╩═════════════════════════╩════════════════════╩════════════════════╩══════════════════════════════╩═══════════════════════════╝

Note the query shows the certificate is ENCRYPTED_BY_MASTER_KEY - the master key referenced here is the master key for the master database.

In order to restore my_db onto another server, you'll need to backup the the certificate (with its private key) used to encrypt the database, then restore it onto the target SQL Server.

This should happen on the source SQL Server:

BACKUP CERTIFICATE db_encryption_cert
TO FILE = 'C:\db_encryption_cert'
WITH PRIVATE KEY (
    FILE = 'C:\db_encryption_cert_private_key'
    , ENCRYPTION BY PASSWORD = 'new private key password'
    );

Store the resulting certificate file and private key and it's password on a secure file system offsite.

This should happen on the target SQL Server:

CREATE CERTIFICATE db_encryption_cert
FROM FILE = 'C:\db_encryption_cert'
WITH PRIVATE KEY (
    FILE = 'C:\db_encryption_cert_private_key'
    , DECRYPTION BY PASSWORD = 'new private key password'
    );

Once you've created the certificate on the target server, you should be able to restore the database without issue.

If you are preparing for disaster recovery, and intend on being able to restore the source SQL Server's master database, etc, you should also backup the service master key and the master database master key:

USE master;
GO
BACKUP SERVICE MASTER KEY 
TO FILE = 'C:\service_master_key'
ENCRYPTION BY PASSWORD = 'new service master key password';

BACKUP MASTER KEY
TO FILE = 'C:\master_database_master_key'
ENCRYPTION BY PASSWORD = 'new master database master key password';

These keys, and their associated encryption passwords, should be stored in a secure location off-site.

Related Question