none
Where to store/perform SHA1 Hash?

    Question

  • Currently i have a simple users table that stores the username, password, and salt.  I currently have my website create users by calling a stored procedure that creates the user and hashes their password and generates a salt for them.  My Question is the following.

    In order to obtain the most security where should the hashing function/algorithm be performed at? As stated above, i currently have it as a stored procedure but would like it to be elsewhere just in case the Database gets hacked, the hacker doesn't automatically have access to the stored procedure which contains the algorithm i use.  Thanks

    Saturday, February 09, 2013 7:11 AM

Answers

  • Hi

    If SP or UDF are created WITH ENCRYPTION then it is not possible to decryt it using sql server commands. There is no way to get the orginal source code once it is executed on server. Only privileged users who can acess system tables over DAC port or directly acess database files as well can attach a debugger to the server process can retrive the decrypted procedure from memory at runtime.

    Note : Sql server replication can not replicate encrypted sp or UDF. If   SP is encrypted Actual execution plan of the SP is not visible. Even it does not respond to that command and does not display the tab of execution plan.

    Refrence :  Actual Execution Plan vs. Estimated Execution Plan. of Encrypted SP

    Thanks

    Saurabh Sinha

    Sunday, February 10, 2013 9:33 AM
  • I downloaded an evaluation copy of Apex SQLEdit. In the database I was playing around in, there happened to be "encrypted" procedures. I could right-click and script without any problem. I got an extra question, that was all.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, February 10, 2013 11:26 AM

All replies

  • In the stored procedure you could use the CREATE PROCEDURE WITH ENCRYPTION option to ensure no one can read the stored procedure even if they have access to the database.

    Saturday, February 09, 2013 8:00 AM
  • WITH ENCRYPTION is only obfustication, and it's a trivial task for a knowledgeable person to retrieve the clear text.

    Then again, if you use a hash, having access to the algorithm does not really help to crack the passwordd, as a good hashing algorithm is irreversible.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 09, 2013 11:19 AM
  • WITH ENCRYPTION is only obfustication, and it's a trivial task for a knowledgeable person to retrieve the clear text.

    Then again, if you use a hash, having access to the algorithm does not really help to crack the passwordd, as a good hashing algorithm is irreversible.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    If they could view the stored procedure and see how you're apply the salt couldn't they just create a giant rainbow table of common password, apply the salt, and store the hashes.  Then all they need to do is scan table for hash matches to know the original password?
    Sunday, February 10, 2013 12:50 AM
  • Hi

    If SP or UDF are created WITH ENCRYPTION then it is not possible to decryt it using sql server commands. There is no way to get the orginal source code once it is executed on server. Only privileged users who can acess system tables over DAC port or directly acess database files as well can attach a debugger to the server process can retrive the decrypted procedure from memory at runtime.

    Note : Sql server replication can not replicate encrypted sp or UDF. If   SP is encrypted Actual execution plan of the SP is not visible. Even it does not respond to that command and does not display the tab of execution plan.

    Refrence :  Actual Execution Plan vs. Estimated Execution Plan. of Encrypted SP

    Thanks

    Saurabh Sinha

    Sunday, February 10, 2013 9:33 AM
  • I downloaded an evaluation copy of Apex SQLEdit. In the database I was playing around in, there happened to be "encrypted" procedures. I could right-click and script without any problem. I got an extra question, that was all.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, February 10, 2013 11:26 AM