1

As it is said here it is a good practice to backup the database master key, because when the database is moved to another instance it will need the original DMK that has been used to protect the certificates in order to skip regeneration of the encrypted stuff.

In the context of database backup encryption, when the DMK is stored in the master database with the rest of the certificates used for backup encryption, do we need to create a backup of the DMK?

Also, I am wondering, as it said that

When it is created, the master key is encrypted by using the AES_256 algorithm and a user-supplied password.

Does this mean, that if I used same password to create and protect each DMK in my master databases across all instances, the key will be the same?

If not, it is OK to create a DMK on one database, and to restore it on all instances in order to have the same key everywhere and to need make a backup of only one key?

1

As it is said here it is a good practice to backup the database master key, because when the database is moved to another instance it will need the original DMK that has been used to protect the certificates in order to skip regeneration of the encrypted stuff.

That answer isn't factually correct. When the database is migrated, the DMK was encrypted with the old SMK and the password. You can open the DMK with the password on the new instance and add decryption by the new SMK. This doesn't require that you decrypt and encrypt the securables protected by the DMK again, since the actual DMK key didn't change - just what is protecting it.

In the context of database backup encryption, when the DMK is stored in the master database with the rest of the certificates used for backup encryption, do we need to create a backup of the DMK?

Depends but most likely you won't directly need it unless there is something really, really bad that happens. Then you'll need to restore the DMK over the other one but that's not ideal, fun, or easy, either. If you have a backup of the master database then it already holds the DMK, additionally you will need a safe place to store the password. Since master is a special database, the use cases of this are much smaller than a user database.

Does this mean, that if I used same password to create and protect each DMK in my master databases across all instances, the key will be the same?

No, there is an initialization vector that is used so the keys won't be the same. It's not based off of the password used.

If not, it is OK to create a DMK on one database, and to restore it on all instances in order to have the same key everywhere and to need make a backup of only one key?

Definitely not ok. Is it ok to have just one password for everything from your email, online banking, and fantasy football account? Probably not. Probably not good to have a single key that opens your car, front door, mailbox, gate, bank deposit box, etc.

  • Yes, having single key that opens my car, front door, mailbox, gate, bank deposit box, etc. may be not good, but having single key that opens my 30 cars works perfectly for me - imagine a safe full will 30 cars keys and you trying to find particular one. What about making 30 duplicates of this cars keys in order and save them in another box in case the first ones are not available by any reason? Having one for all seems perfectly fine and we do not need to have backups of them - nice. – gotqn Nov 16 '17 at 21:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.