If I backup my Certificates, why do I need to backup the DMK?

Question

If I have a server where I use TDE, configured as follows:

USE [master]
CREATE DATABASE MyTDE
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My57r0ngp455';
CREATE CERTIFICATE MyTDECert WITH SUBJECT = 'My Certificate';
GO
/* enable TDE */
USE MyTDE;
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE MyTDECert;
GO
ALTER DATABASE MyTDE SET ENCRYPTION ON;
GO
/* backup the TDE cert */
USE [master];
BACKUP CERTIFICATE [MyTDECert] TO FILE = 'C:\Test\TDECert.cert'
WITH PRIVATE KEY (
                   FILE = 'C:\Test\TDEcert.key', 
                   ENCRYPTION BY PASSWORD = 'MyStr0ngP455w0rd'
                  );

and I backup nightly on a schedule:

BACKUP DATABASE MyTDE
TO     DISK = 'C:\Test\TDEDatabaseBackup.bak'
WITH   INIT,
       FORMAT;

If the server this database is running on has some sort of disaster, I would need to restore the backups to another server and to do this, I would need the backup of MyTDECert to recreate that certificate on the new server

On the new server, I can create the TDE Certificate from a backup:

USE [master];
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My57r0ngp4552';
CREATE CERTIFICATE MyTDE1
FROM FILE = 'C:\Test\TDEcert.cert'
WITH PRIVATE KEY (FILE = 'C:\Test\TDEcert.key',
DECRYPTION BY PASSWORD = 'MyStr0ngP455w0rd');

and this allows me to restore the database:

RESTORE DATABASE MyTDE
FROM     DISK = 'C:\Test\TDEDatabaseBackup.bak'

Similarly, if I have a database that is not using TDE, but I backup using a certificate:

USE [master]
CREATE DATABASE MyDB
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My57r0ngp455';
CREATE CERTIFICATE [BackupCert] WITH SUBJECT = 'Backup Certificate', EXPIRY_DATE = '2022-01-29T15:00:00'
BACKUP CERTIFICATE [BackupCert] TO FILE = 'C:\Test\BackupCert.cert' 
WITH PRIVATE KEY (
                   FILE = 'C:\Test\backupcert.key', 
                   ENCRYPTION BY PASSWORD = 'MyStr0ngP455w0rd'
                  );
BACKUP DATABASE MyDB
TO     DISK = 'C:\Test\EncryptedDBBackup.bak'
WITH   INIT,
       ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupCert),
       FORMAT;

Again, to restore these backups to another server, I need the backup of BackupCert to create the certificate on the second server:

USE [master];
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My57r0ngp4552';
CREATE CERTIFICATE [BackupCert]
FROM FILE = 'C:\Test\STEVETEST\BackupCert.cert'
WITH PRIVATE KEY (FILE = 'C:\Test\backupcert.key',
DECRYPTION BY PASSWORD = 'MyStr0ngP455w0rd');
RESTORE DATABASE MyDB
FROM     DISK = 'C:\Test\EncryptedDBBackup.bak'

What I don't understand is, would I need to back up the database master key in the master database? I can still use the certificates as I have backed them up and can recreate them on another server and they have the correct thumbprint to restore the encrypted backups

Looking at this post it suggests that if I move the database in which the key is created to another server, I would need to re-create the master key from a backup, but in my case I am not moving the master database itself.

Is backing up the DMK needed in the scenarios above?

Charlieface · Accepted Answer · 2022-01-05T12:27:52.013

No, you do not need to for databases that do not have TDE.

You are confusing two different things:

The database master key
- Databases that do not use encryption:
  
  If you backup the certificate separately then this does not normally need backing up. You would just restore the whole DB from a backup with its certificate. You only need to back this up if you are protecting secrets within the database that you have no other backup of (such as a DEK or other symmetric key).
- Databases that use any form of encryption, such as TDE or CLE
  
  The master key protects the other encryption key. So you may be at risk of losing the DEK/other encryption keys, therefore you must backup the master key in order to recover that info. You need the key even if you have a separate backup certificate.
The service master key
You only need to back up the service master key if you intend to restore the master database, and you don't have access to a master backup, only the .mdf files. Note that things like linked server credentials are stored here, so you may want to be able to recover these, but it is not normally necessary.

You do need to create a new service master key (created automatically) and database master key, before you restore the database certificate to the server, because it needs protecting there. But then you are able to restore the backup.

Backups of other databases are only encrypted using their own certificates. The database master key protects the certificate within the database headers (as well as the DEK for TDE-enabled databases). The service master key is what protects the database headers while on the server. Because the certificate is not in unencrypted form in the backup, there is never a need to protect it, so the master key is not used for that.

If I backup my Certificates, why do I need to backup the DMK?

1 Answers1