locked
MS SQL encryption function output differently each time RRS feed

  • Question

  • Hi all,

    We are using MS SQL 2016 Standard Edition SP1.

    And when we used the encryption function, "EncryptByKey", the end result of the encrypted value seems to be different even though we are encrypting the same text. E.g. encrypting "ABCD0123" first time output "0x0010FF740752C44785EE835649E31F060100000020AE31EAA6A354B95C7BE3991FF3924957A1301E3DA460A96A52B7614A69CA5CE56A21865BBDAD0280CD84AE052770A5"

    and when we run the same encryption second time for the same "ABCD0123" input, we get the another output:

    "0x0010FF740752C44785EE835649E31F06010000003EC461FA076B3DAF0A8AC37BB442848DBFEBC0CAA8BF3F09F8B53E7D00C695497314863BBFD4A6F946BB25AB0E8DE6D3"

    Is it normal for the MS SQL encryption function to produce different output each time it run?

    Please help me understand how we can resolve such scenario. Or rather what is the best practise for such scenario if we want to get the same output for the encrypted value?

    Thanks a lot in advance.

    Best regards,

    Alex.

    Friday, December 28, 2018 9:12 AM

All replies

  • Hi Alex,

    >>Is it normal for the MS SQL encryption function to produce different output each time it run?
    Yes, it is by design. To prevent discovery of plain text content by comparing encrypted values (the second attack), most encryption algorithms include a salt value. In SQL Server, a random salt value is always applied to the encryption. It is important for security and it will result in different ciphertexts for the same message.

    >>Or rather what is the best practise for such scenario if we want to get the same output for the encrypted value?
    You can’t do it. It is unsafe. 

    Best Regards
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, December 31, 2018 5:27 AM