How to back multiple master database key DEK on SQL with TDE Enable

Question

I have an SQL instance with multiple data bases. Each of them are TDE encrypted.

I know that all data bases use the same service master key, SMK, to encrypt tempdb. I have back up the SMK using:

BACKUP SERVICE MASTER KEY
TO FILE = 'tmp-path.key'
ENCRYPTION BY PASSWORD = 'temp-password';

Now, how do I back up the distinct database master key, DMK? Each data base use a different one, but the SQL statement doesn't allow to specify which one to back up. Next, is the command I'm running.

BACKUP MASTER KEY
TO FILE = 'tmep-path.key'
ENCRYPTION BY PASSWORD = 'temp-passowrd'

I though that by:

use [specific-db];
GO

It will back up an specific one, but the command won't run. You need to be on master, to run the command.

Thanks

Answer 1

The way TDE is configured in SQL Server is as follow:

1. Create Master key in master database
2. Create Certificate using the master key
3. Create Database Encryption key using the certificate

There are only two things you need to/can backup here:

1. Master Encryption Key - The script you have shown in your question will do that.

   BACKUP MASTER KEY
   TO FILE = 'tmep-path.key'
   ENCRYPTION BY PASSWORD = 'temp-passowrd'
   

2. Certificate - You can either create a separate Certificate for each database to be encrypted or a single certificate to encrypt all database, You will create and backup the certificate as follow:

   -- Create Certificate
   USE Master;
   GO
   CREATE CERTIFICATE TDE_MyDB_Cert 
   WITH SUBJECT = 'My TDE Certificate for MyDB user database'
   GO
   

This certificate is protected by the service master key.

    -- Encrypt Database using the Certificate
    USE [MyDB]  
    GO  
    CREATE DATABASE ENCRYPTION KEY  
    WITH ALGORITHM = AES_128  
    ENCRYPTION BY SERVER CERTIFICATE TDE_MyDB_Cert 
    GO 

Now you need to backup the certificate with the private key for the database which you would do, doing:

USE master;  
GO  
BACKUP CERTIFICATE TDE_MyDB_Cert   
TO FILE = 'C:\TDE_MyDB_Cert_File.cer'  
WITH PRIVATE KEY (FILE = 'C:\TDE_MyDB_Cert_PKey.pvk' ,  
ENCRYPTION BY PASSWORD = 'S0M34tR0nGP@$$w)rd' )  
GO

1. TDE_MyDB_Cert_File.cer Is the certificate backup
2. TDE_MyDB_Cert_PKey.pvk is the backup for Private key for the database