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

  • 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

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

  • 1
    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

We just would like to be able to tell if it is already done or not. Anyone have any tricks up their sleeve?

Well, yes, actually.

As explained more fully in the following answer of mine on a very similar question:

How do I check that the database master key encryption is valid?

since the Service Master Key (the correct one) is needed to use the Database Master Key for operations that do not explicitly specify a password, you just need to attempt using the Database Master Key. If the operation succeeds, then the current Service Master Key is the correct one. But if it fails, then you know that the Database Master Key needs some other Service Master Key.

Two simple-enough options are:

  • If you have a Certificate that is guaranteed to exist in the Database being restored, try using it:

    USE [newly_restored_db];
    
    IF ( SIGNBYCERT( CERT_ID( '{certificate_name}' ), 'test' ) IS NULL)
    BEGIN
      OPEN MASTER KEY DECRYPTION BY PASSWORD = '{password}'; -- password used to protect DMK
      ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '{password}';
      CLOSE MASTER KEY;
      PRINT 'All fixed, yo!';
    END;
    ELSE
    BEGIN
      PRINT 'All good, yo!';
    END;
    
  • If no Certificate is guaranteed to exist in the Database being restored, then try to create one. If the SMK can be used to automatically decrypt the DMK, then the Certificate will be created, else the operation will fail:

    USE [newly_restored_db];
    
    BEGIN TRY
      CREATE CERTIFICATE [TestCert] WITH SUBJECT = 'yadda yadda yadda';
      DROP CERTIFICATE [TestCert];
      PRINT 'All good, yo!';
    END TRY
    BEGIN CATCH
      OPEN MASTER KEY DECRYPTION BY PASSWORD = '{password}'; -- password used to protect DMK
      ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '{password}';
      CLOSE MASTER KEY;
      PRINT 'All fixed, yo!';
    END CATCH;
    

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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