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:SQL2019certbk.cert' WITH PRIVATE KEY ( FILE = 'C:SQL2019certkey.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.