Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
2  
I suggest you open an item on connect –  Remus Rusanu Jan 13 '12 at 19:39
    
Well, if you don't have any tricks up your sleeve, maybe I should! I will link to it if I do. –  Anon246 Jan 13 '12 at 19:42
add comment

1 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

share|improve this answer
    
Unfortunately, the wording in BOL is inaccurate -- "encrypted by the service master key" should read "encrypted by a service master key." –  Jon Seigel Oct 16 '12 at 16:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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