2 edited tags
| link
    Tweeted twitter.com/#!/StackDBAs/status/158329624802246656
source | link

DB Master Key Encrypted By *Correct* Service Master Key?

My client has frequently run process where we move databases from a prod environment to lower environments (refresh QA with prod database, etc). These DBs have database master keys on them. We have no issues re-encrypting these into the service master key on the destination server, but here's the annoying part:

  1. sys.databases says that the DMK is already encrypted (albeit with the source service's SMK)
  2. There is no way we can find to tell if the DMK is encrypted by the current SMK (checked all the encryption DMVs)
  3. The SMK signature in sys.symmetric_keys is always 0x01 (not unique across servers).

This isn't a problem, per se. The fix is always re-encrypt the DMK to the SMK. We just would like to be able to tell if it is already done or not. Anyone have any tricks up their sleeve?

Thanks, Eric