Master Key questions
For a test, I've created a database master key.
Under what circumstances would I use OPEN MASTER KEY DECRYPTION?
Also, how would I go about encrypting the database master key (other than automatically via the SMK)? The reason I ask is because the BACKUP MASTER KEY states that the password is required if the DMK had been encrypted with a password. I used a password when creating the DMK and was able to back it up without providing the original password, so I'm assuming this is referencing an explicit encryption of the DMK?
Thanks,
Joe
By default, a database master key (DMK) will have two encryptions: a password encryption and an encryption with the service master key (SMK). There is always a password encryption but the SMK encryption can be dropped using the statement:
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
So, by default, because of the SMK encryption, the system can automatically decrypt the master key and only needs to do permission checks to verify the right to access it. If the SMK encryption is dropped, then the key must be opened manually using the OPEN statement before it can be used.
BACKUP MASTER KEY doesn't actually require a decryption key, it only requires that the DMK is either encrypted by the SMK or that it has already been opened using the OPEN statement. The password required by BACKUP MASTER KEY is for encrypting the key before it is written to the backup file. I suppose that you hit the first scenario where you had the DMK encrypted by the SMK and you didn't had to open it before backup.
As a side note, the encryption by the SMK can be added back using the following statement:
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
This requires the DMK to have been opened first.
Thanks
Laurentiu
