SQL Server PWDEncrypt value comparison

Question

I think what I am about to ask is impossible, however, figured it was worth a shot here.

We have an application that makes use of SQL Servers PWDEncrypt and PWDCompare functions.

Part of the system creates duplicates of users (same logon and password). Due to a bug in the system, instead of copying the binary stored PWDEncrypt of a password it performed another PWDEncrypt of the password. Therefore the binary values do not match.

Is it possible to find out if the two binary values are hashes of the same password?

e.g. PWDEncrypt('abc') = PWDEncrypt('abc')

If I can do this then it means I can find out how many users this bug has actually affected rather than having to deal with thousands!

EDIT: To clarify, PWDEncrypt('abc') = PWDEncrypt('abc') will NOT return true as the passwords are hashed to different values.

Whilst I know its impossible to obtain the password from the hash, PWDCOMPARE('abc', PWDENCRYPT('abc')) works, therefore, internally SQL Server must be doing more than just hashing the password you're comparing and check the values are the same.

Answer 1

You can just type SELECT CASE WHEN PWDEncrypt('abc') = PWDEncrypt('abc') THEN 1 ELSE 0 END into a query window and see the result.

Answer 2

It seems that Joel's statement is correct in SQL Server 2000 , but not in SQL Server 2005.

When you generate the hashes together in the same statement in 2000, they end up with the same salt (random seed number at the beginning) which makes them identical. In 2005 a different salt is always generated, so they never match

if you try this on SQL Server 2000:

PRINT PWDEncrypt('abc')
PRINT PWDEncrypt('abc')
PRINT PWDEncrypt('aaa')
PRINT PWDEncrypt('bbb')

you always have the same salt at the beginning of the hash, where as in 2005 its always different. Also notice that in SQL Server 2005, the hash is shorter as it no longer maintains a copy of the hash in uppercase for case in-sensitive password compatibility.

If you can generate the hash with the same salt, then you can compare them (which means trying a brute force or dictionary attack) Have a look at this article on how to do it. It shows you how to crack SQL Server password in C using the CryptCreateHash function.