CODEWITHSUNDEEP
.netsqlvb.netcryptographysha1
carlowahlstedt
carlowahlstedt

Reputation: 158

.Net SHA1CryptoServiceProvider not matching SQL Hashbytes

After converting some plaintext passwords stored in SQL using Hashbytes can't seem to get .Net to generate a proper matching hash.

SQL used to convert the passwords:

UPDATE Users
SET UserPassword = HASHBYTES('SHA1', UserPassword + CAST(Salt AS VARCHAR(36)))

Now the .Net code used to generate the hash:

Dim oSHA1 As New System.Security.Cryptography.SHA1CryptoServiceProvider
Dim bValue() As Byte
Dim bHash() As Byte

bValue = System.Text.Encoding.UTF8.GetBytes(sPlainTextPass)
bHash = oSHA1.ComputeHash(bValue)
oSHA1.Clear()

Dim sEncryptPass As String = String.Empty
For i As Integer = 0 To bHash.Length - 1
    sEncryptPass = sEncryptPass + bHash(i).ToString("x2").ToLower()
Next

A few added notes: The salt is stored in the database. sPlainTextPass contains the password+salt in plain text. I tried several different encodings including ASCII, UTF7, and UTF8. The database field is a varchar which is supposed to match to UTF8 from what I understand.

Help?

Upvotes: 4

Views: 2643

Answers (4)

Nate
Nate

Reputation: 141

I have experienced this same thing... I think it has to do with the update statement to get your hash populated in the users table.

I found the problem to be the data type. If in the expression 

UserPassword + CAST(Salt AS VARCHAR(36))

the UserPassword column is nvarchar, the result will be nvarchar and a different hash.

I fixed my problem by casting the expression to concatenate the password and salt to varchar.

A good test to see if this really is the same problem is by running this...

SELECT HASHBYTES('SHA1', UserPassword + CAST(Salt AS VARCHAR(36))),
HASHBYTES('SHA1', CAST( UserPassword + CAST(Salt AS VARCHAR(36))as varchar)) 
FROM Users

Upvotes: 0

Evgeniy Muzyka
Evgeniy Muzyka

Reputation: 16

Use nvarchar type instead of varchar in T-SQL and results will match as well

Upvotes: 0

usr
usr

Reputation: 171246

You cannot hash a string, you can only hash bytes. Therefore SQL Server and you have to use an encoding to translate a string to bytes.

SQL Server doesn't support UTF8. You need to find out what it uses and match that encoding in your application. For an nvarchar, I'd try Encoding.Unicode and test this with rare and special characters.

There seems to be some information on the web about this topic which I found Googling for "hashbytes encoding": http://weblogs.sqlteam.com/mladenp/archive/2009/04/28/Comparing-SQL-Server-HASHBYTES-function-and-.Net-hashing.aspx Although I must say the blog post contains obvious errors and cannot be trusted.

Upvotes: 2

sybkar
sybkar

Reputation: 386

I've seen this type of thing happen before with Sybase SQLAnywhere. Depending on the SQL db you're using, it may salt the hash on it's own, in which case, you'd never get a matching hash (without knowing the inner workings of the DB, that is)

EDIT:

If you're looking to compare passwords, in light of any internal-salting, you could send the .NET hashed password to the DB, hash it again there, and check for equality with the stored value. This of course assumes that you've stored the password in a similar way.

Upvotes: 0

Related Questions