Sql-server – DB Master Key Encrypted By *Correct* Service Master Key

encryptionSecuritysql serversql-server-2008-r2t-sql

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

Best Answer

As per books online

"The is_master_key_encrypted_by_server column of the sys.databases catalog view in master indicates whether the database master key is encrypted by the service master key."

Here is the link http://technet.microsoft.com/en-us/library/ms174382.aspx

Related Question