sql-server add tag
Max (imported from SE)
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?
Top Answer
Max (imported from SE)
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.

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.