
By: K. Brian Kelley  Read Comments (10)  Related Tips: More > Security 
I'm trying to use the HASHBYTES() function and I see there is an assortment of hashing algorithms available. What is the difference between each one?
HASHBYTES(), as the name implies, is a function or algorithm that generates a hash from some input. If you're not familiar with what a hash is, here's a good working definition for a hash function:
hash function  A hash function takes in data and returns back a fixed length block of bits such that any change to the data should result in a different block.
HASHBYTES() is actually a function which provides access to several hashing algorithms. SQL Server 2005 and up have the following protocols (how you specify them in HASHBYTES is in parentheses):
SQL Server 2012 introduces these additional hashing algorithms:
If you're looking for something just to quickly differentiate between two blocks of data, then it doesn't make a whole lot of difference.
If, however, you're dealing with hashes for passwords or anything related to security, then your choice can make a big difference.
Since a hash function will return a fixed length block, obviously, there are a finite set of possibilities for the output. Therefore, there's bound to be different inputs that will result in the same output. We call these situations collisions. What is of concern is if a collision can be engineered fairly quickly/cheaply. This is referred to as a collision attack. If a hashing algorithm is susceptible to this type of attack or other attacks with reasonable resources that either (a) allow you to create an identical hash with different input or (b) figure out the input from the hash, then those algorithms should be avoided. As of the writing of this tip, the following algorithms fall into that list:
There is one algorithm in this group, and that's SHA1 (SHA 1). While it is theoretically possible to generate collisions, in 2012 it's rather expensive to do so, as this Bruce Schneier post points out. In a few years, especially with cloud computing, it should be within the financial resources of organized crime to be able to break the algorithm. As a result, the US government had recommended that government agencies start phasing it out start back in 2010. However, it's still very much in use.
If you're not on SQL Server 2012, SHA1 is the best choice to use.
The SHA2 algorithm comes in several block sizes, of which SQL Server 2012 implements two:
Other than the block size, is there a real difference between the two? Yes, there is. The SHA512 implementation utilizes 80 rounds, whereas the SHA256 implementation only uses 64. A round is basically a repeated series of steps in the algorithm itself. Therefore, most attacks should have a harder time with SHA512. That being said, SHA256 is more widely implemented.
Monday, October 27, 2014  12:20:54 PM  arif  Read The Tip 
Thanks Brian.. Very straightforward and easy to understand

Wednesday, May 07, 2014  3:22:44 AM  Manikanth  Read The Tip 
I am using SHA2_256 in SQL Server 2012 LEN(HASHBYTES('SHA2_256','Samplestring')) when do i get 31 and when do i get 32 ? Hash1 ; 0xD2F3C3D588292A28938E9FC7AC6F86AA9A2FCA270CE958A8CB7620B11295D320 (Length 31) Hash2 : 0xA9A7529D790E43C103741F7EBE0FE3FC09F455A7E91AA92FD223D8B50E3D882B (Length 32)

Wednesday, October 30, 2013  12:31:24 PM  Key stretching  Read The Tip 
Most importantly, if you're using hashing for password hasing, you should use key stretching, as using just one hash iteration is far too fast  and more importantly, is very likely to be much faster on an attacker's hardware than it is on your own (see empirical example, below), even if the attacker is a bored teenager with a single gaming video card. PBKDF2 (also known as PKCS #5 v2.0 and RFC2898), BCRYPT, and SCRYPT are all examples of this, and sufficient numbers of itertaions need to be chosen. Per NIST SP800131A, http://csrc.nist.gov/publications/nistpubs/800131A/sp800131A.pdf SHA1 is Disallowed for digital signature generation after 2010, LegacyUse for digital signature verification after 2010, but remains Acceptable for nondigital signature use with no end date given. Therefore, if NIST special publications matter to you, you need to pay attention to what your use case is. Note that SHA512 uses 64bit words vs. the 32bit words fo SHA256, and thus at this time, an attacker computing SHA512 on graphics cards does not have as much of an advantage as computing SHA256 on said graphics cards. As an empircal example, oclHashcatlite with a single Radeon 7970 v0.15 computes about 1.1 billion (with a B) SHA256 tries per second, but only about 81 million (with an M) SHA512 tries per second. Note that the same system computes about 2.8 billion (with a B) SHA1 tries per second, and 8.2 billion (with a B) MD5 tries per second. As a contrast, oclHashcatplus v0.15 on similar hardware computes WPA2 at 133 thousand (with a T) tries per second. WPA2 is defined as PBKDF2(HMAC−SHA1, passphrase, ssid, 4096, 256). Note that multiple graphics cards can be put in a single machine for roughly linear speed increases, and multiple machines can likewise be used for linear speed increases. For password storage, see https://www.owasp.org/index.php/Password_Storage_Cheat_Sheet 
Tuesday, August 27, 2013  2:04:25 PM  mbourgon  Read The Tip 
If you're not worried about attacks, which ones have the overall lowest likelihood of collisions? I've recommended MD5 for a while because the collision chance is low  some of our devs use binary_checksum (No! Bad dev! No cookie!) to check to see if the values have changed, and MD5 seems to be a much better option.
Thanks! 
Monday, July 15, 2013  7:50:37 PM  TimothyAWiseman  Read The Tip 
As always, a great post. But since you mention storing passwords, it might be worth going a bit further. For instance, it can help to salt as well as hash the passwords, and as ArsTechnica pointed out even that is probably not enough against a determined security expert. Proper password storage is a difficult thing. 
Sunday, July 14, 2013  1:20:15 PM  Sweet!!!  Read The Tip 
Sweet!!! 
Friday, July 12, 2013  1:37:30 PM  Ed  sqlscripter  Read The Tip 
Great posting... 
Thursday, July 11, 2013  5:37:15 PM  D  Read The Tip 
nice one, thank you. 
Thursday, July 11, 2013  10:15:41 AM  CodePro  Read The Tip 
Excellent post, thanks. 
Thursday, July 11, 2013  9:09:28 AM  K. Brian Kelley  Read The Tip 
I missed a link reference. The definition is adapted from wikipedia's hash function definition. I've simplified it. If you want to see the original, it is here: http://en.wikipedia.org/wiki/Cryptographic_hash_function 