locked
Create Symmetric Key - Why use Key_Source or Identity_Value RRS feed

  • Question

  • Re: Create Symmetric Key - Why use Key_Source or Identity_Value

    We are going to start using column encryption using a DMK, certificate and symmetric keys. Only the DMK will have a password. I have practiced backingup and restoring to the same server and another server and everything worked well using the following to create the key. I only needed to provide the DMK password on the other server.

    CREATE SYMMETRIC KEY CreditCards_Key_01
    ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE CreditCards;
    

    But now I am reading that you should use the Key_source and Identity_value when creating your key. When are they needed? And do you recommend using them? I am kind of hesitant because I think that the Identity_Value would be like a password and password management at my client sites is very lacking.

    CREATE SYMMETRIC KEY CreditCards_Key_01
    WITH KEY_SOURCE = 'A pass phrase from which to derive the key.',
    IDENTITY_VALUE = 'An identity phrase from which to generate a GUID for tagging data that is encrypted with a temporary key',
    ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE CreditCards;
    

    Tuesday, March 3, 2015 10:49 PM

Answers

  • OK, got it. You can use the exact same DMK password, certificate name and symmetric key name on two different databases but the symmetric keys are different. You will not be able to do an INSERT INTO from one db to the other and db, and be able to retrieve the unencrypted values on the destination DB, unless you DECRYPTBYKEY on the source DB, INSERT INTO using the clear values, and then ENCRYPTBYKEY on the destination. But if you setup the Symmetric keys on the two DBs using the same Key_Source and Identity_value the keys will be exactly the same and you can copy data from one DB to the other DB and use DECRYPTBYKEY to see the clear values. 
    Wednesday, March 4, 2015 2:45 PM

All replies

  • OK, got it. You can use the exact same DMK password, certificate name and symmetric key name on two different databases but the symmetric keys are different. You will not be able to do an INSERT INTO from one db to the other and db, and be able to retrieve the unencrypted values on the destination DB, unless you DECRYPTBYKEY on the source DB, INSERT INTO using the clear values, and then ENCRYPTBYKEY on the destination. But if you setup the Symmetric keys on the two DBs using the same Key_Source and Identity_value the keys will be exactly the same and you can copy data from one DB to the other DB and use DECRYPTBYKEY to see the clear values. 
    Wednesday, March 4, 2015 2:45 PM
  • Hi TheBrenda,

    Glad to hear that the issue is resolved. I mark your reply as answer, that way, other community members could benefit from your sharing.

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Thursday, March 5, 2015 7:53 AM
  • Yes Thanks for your Answer :-)

    JAYENDRAN


    Monday, July 10, 2017 10:01 AM
  • I know that this thread is old, but since the information has not changed since it has posted, can someone explain best practice on how to generate the key_source and identity_value to ensure that strong safeguards are in place and cannot be guessed?  Is this the same as generating a password, or should I reach into a C#/.NET method or library to generate these?

    Monday, March 30, 2020 3:45 PM