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:
- sys.databases says that the DMK is already encrypted (albeit with the source service's SMK)
- There is no way we can find to tell if the DMK is encrypted by the current SMK (checked all the encryption DMVs)
- 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?