brady gaster

yadnb

Built-in SQL Server Password Hashing

Not sure how many of you know about this already, but I sure didn't. My nice Russian DBA told me this trick this morning. Apparently there's a built-in SQL Server function called pwdencrypt. It is the function SQL uses internally to encrypt SQL Server users. Anyway, here's a little SQL script you can run in Query Analyzer to see how it works.

select pwdencrypt('apassword')
select pwdcompare('apassword',pwdencrypt('apassword'))

Note - I do not condone the safety or security of this technique. I just found out about it today and thought it was nifty. Feel free to educate your peers in the comments of this post if, for some reason, you know this to be a bad idea.

Posted: Nov 26 2003, 02:00 PM by tatochip | with 6 comment(s)

Comments

M. Keith Warren said:

I have read that this was not necessarily a safe method.
# November 26, 2003 2:03 PM

James High said:

It's also not supported by microsft. They may change the hash algorithm with future service packs. Of course they will update the internal passwords but any data stored in a user table will not be upadated. In other words, use with caution.
# November 26, 2003 2:34 PM

Douglas Reilly said:

They are undocumented, unsupported, and have in the past changed with different versions (even SP's, I think) and broken folks applications that used them.
# November 26, 2003 2:35 PM

Brian Desmond said:

I posted about this a while ago, and was informed that I shouldn't use this thing because it's undocumented. Googling shows that they did change the hashing method between SQL7 an SQL2000.

For personal and small projects, it is definetely convenient though!
# November 26, 2003 3:40 PM

SpiderMan said:

I tried the following script:

select pwdcompare('A',pwdencrypt('a'))

In SQL Analyzer the result is 1, means it's not Case Sensitive. Why? I am running SQL Server 2k.
# November 26, 2003 11:38 PM

TrackBack said:

# December 1, 2003 10:34 AM
Leave a Comment

(required) 

(required) 

(optional)

(required)