locked
Change database master key - 3.6.4 PERIODIC CRYPTOGRAPHIC KEY CHANGES RRS feed

  • Question

  • Hello to all,


    I am a new in the SQL Server 2005 encryption, my question is related about the master key.

    When we started using SQL Server 2005 encryption features I have provided a password in order to start, by now, we have around 100,000 records in order to compliance some regulations I need to change the database master key.

    Basically the requirement from the IT director was:

    "Every 5 Months I need to change the database master key, drop the old password and add a new password."

    But what happens if I drop the current password and put the new password in the database masterkey, all the records will be available to be decrypted using the new password?
    or
    First I need, before to drop the old password:
    1.- Decrypt all records using the current password.
    2.- Drop the current password.
    3.- Add the new password.
    4.- ReEncrypt all records using the current password.

    Particulary we are trying to be compliance vith PCI DSS Standard, and I need to put in place a procedure un order to compliance with the 3.6.4  PERIODIC CRYPTOGRAPHIC KEY CHANGES requirement.

    Thank you.
    Saturday, May 16, 2009 2:23 AM

Answers

  • You should not need to do all this. SQL Server will handle the decrypt automatically if you use ALTER MASTER KEY. For instance:

    ALTER MASTER KEY
    REGENERATE WITH ENCRYPTION BY PASSWORD = 'MyN3wP3ssw0rd!';
    

    Make sure you do not use the FORCE option as that will regenerate the key even if all the data can't be decrypted. In that case, obviously data will be lost.

    More here: Books Online - ALTER MASTER KEY


    K. Brian Kelley, http://www.truthsolutions.com/
    • Marked as answer by misael7719 Saturday, May 16, 2009 6:06 PM
    Saturday, May 16, 2009 2:50 PM

All replies

  • You should not need to do all this. SQL Server will handle the decrypt automatically if you use ALTER MASTER KEY. For instance:

    ALTER MASTER KEY
    REGENERATE WITH ENCRYPTION BY PASSWORD = 'MyN3wP3ssw0rd!';
    

    Make sure you do not use the FORCE option as that will regenerate the key even if all the data can't be decrypted. In that case, obviously data will be lost.

    More here: Books Online - ALTER MASTER KEY


    K. Brian Kelley, http://www.truthsolutions.com/
    • Marked as answer by misael7719 Saturday, May 16, 2009 6:06 PM
    Saturday, May 16, 2009 2:50 PM
  • Hello Brian,

    Thank you for your answer.

    Sounds great when you say I do not need to decrypt a re-encrypt the data.

    I will review the command using the BOL.

    Thank you very much again.

    Best Regards

    Saturday, May 16, 2009 6:06 PM