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

Question

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

Answer 1

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

Answer 2

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;