locked
Database master key regenerate - should column data change? RRS feed

  • Question

  • Similar to the below post I needed to regenerate a database master key with a new password for PCI compliance.

    http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/35007069-32eb-425d-abcd-fdf6224d369b

    We ran the statement:  

    ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'NewPassword';

    We selected the encrypted column data (varbinary(84)) before running the regenerate statement and then did it again after running the regenerate statement.  We noticed that the data in the column did not change.  Is this normal and expected?  When testing in our release environment we did see the encrypted column data change so we just want to make sure there is no other issue and that the data was actually re-encrypted.

    When inserting data into the encrypted column we use the following statement that encrypts using a symmetric key in case this makes a difference:

    ENCRYPTBYKEY(KEY_GUID('SymmetricKeyName'), CAST(@CCnumber AS varchar(16)))

    Thank you for any help in advance.

    Thursday, May 30, 2013 1:30 PM

Answers

  • Hallo Addamse,

    NO - it doesn't have to change because the DMK is for the protection of the certificate which you need for the protection of the symetric key.

    So the symetric key doesn't change in any way - why should the encrypted values been changed :)
    To see the dependencies between SMK - DMK - Certificates - ... have a look to the really great video from Bob Beauchemin concerning encryption:

    http://technet.microsoft.com/en-us/sqlserver/gg429824.aspx


    Uwe Ricken

    MCM SQL Server 2008
    MCSE - SQL Server 2012
    MCSA - SQL Server 2012

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    • Marked as answer by addamse Friday, May 31, 2013 6:29 PM
    Friday, May 31, 2013 5:57 PM