7

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?

7

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.

  • Hi Max. Thanks for the great explanation. Do you happen to also know if the database is encrypted with AES 256, is the symmetric key stored as part of the database, and is this encrypted symmetric key also stored in backups? – Dave Goldsmith May 1 '18 at 0:18
  • TDE encryption keys are stored in the master database. That includes any backups of the master database. Having said that, the key in the master database is itself encrypted by the service master key which is not backed up in any SQL Server backup. – Max Vernon May 1 '18 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.