Sql-server – Database master key and certificate for backup encryption – SQL Server

certificateencryptionsql server

I'm trying to understand backup encryption and I have created a master key (in the master DB) and used a password as follows:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test123!';
GO

Then I create a certificate as follows:

CREATE CERTIFICATE CertName
WITH SUBJECT = 'DB Backup Certificate';
GO

I backup this certificate as follows:

BACKUP CERTIFICATE CertName TO FILE = 'C:\SQL2019\certbk.cert'
WITH PRIVATE KEY (
FILE = 'C:\SQL2019\certkey.pvk',
ENCRYPTION BY PASSWORD = 'bkPofcert!')

Then I take a DB backup using this certificate, delete the DB, certificate and the master key and try to restore the DB which is not possible (I do understand this).

So I create the master key, restore the certificate from the certificate backup that was taken above and then it lets me restore the DB successfully.

My issue is that even if you provide a different password than what you originally used to create the master key (e.g. 'newpassword123!' instead of 'test123!', it will still let you restore the certificate and the DB. Isn't the certificate encrypted by the master key? If so, how does it let you proceed even with a different password for the master key? Also if this is the case, why do we need to backup the master key? When we can just create a new one with a different password and all works well.

I also tried to restore this DB on another instance (after creating a new master key with a different password and after restoring the certificate as per above) and it still allowed me to restore this DB. So this also eliminates the possibility that it lets me restore the encrypted DB (even with a different master key) due to the master key being encrypted (and hence "recognised") by the service master key – since on the other instance the service master key should be different.

Best Answer

Isn't the certificate encrypted by the master key?

Not exactly. To be more precise, the certificate private key is the one encrypted by the master key and you can see that under the Remarks section of the CREATE MASTER KEY doc:

The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database.

You can check sys.certificates to see that information as well:

SELECT name, pvt_key_encryption_type_desc, pvt_key_last_backup_date 
FROM sys.certificates;

cert info

how does it let you proceed even with a different password for the master key?

When you backed up the certificate using the option WITH PRIVATE KEY you added to the backup the actual key used to encrypt things referenced by that certificate. When the certificate was within SQL Server, its master key secured the certificate private key, but when you back it up the only protection seems to be that password for the backup ENCRYPTION BY PASSWORD = 'bkPofcert!'. If you try to backup the certificate and private key without a password like this:

BACKUP CERTIFICATE CertName TO FILE = 'C:\SQL2019\certbk.cert'
WITH PRIVATE KEY (
FILE = 'C:\SQL2019\certkey.pvk')

SQL Server throws the following error:

An encryption password must be provided to encrypt the private key of this certificate.

That means while the private key is being moved (by backing it up to restore it later) the only protection you get is that password from the backup, not the one from the master key. The password from the CREATE MASTER KEY command is in fact used when you need to use the command OPEN MASTER KEY.

As you have noticed by now, You shouldn't keep the certificate backup and the database backup at the same place for security reasons.

why do we need to backup the master key?

Because it encrypts objects (like the private keys used by the certificates) while they're still on the instance and if it was to be lost, things on the instance would not be decrypted.

The following backup of the certificate without its private key works, but it would not be enough to restore the database backup later as you did:

BACKUP CERTIFICATE CertName TO FILE = 'C:\SQL2019\CertName.cer';

The reason it does not restore the database properly is because the certificate misses the private key it used before.

Relevant material:
SQL Server Encryption - Youtube video by MVP Ben Miller
Encryption Hierarchy

Disclaimer: I provided this answer based on research and I'd be really glad if someone with more experience could confirm that my answer is appropriate because I understand encryption is a delicate subject and could cause someone to be locked out of its data if used without accurate knowledge.

Related Question