I'm trying to understand backup encryption and I have created a master key (in the master DB) and used a password as follows:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test123!';
Then I create a certificate as follows:
CREATE CERTIFICATE CertName
WITH SUBJECT = 'DB Backup Certificate';
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.