locked
a question about database master key RRS feed

  • Question

  •  

        The database master key is not protected by the service master key by default if database was restore to a new sql server instance.

        We should reprotected manually by running following code:

         OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
         ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

     

     

    But I found it's inconsistent from the sys.key_encryptions dmv.

    I query the dmv after i attach the database, it indicate that the database master key was encrypt by master key

    key_id   thumbprint crypt_type crypt_type_desc                                     crypt_property

    101       0x01          ESKM      ENCRYPTION BY MASTER KEY     0x058CA1D44406C0BD4901AA2923921F831728C414DA5FF390

     

    I didn't execute the forementioned code when i query the dmv.

    Is it a bug? 

    Please correct me if  I misunderstand.

    Monday, October 13, 2008 3:09 AM

Answers

  •   It is not a bug, the DB Master Key (DBMK) is still protected by the previous SQL Server instance Service Master Key (SMK). If the database is to be reattached to the old system the DBMK would still be available automatically.

     

      The DB itself is oblivious of which SQL server instance where it has been attached to; but since the SMKs don't match the attempts to open the key by the new SMK will fail, leading to the perception that the DBMK is not protected by the SMK. For practical purposes this perception is correct, but it not reflected in the catalog views.

      I hope this answers your question,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Monday, October 13, 2008 5:17 PM

All replies

  •   It is not a bug, the DB Master Key (DBMK) is still protected by the previous SQL Server instance Service Master Key (SMK). If the database is to be reattached to the old system the DBMK would still be available automatically.

     

      The DB itself is oblivious of which SQL server instance where it has been attached to; but since the SMKs don't match the attempts to open the key by the new SMK will fail, leading to the perception that the DBMK is not protected by the SMK. For practical purposes this perception is correct, but it not reflected in the catalog views.

      I hope this answers your question,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Monday, October 13, 2008 5:17 PM
  • thanks.

     

    Tuesday, October 14, 2008 2:57 AM