SQLServerCentral.com / Discuss Content Posted by Steve Jones / Article Discussions by Author / Article Discussions / HASHBYTES / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 10:29:19 GMT20RE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspxHi Steve,Ok, ok, I know that.Regards.Thu, 11 Jun 2015 06:03:38 GMTJunior Galvão - MVPRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspxI don't understand the confusion. Hashbytes cannot add a salt. The programmer adds it, which is the answer to the question and what is shown in the link you posted.Wed, 10 Jun 2015 12:09:25 GMTSteve Jones - SSC EditorRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspxHi Steve,As I commented, referring to the Books Online cannot make use of salt on function Hashbytes instead, but there are ways to do it, even I posted the link.That was my confusion regarding your inquiry.Wed, 10 Jun 2015 12:00:07 GMTJunior Galvão - MVPRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]Junior Galvão - MVP (6/9/2015)[/b][hr]Hi Steve Jones,I appreciate your question, but the answers are a little confusing if we look at the Books Online and other blogs, like this: http://www.mssqltips.com/sqlservertip/3293/add-a-salt-with-the-sql-server-hashbytes-function/Regards.[/quote]What's confusing?Wed, 10 Jun 2015 11:52:55 GMTSteve Jones - SSC EditorRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspxI have to amend my answer!There is, in fact, a PBKDF2 implementation on SQL Server - even PBKDF2-HMAC-SHA-512 (modifiable for SHA1 if required):[url=https://stackoverflow.com/questions/7837547/is-there-a-sql-implementation-of-pbkdf2]https://stackoverflow.com/questions/7837547/is-there-a-sql-implementation-of-pbkdf2[/url]Both the SHA-512 and the SQL 2008 compliant SHA-1 version are at:[url=https://github.com/Anti-weakpasswords/PBKDF2-MSSQL-Custom-A]https://github.com/Anti-weakpasswords/PBKDF2-MSSQL-Custom-A[/url]These are still horrifically slow even compared to normal CPU based methods available in C, .NET, OpenSSL, John the Ripper, Hashcat, etc. much less the advanced GPGPU methods like [url=https://hashcat.net/oclhashcat/]oclHashcat[/url]Tue, 09 Jun 2015 14:30:43 GMTNadrekRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspxHi Steve Jones,I appreciate your question, but the answers are a little confusing if we look at the Books Online and other blogs, like this: http://www.mssqltips.com/sqlservertip/3293/add-a-salt-with-the-sql-server-hashbytes-function/Regards.Tue, 09 Jun 2015 14:19:18 GMTJunior Galvão - MVPRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]mohammed moinudheen (2/9/2012)[/b][hr]No idea about this really. I guessed it and got it wrong :-)[/quote]+1Fri, 11 Jan 2013 02:18:19 GMTDineshbabuRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspxWow!Tom, you have my standing invitation to the whisk[e]y bar in Bellevue, whenever you happen to be in this neck of the woods, but I will have to up that.I am on systems that are on the Intranet and trusted, so I do not have to worry about these things. So reading your post, I found my blind spot, probably one of many.Much thanks, again, and I am buying, even if your call is a 50 years old single malt.Sat, 11 Feb 2012 21:19:13 GMTRevenantRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]cengland0 (2/10/2012)[/b][hr]Generally companies require you to change your passwords every 30, 60, or 90 days so a brute force doesn't work in these cases either.[/quote]Two points here: The companies which do this so it to their own staff, not to their customers - so customer passwords have to be safe for a much longer time than the internal passwords to which these change frequencies apply. So something good enough to be safe for rather more than several years will be needed, unless the attitude to customer security changes.Besides, the pasword I use for my most secure stuff is a couple of hundred characters long and I'm not going to create and learn to remember a new one of those every 90 days. I lost my PGP keys way back when because I was persuaded to change my passphrase regularly - and of course couldn't even revoke them, so they are still on public servers somewhere, since only someone who knows them can revoke them.[quote]Sending the clear text from the application to the database is dangerous because the trace command can pick that up and be captured by anyone with full access to the database. Having it hashed in the application makes it so only the salt and hashedpassword values are sent to the database and they are no longer clear text at that point. You can look in the table or do whatever trace command you want and you will not be able to see the user's original password anywhere in the database.[/quote]One can perfectly well use encryption (preferably asymmetric, of course) between the database and the app, and do the hashing in the database - or one could if the database were capable of doing the hashing; for long term high security, SQL Server currently isn't.The idea that passwords have to be passed to the database in unencrypted form is just plain false, and is not a reason for not doing the hashing in the database. The real reason is the SQL Server doesn't have the required hash functions. SQLS 2012 will improve things a little with the addition of SHA2-256 and SHA2-512 to the hashbytes repertoire, but it's still missing the crucial thing: a seriously slow hash; and that means that it can't do the hashing needed for lasting high security.Of course banks and investment managers and so on providing customers with a web interface don't have a security model that delivers either high security or lasting security - they impose nonsense like a password must be alphanumeric (no symbols) and/or the alpha part of the password is case insensitive and/or the password must be between 6 and 12 characters, they rubbish the vulnerabilities in their security models that are demonstrated by competent security experts (look at how VISA and MasterCard treat the Cambridge University security research team), some of them even hold passwords in clear.Sat, 11 Feb 2012 16:00:55 GMTTomThomsonRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]Nadrek (2/10/2012)[/b][hr]I would say hashing should be done on the application side at this point, because A) SQL Server does not provide us modern hash algorithms (i.e. the SHA-2 family; SHA-512, SHA-256, SHA-384, Whirlpool, the SHA-3 finalists, etc.)[/quote]One note here: SQL Server 2012 will add SHA2Fri, 10 Feb 2012 11:51:08 GMTSteve Jones - SSC EditorRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote]Seems the database would have two columns. One for Salt and one for HashedPassword. Then, you have your application do the hashing with the RANDOM salt. You store both in the database.[/quote]Usually we can use the user ID column so that SALT will be unique for each user though more than one have same password.[quote]Reverse engineering a HashedPassword is difficult unless you already have several commonly used password that are in a table and you compare it with the HashedPassword column.[/quote]Hashed password can never be reverse engineered (as in the case of encryption). The only possible way is bruteforce attack with the help of rainbow table. Ofcourse, there are other ways like capturing key stroke.Again this can further be controlled by limiting incorrect password attempts combined with strong hash algorithm.Fri, 10 Feb 2012 10:59:20 GMTSathishKRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]Raghavendra Mudugal (2/10/2012)[/b][hr]how do you suggest to plan this? first to "get" you need to "set", and how are you going to get by bypassing the trace?(your comments has created a self loop here)[/quote]Seems the database would have two columns. One for Salt and one for HashedPassword. Then, you have your application do the hashing with the RANDOM salt. You store both in the database.Reverse engineering a HashedPassword is difficult unless you already have several commonly used password that are in a table and you compare it with the HashedPassword column. This will then give you a list of users that contain the same hash values as your precompiled table. This does not mean the user had the same password but does mean your password will hash to the same value and let you sign in. One hashed value can usually be achieved by many different clear-text passwords.So, to prevent these "dictionary attacks," use a salt value which changes the final hash value for the stored password. Processing these through a dictionary table is useless because all users have a different salt value. You would have to have a pre-compiled dictionary table for each user in the database which gets much more difficult to process. The chances of you getting a match this way is statistically 0% unless you have several years to brute force it. Generally companies require you to change your passwords every 30, 60, or 90 days so a brute force doesn't work in these cases either.Sending the clear text from the application to the database is dangerous because the trace command can pick that up and be captured by anyone with full access to the database. Having it hashed in the application makes it so only the salt and hashedpassword values are sent to the database and they are no longer clear text at that point. You can look in the table or do whatever trace command you want and you will not be able to see the user's original password anywhere in the database.The only method for acquiring these passwords is if you use a network sniffer that captures the packets from the client's computer to the web server. And, this is only easy if you're not using an https server and still on a simple unencrypted http server.Fri, 10 Feb 2012 09:43:23 GMTcengland0RE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]SathishK (2/10/2012)[/b][hr][quote][b]Raghavendra Mudugal (2/10/2012)[/b][hr][quote][b]cengland0 (2/10/2012)[/b][hr][quote][b]Raghavendra Mudugal (2/10/2012)[/b][hr][quote][b]cengland0 (2/10/2012)[/b][hr][quote][b]Steve Jones - SSC Editor (2/9/2012)[/b][hr][code="sql"]SELECT HASHBYTES( 'SHA1', 'R@nd0m' + Pwd + firstname) FROM Employees[/code]In this case, without access to the code, it becomes hard to determine what the input values for the hash function are.[/quote]Someone mentioned in another article that all you need to do is run a trace and you can get all this information. So, should the hashing be done on the application side to prevent this type of password retrieval with trace on?[/quote]just a thought - if that SP is encrypted then finding that in the TRACE is not possible (not sure if secured info are generally passed in the plane SQL statements.[/quote]So you're saying to put the query into an encrypted stored procedure to avoid the trace from getting the passwords? How do you pass parameters to that stored procedure? Wouldn't you do something like:EXEC sp_somestoreprocedure('salt','password')Couldn't that command be captured by the trace?[/quote]I thought SALT is fixed static string in the encrypted SP... you dont need to expose the SALT string as open parameter value, that needs to be kept safe. and on the password use the same concept what you are using now... SALT'ing is just added feature to HASH'ing. Let DB things remain on the DB side, if you want use something more on UI level, you can use any encryption concept besides HASH'ing.[/quote] Simply get the hashed(and salted) password from DB to the application. .[/quote]how do you suggest to plan this? first to "get" you need to "set", and how are you going to get by bypassing the trace?(your comments has created a self loop here)Fri, 10 Feb 2012 09:24:42 GMTRaghavendra MudugalRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]Raghavendra Mudugal (2/10/2012)[/b][hr][quote][b]cengland0 (2/10/2012)[/b][hr][quote][b]Raghavendra Mudugal (2/10/2012)[/b][hr][quote][b]cengland0 (2/10/2012)[/b][hr][quote][b]Steve Jones - SSC Editor (2/9/2012)[/b][hr][code="sql"]SELECT HASHBYTES( 'SHA1', 'R@nd0m' + Pwd + firstname) FROM Employees[/code]In this case, without access to the code, it becomes hard to determine what the input values for the hash function are.[/quote]Someone mentioned in another article that all you need to do is run a trace and you can get all this information. So, should the hashing be done on the application side to prevent this type of password retrieval with trace on?[/quote]just a thought - if that SP is encrypted then finding that in the TRACE is not possible (not sure if secured info are generally passed in the plane SQL statements.[/quote]So you're saying to put the query into an encrypted stored procedure to avoid the trace from getting the passwords? How do you pass parameters to that stored procedure? Wouldn't you do something like:EXEC sp_somestoreprocedure('salt','password')Couldn't that command be captured by the trace?[/quote]I thought SALT is fixed static string in the encrypted SP... you dont need to expose the SALT string as open parameter value, that needs to be kept safe. and on the password use the same concept what you are using now... SALT'ing is just added feature to HASH'ing. Let DB things remain on the DB side, if you want use something more on UI level, you can use any encryption concept besides HASH'ing.[/quote]First no need to send the SALT string and the password altogether to the database. Simply get the hashed(and salted) password from DB to the application. This can then be compared by hashing (and salting) the user entered password from UI for further validation in the application end. Hence no need to worry about whether the password/salt will be caught in trace or in transportation layer. This works when the SALT is a static string or user name.Ofcourse, there are more ways to implement it, but I am saying one of them.Fri, 10 Feb 2012 08:43:20 GMTSathishKRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]cengland0 (2/10/2012)[/b][hr]Someone mentioned in another article that all you need to do is run a trace and you can get all this information. So, should the hashing be done on the application side to prevent this type of password retrieval with trace on?[/quote]In general, you simply have to choose how hard you want which potential adversaries to work using which means. I would say hashing should be done on the application side at this point, because A) SQL Server does not provide us modern hash algorithms (i.e. the SHA-2 family; SHA-512, SHA-256, SHA-384, Whirlpool, the SHA-3 finalists, etc.) B) SQL Server does not provide us with an implementation of PBKDF2; even the SHA-1 only version the .NET framework gives us C) Conditional: When a single SQL Server instance is serving multiple client machines, loading SQL Server up with hundreds of thousands (or more) of hash iterations in order to allow a reasonable measure of security for users who use passphrases/passwords with a smaller total keyspace may end up putting more load on the CPU of the SQL box than is desirable C1) Do you really want to try to use GPGPU computation on a SQL Server instance from inside SQL?[quote][b]Raghavendra Mudugal (2/10/2012)[/b][hr]I thought SALT is fixed static string in the encrypted SP... you dont need to expose the SALT string as open parameter value, that needs to be kept safe. and on the password use the same concept what you are using now... SALT'ing is just added feature to HASH'ing. Let DB things remain on the DB side, if you want use something more on UI level, you can use any encryption concept besides HASH'ing.[/quote]Adding a salt in and of itself is merely a device to 1) prevent the trivial identification of the same cleartext (i.e. hash A5BC is present in rows 5, 15, 33, and 114; they have the same cleartext value!) and 2) render more difficult precomputed dictionaries.Using a fixed static string completely precludes reason 1) as the same cleartext + the same salt = the same hash, and nearly completely undermines reason 2), since once an attacker figures out the fixed single salt, they can proceed with precomputing a massive dictionary, and the result takes up the same space (and takes the same amount of time) as it would have without any salt at all. Salts need to be significantly long and completely random; feel free to add _more_ content, but the useful core of it is a set of random bytes of significant length.Fri, 10 Feb 2012 08:08:48 GMTNadrekRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]cengland0 (2/10/2012)[/b][hr][quote][b]Raghavendra Mudugal (2/10/2012)[/b][hr][quote][b]cengland0 (2/10/2012)[/b][hr][quote][b]Steve Jones - SSC Editor (2/9/2012)[/b][hr][code="sql"]SELECT HASHBYTES( 'SHA1', 'R@nd0m' + Pwd + firstname) FROM Employees[/code]In this case, without access to the code, it becomes hard to determine what the input values for the hash function are.[/quote]Someone mentioned in another article that all you need to do is run a trace and you can get all this information. So, should the hashing be done on the application side to prevent this type of password retrieval with trace on?[/quote]just a thought - if that SP is encrypted then finding that in the TRACE is not possible (not sure if secured info are generally passed in the plane SQL statements.[/quote]So you're saying to put the query into an encrypted stored procedure to avoid the trace from getting the passwords? How do you pass parameters to that stored procedure? Wouldn't you do something like:EXEC sp_somestoreprocedure('salt','password')Couldn't that command be captured by the trace?[/quote]I thought SALT is fixed static string in the encrypted SP... you dont need to expose the SALT string as open parameter value, that needs to be kept safe. and on the password use the same concept what you are using now... SALT'ing is just added feature to HASH'ing. Let DB things remain on the DB side, if you want use something more on UI level, you can use any encryption concept besides HASH'ing.Fri, 10 Feb 2012 04:51:01 GMTRaghavendra MudugalRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]Raghavendra Mudugal (2/10/2012)[/b][hr][quote][b]cengland0 (2/10/2012)[/b][hr][quote][b]Steve Jones - SSC Editor (2/9/2012)[/b][hr][code="sql"]SELECT HASHBYTES( 'SHA1', 'R@nd0m' + Pwd + firstname) FROM Employees[/code]In this case, without access to the code, it becomes hard to determine what the input values for the hash function are.[/quote]Someone mentioned in another article that all you need to do is run a trace and you can get all this information. So, should the hashing be done on the application side to prevent this type of password retrieval with trace on?[/quote]just a thought - if that SP is encrypted then finding that in the TRACE is not possible (not sure if secured info are generally passed in the plane SQL statements.[/quote]So you're saying to put the query into an encrypted stored procedure to avoid the trace from getting the passwords? How do you pass parameters to that stored procedure? Wouldn't you do something like:EXEC sp_somestoreprocedure('salt','password')Couldn't that command be captured by the trace?Fri, 10 Feb 2012 04:03:09 GMTcengland0RE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]cengland0 (2/10/2012)[/b][hr][quote][b]Steve Jones - SSC Editor (2/9/2012)[/b][hr][code="sql"]SELECT HASHBYTES( 'SHA1', 'R@nd0m' + Pwd + firstname) FROM Employees[/code]In this case, without access to the code, it becomes hard to determine what the input values for the hash function are.[/quote]Someone mentioned in another article that all you need to do is run a trace and you can get all this information. So, should the hashing be done on the application side to prevent this type of password retrieval with trace on?[/quote]just a thought - if that SP is encrypted then finding that in the TRACE is not possible (not sure if secured info are generally passed in the plane SQL statements.Fri, 10 Feb 2012 03:44:58 GMTRaghavendra MudugalRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]Steve Jones - SSC Editor (2/9/2012)[/b][hr][code="sql"]SELECT HASHBYTES( 'SHA1', 'R@nd0m' + Pwd + firstname) FROM Employees[/code]In this case, without access to the code, it becomes hard to determine what the input values for the hash function are.[/quote]Someone mentioned in another article that all you need to do is run a trace and you can get all this information. So, should the hashing be done on the application side to prevent this type of password retrieval with trace on?Fri, 10 Feb 2012 03:32:03 GMTcengland0RE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]Steve Jones - SSC Editor (2/9/2012)[/b][hr]Your code does return the same values, but I'm not sure pre-pending or appending is the issue. I could be wrong, but it seems that length is more important.If I do:[code="sql"]declare @t nvarchar(200)select @t = N'This is my string'select Hashbytes('SHA1', @t)UNION ALLSELECT Hashbytes('SHA1', @T + N'R@nd0mS!a6lTValue')UNION ALLSELECT Hashbytes('SHA1', @T + N'R@nd0mS!a6lTValue2342343')UNION ALLSELECT Hashbytes('SHA1', @T + N'R@nd0mS!a6lTValuefvsddgdfgfdgdf')[/code]Adding different values, I get different results. On my machine:[code="plain"]0xB9A02E529093456D139C69FC5E5D4D825B7EC24B0xCDE457DD8AB6C020E9852FE5B6953E02631A2CB20x6872C2C174FD33931D702F321C427D355B28016E0x208DBF4BE2F339ED5861258F7854F4A6EAAFBE23[/code]The idea here would be to use this in your table. So if I have salaries:[code="plain"]CREATE TABLE employees( firstname VARCHAR(50), pwd VARCHAR(200))GOINSERT dbo.Employees VALUES ('Steve', 'Easy') , ('Bob', 'H@rder') , ('Andy', 'VeryH$ardP2ssword') [/code]I could hash these as [code="sql"]SELECT HASHBYTES( 'SHA1', Pwd) FROM Employees [/code]However the results will always be the same, and more important, I could do a copy of the value from Steve's row to Andy's row and then log in as Andy. However I can salt these to make this type of attack less of an issue:[code="sql"]SELECT HASHBYTES( 'SHA1', Pwd + firstname) FROM Employees[/code]That's simple, and potentially an attacker can still go through all columns in the table, appending and prepending values, but I can make it harder with something like:[code="sql"]SELECT HASHBYTES( 'SHA1', 'R@nd0m' + Pwd + firstname) FROM Employees[/code]In this case, without access to the code, it becomes hard to determine what the input values for the hash function are.[/quote]Steve,first of all thanks again for your QotD and for taking the time to dig deeper into this matter.From what I know, when hashing a string using SHA1 with HASHBYTES, the result is basically a (hex) value of 20 digits (or bytes; see code and results below). Whatever the string to hash consists of, a different result will be calculated (but for a given string it will always be the same).In your examples you showed several ways, of how to pad a password with additional strings (pre- and/or appending them) to create a more complex (and longer) string to be hashed, thus making it harder to guess (or brute force) the actual password (generally speaking; I'm not an encryption pro and hence cannot discuss the advantages or shortcomings of the various hashing algorithms).[code="sql"]select hashbytes('SHA1', 'abc'), datalength(hashbytes('sha1', 'abc'))union allselect hashbytes('SHA1', 'cba'), datalength(hashbytes('sha1', 'cba'))union allselect hashbytes('SHA1', 'abcdef#gh76ij"$%%klmnopq@€rstuvwxyz'), datalength(hashbytes('sha1', 'abcdef#gh76ij"$%%klmnopq@€rstuvwxyz'));[/code][code="plain"]0xA9993E364706816ABA3E25717850C26C9CD0D89D 200xD9F0509FB7E8BD7D4C4B627DFEC70C0C0E01FB34 200x90F9C7AC24422A58AF65970D95959F661DFF746B 20[/code]Anyway, by changing the word [i]parameter[/i] to [i]value[/i] in the QotD, my only concern regarding wording was remedied.As always, this was an excellent question and the following discussion was even better.Kind regards,MichaelFri, 10 Feb 2012 02:02:34 GMTmichael.kaufmannRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]L' Eomot Inversé (2/9/2012)[/b][hr]I would be unhappy with 64 bits; basicly I think of the salt length as being comparable to key length for symmetric excryption, and for symmetric encryption I choose random salts of at least the key length. For a high security system I would be distinctly unhappy with a salt shorter than 128 bits, just as I would be distinctly unhappy with a symmetric key shorter that 128 bits. In fact I would go for a salt length the same as the hash output length (160 bits for SHA1, if - unlikely for high security and long duration - I thought SHA1 adequate).[quote]2) Run a _lot_ of iterations using a standard iteration algorithm (not available in SQL Server). See my post for some details on the complexity/length vs. iteration tradeoff[/quote]Not too sure about standard iteration algorithms;...[/quote]I almost always agree with longer is better (assuming equal randomness); 64 bits of random salt (nonce) probably puts it in the "a couple of decades" protection level category, which is good for some things, and poor for others. SHA-1 is not recommended anymore - it's considered to have collision complexity on the order of 2^52 (52 bits), per [url=http://eurocrypt2009rump.cr.yp.to/837a0a8086fa6ca714249409ddfae43d.pdf]this Eurocrypt 2009 presentation, by Cameron McDonald, Philip Hawkes and Josef Pieprzyk[/url], and no more than 80 bits of "security", per the [url=http://csrc.nist.gov/publications/nistpubs/800-131A/sp800-131A.pdf]U.S. NIST SP800-131A[/url].Standard iteration algorithm: PBKDF2 (Password-Based Key Derivation Function, PKCS #5 2.0) is a specific algorithm for having multiple iterating hashes for generating a password; see my post in the [url=http://www.sqlservercentral.com/Forums/Topic1243249-263-1.aspx]Should we outsource identity management[/url] thread for more detail and some links to specifications and implementations.Thu, 09 Feb 2012 12:57:26 GMTNadrekRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspxNice question, had no idea.Thu, 09 Feb 2012 11:36:09 GMTKen WymoreRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]Nadrek (2/9/2012)[/b][hr1) Salt with a long salt that, for each row, is independently created by a cryptographic random number generator (I recommend at least 64 bits) and stored cleartext.[/quote]I would be unhappy with 64 bits; basicly I think of the salt length as being comparable to key length for symmetric excryption, and for symmetric encryption I choose random salts of at least the key length. For a high security system I would be distinctly unhappy with a salt shorter than 128 bits, just as I would be distinctly unhappy with a symmetric key shorter that 128 bits. In fact I would go for a salt length the same as the hash output length (160 bits for SHA1, if - unlikely for high security and long duration - I thought SHA1 adequate).[quote]2) Run a _lot_ of iterations using a standard iteration algorithm (not available in SQL Server). See my post for some details on the complexity/length vs. iteration tradeoff[/quote]Not too sure about standard iteration algorithms; but I'm a lot more than a decade behind the state of the art, so maybe there are some good standard iteration algorithms that I don't know about; anyway, by whatever method one has to achieve an adequately slow hash. Assuming that a required slowness is stored along with the salt, that the hash has a required slowness parameter, and that the login mechanism can have write access to the store, slowness can be increased without user visibility at next login, and that is highly desirable.Thu, 09 Feb 2012 11:30:22 GMTTomThomsonRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]Steve Jones - SSC Editor (2/9/2012)[/b][hr]The idea here would be to use this in your table. So if I have salaries:[code="plain"]CREATE TABLE employees( firstname VARCHAR(50), pwd VARCHAR(200))GOINSERT dbo.Employees VALUES ('Steve', 'Easy') , ('Bob', 'H@rder') , ('Andy', 'VeryH$ardP2ssword') [/code]I could hash these as [code="sql"]SELECT HASHBYTES( 'SHA1', Pwd) FROM Employees [/code]However the results will always be the same, and more important, I could do a copy of the value from Steve's row to Andy's row and then log in as Andy. However I can salt these to make this type of attack less of an issue:[code="sql"]SELECT HASHBYTES( 'SHA1', Pwd + firstname) FROM Employees[/code]That's simple, and potentially an attacker can still go through all columns in the table, appending and prepending values, but I can make it harder with something like:[code="sql"]SELECT HASHBYTES( 'SHA1', 'R@nd0m' + Pwd + firstname) FROM Employees[/code]In this case, without access to the code, it becomes hard to determine what the input values for the hash function are.[/quote]Note that while it's harder, this example is of a low cardinality salt, if I take my 10 million word password dictionary, and multiply it by my 10,000 word firstname list, I end up with a 10 billion word dictionary. Were I to precalculate it, it would take up on the order of 2TB - we'll call it a single 2TB hard drive; pretty cheap. To compute them, I can use modern software on a set of GPU's in a single machine, and run through all 100 billion possibilities in less than 10 seconds (plus the amount of time to write them to the disk).Better is a fully random salt of large enough length (64+ random bits), so as to make these blind precalculation attacks impractical; instead, each attack will need to apply salt+hash for every word in the dictionary separately; still at over 15 billion tries per second, but at least it has to be retried on each row.Thu, 09 Feb 2012 11:17:49 GMTNadrekRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspxDarn, talked myself of the correct answer.Thu, 09 Feb 2012 11:05:56 GMTBritt CluffRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]michael.kaufmann (2/9/2012)[/b][hr]I'd second this; it's my understanding that concatenating a fixed string as [i]salt[/i] (in Steve's example assigned to a variable) to another string can't be considered a [i]salt parameter[/i], which should be a random value (for increased security). The following query will return the exact same results as Steve's proposed solution in the 'Correct Answer' section of this QotD :cool::...I'd say, no matter how many string parts are concatenated, the combined string qualifies as [b]{ @input | 'input' }[/b] following the HASHBYTES syntax.[/quote]Your code does return the same values, but I'm not sure pre-pending or appending is the issue. I could be wrong, but it seems that length is more important.If I do:[code="sql"]declare @t nvarchar(200)select @t = N'This is my string'select Hashbytes('SHA1', @t)UNION ALLSELECT Hashbytes('SHA1', @T + N'R@nd0mS!a6lTValue')UNION ALLSELECT Hashbytes('SHA1', @T + N'R@nd0mS!a6lTValue2342343')UNION ALLSELECT Hashbytes('SHA1', @T + N'R@nd0mS!a6lTValuefvsddgdfgfdgdf')[/code]Adding different values, I get different results. On my machine:[code="plain"]0xB9A02E529093456D139C69FC5E5D4D825B7EC24B0xCDE457DD8AB6C020E9852FE5B6953E02631A2CB20x6872C2C174FD33931D702F321C427D355B28016E0x208DBF4BE2F339ED5861258F7854F4A6EAAFBE23[/code]The idea here would be to use this in your table. So if I have salaries:[code="plain"]CREATE TABLE employees( firstname VARCHAR(50), pwd VARCHAR(200))GOINSERT dbo.Employees VALUES ('Steve', 'Easy') , ('Bob', 'H@rder') , ('Andy', 'VeryH$ardP2ssword') [/code]I could hash these as [code="sql"]SELECT HASHBYTES( 'SHA1', Pwd) FROM Employees [/code]However the results will always be the same, and more important, I could do a copy of the value from Steve's row to Andy's row and then log in as Andy. However I can salt these to make this type of attack less of an issue:[code="sql"]SELECT HASHBYTES( 'SHA1', Pwd + firstname) FROM Employees[/code]That's simple, and potentially an attacker can still go through all columns in the table, appending and prepending values, but I can make it harder with something like:[code="sql"]SELECT HASHBYTES( 'SHA1', 'R@nd0m' + Pwd + firstname) FROM Employees[/code]In this case, without access to the code, it becomes hard to determine what the input values for the hash function are.Thu, 09 Feb 2012 11:05:14 GMTSteve Jones - SSC EditorRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]Daniel Bowlin (2/9/2012)[/b][hr]Interesting question, and an interesting bit of reading. However[quote]That quote comes from a section in whch the topic is encryption and encrypted data; SQL Server encryption always uses a random salt.[/quote]How can SQL Server use a random salt and still have reproduceable results? Am I getting some concepts confused here?[/quote]If you stick some random bits onto the front of something that's being encrypted using a symmetric cipher whose key schedule is impacted at each point in the encryption by the story so far (eg by cipher block chaining) you (a) render dictionary attacks ineffective and (b) ensure that someone who can see rows with an encrypted field can't discover, without doing the decryption, whether two rows both have the same value in that field; but the only effect on decryption is that when you have decrypted the field there are some extra bits on the front that need to be thrown away. The salt can of course be random length, so that the same value encrypted twice may have two different lengths, which really gives attackers trouble; doing this of course requires the length of the salt to be encoded in the salt, so that decryption knows how much of the decrypted string to throw away. I have no idea whether SQL Server uses varying length salts or a fixed length (and if I had I wouldn't be telling).Of course while screwing up attackers one also screws up performance; there's lots of tradeoffs to be made and serious design to be done if you want really good security at the same time as really good performance.Thu, 09 Feb 2012 11:00:14 GMTTomThomsonRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]Raghavendra Mudugal (2/9/2012)[/b][hr]For me this question did not made any sense.the sample code is just concatenating another variable to it, you can name it @salt to @sugar... still the sample code will not make sense to me....My only concern is - question and it's answer does not really suites. I dont think SALT is tech word here in SQL, so it does not paints proper picture.[/quote]A salt is an addition to the hash function to change the outcome, not a parameter. I have altered the question to say "salt value" instead of parameter, which may have been too tricky.Salt is a concept, not a specific value. The variable name was chosen not to imply @salt is needed, but to mark the idea.Thu, 09 Feb 2012 10:55:43 GMTSteve Jones - SSC EditorRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]AlexSQLForums (2/9/2012)[/b][hr]:( never added salt before.[/quote]Adding a salt in and of itself is merely a device to 1) prevent the trivial identification of the same cleartext (i.e. hash A5BC is present in rows 5, 15, 33, and 114; they have the same cleartext value!) and 2) render more difficult precomputed dictionaries. While these are laudable goals, the second requires significantly long salts (a 1 character hex salt makes a 10 million word dictionary precomputation take as long as a 2.5 billion word dictionary... which _still_ takes less than a second and almost certainly fits in RAM on a modern consumer machine, for a single MD5/SHA1 round each).If you're using this for passwords, see my post in [url=http://www.sqlservercentral.com/Forums/Topic1243249-263-1.aspx]Should we outsource identity management[/url] for now; but the summary is: 0) Require difficult to guess cleartexts, particularly for password fields1) Salt with a long salt that, for each row, is independently created by a cryptographic random number generator (I recommend at least 64 bits) and stored cleartext.2) Run a _lot_ of iterations using a standard iteration algorithm (not available in SQL Server). See my post for some details on the complexity/length vs. iteration tradeoffThu, 09 Feb 2012 10:36:46 GMTNadrekRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]Rich Weissler (2/9/2012)[/b][hr][quote][b]GPO (2/8/2012)[/b][hr]The SALE string? This confused me! :-P[/quote]Yeah, I decided SALE had to be a typo for SALT. (If that isn't want happened, someone please yell... I'm still assuming.)[/quote]Correct, the typo has been corrected.Thu, 09 Feb 2012 10:28:03 GMTSteve Jones - SSC EditorRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx:( never added salt before.Thu, 09 Feb 2012 10:13:42 GMTAlexSQLForumsRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[hr][quote]Add the sale string[/quote]Good Question, but "Add the [b]sale[/b] string" seems to be tricky... :-)Thu, 09 Feb 2012 09:50:19 GMTSathishKRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspxGood question - thanks, Steve!Thu, 09 Feb 2012 09:40:49 GMTRevenantRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspxthanks for the question. another good lesson for me today!Thu, 09 Feb 2012 09:19:52 GMTOzYbOi d(-_-)bRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspx[quote][b]L' Eomot Inversé (2/9/2012)[/b][hr]...Hashbytes is a hashing function, not an encryption function...[/quote]I would say the question as intended is a useful learning tool, but it was written poorly. HASHBYTES does not, and cannot, perform encryption, nor can it decrypt. I see interview candidates conflate encrypting with hashing all the time; they aren't the same. Hashing is mathematically easy to do in one direction (cleartext to hash), and mathematically difficult to do in the other direction (hash to cleartext), theoretically to the point that the only way to get the cleartext from the hash is to guess the cleartext and see if you're right*.The SALE vs. SALT typo was also a minor issue, but that's a normal typo.*Guessing MD5 or SHA1 can happen, on a single computer, at speeds measured in tens of billions of guesses per second, applied against dictionaries combined with rules (i.e. every word in the dictionary doubled, every word with different case settings, every word in several dialects of 1337 speak, every word with a suffix of every number from 0 to 1000, etc. etc. etc. still doesn't take very long).Thu, 09 Feb 2012 08:48:57 GMTNadrekRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspxthanks for the question.Thu, 09 Feb 2012 08:28:19 GMTSQLRNNRRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspxNot too many people did. Thats why a lot of people answered the question wrong.Thu, 09 Feb 2012 08:23:14 GMTmrwillcostelloRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspxWhat is a SALE string? I don't understand that terminology.Thu, 09 Feb 2012 08:12:05 GMTLee NadelmanRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspxThe SALE definitely confused me as well. I couldn't force myself to believe that it would be a typo. It goes to show we are all human. I should have went with my gut instinct and chose that answer anyway.Thu, 09 Feb 2012 08:09:42 GMTmrwillcostelloRE: HASHBYTEShttp://www.sqlservercentral.com/Forums/Topic1249456-32-1.aspxThe question could have been worded a bit better but a good question none the less and an interesting discussion as always.Thu, 09 Feb 2012 08:02:52 GMTCliff Jones