umailedit (3/16/2013)
"SHA2 is recommended. Unfortunately you would have to upgrade to SQL Server 2012 in order to use this algorithm. "You should never use hashing algorithms to store password. If sql is indeed using sha1 and sha2 then it is using weak algorithms for password storage. Hashing algorithms are designed to be quick for digesting large blocks of text. To encrypt passwords we need a SLOW algorithm so that it is difficult to try it that many times. Something like twofish or blowfish encrypting the password with itself (for one way).
Beware of conflating the core one-way algorithm with the overall algorithm. Choosing a good, solid core one-way (hash) algorithm for authentication is necessary but not sufficient. Fast vs Slow is relevant only as seen through the attacker's eyes with allowances for expected future capability increases.
SHA-1 by itself is no longer really appropriate for use, agreed, and that's essentially what SQL Server 2000 uses, and in a slight reduction in idiocy, what 2005, 2008, and 2008 R2 use. More technically, those use SHA-1(UCS-2(password with salt)), where UCS-2 is the ex-standard SQL Server still uses when it says 'Unicode' (despite it being removed from the actual Unicode standard in the late 90's). SQL Server 2012 uses SHA-512(UCS-2(password with salt)), which removes the attacks that are targetting SHA-1, but does little else of value - as umailedit correctly noted, one SHA-512 round is very fast... just like one bcrypt round, or one scrypt round, or one PBKDF2 round.
What we all should be doing is much like the graphic - pick a standard, well-known variable round algorithm like PBKDF2 (also known as RFC2898 and PKCS #5), scrypt, or bcrypt, and ideally for each password stored, also store how many rounds you used along with the salt, where the number varies. A minimum number of rounds should be selected based on possible adversary, and if your hardware can't keep up with that, buy new hardware. For PBKDF2 in particular, the recommendation long ago was to double the number of rounds every 2 years to keep up with anticipated increases in computing power, starting at 1000 rounds in 2000. Ideally, in 2013, we should be using 90,510 PBKDF2 iterations.
If it helps, WPA and WPA2 Wifi encryption uses a key generated by PBKDF2(HMAC-SHA1, passphrase, ssid, 4096, 256) (note the use of a non-random, potentially short salt, which is a serious flaw). They're using 4,096 rounds of PBKDF2 in low-end consumer devices, starting back in 2004 - almost a decade ago, now!
Note that all of this is 100% worthless in the face of users who select passwords like "password", which "complexity rules" correctly show to be a pathetically weak 8 character password, or "P@$$w0rd123", which "complexity rules" incorrectly show to be a strong 11 character password - it is, in fact, nothing of the sort when facing rules-based dictionary attacks, being simply "password" with the following rules: Proper case, 1337 speak, append 123. Alternately, users will choose, say, "Robert2010" - again, it'll pass "complexity" rules, but it's a very common password for anyone whose son Bob is turning 3 this year, or whose husband Rob is going to forget their three year anniversary this year. That's a simple proper case, append recent year rule for a dictionary attack based on common first names.
Note that in my post in http://www.sqlservercentral.com/Forums/Topic1243249-263-1.aspx#bm1243781, I've provided a stored procedure to help find passwords that some of the most common starting rules based dictionary attacks will find.
Reference: https://www.owasp.org/index.php/Password_Storage_Cheat_Sheet