Select statement using HASHBYTES not working

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Select statement using HASHBYTES not working

Author

Topic

simonrl
Starting Member

3 Posts

Posted - 2015-04-29 : 07:28:06

Hi all

I've been reading up on storing hashed passwords and am having some trouble getting a login from a hashed and salted password.

I've stripped this down to bare minimum to explain the problem so I'm calling a SP using:

spCreateAdminLogin 'password', 'wibble'

(in practice the salt will be a 16 character GUID)

The business part of the SP does:

INSERT INTO tblLogins (txtPassword, txtGUID) VALUES(HashBytes('SHA2_512', @Password+@Salt), @Salt)

I'm using SHA2_512 after reading up on hashing. The column txtPassword is data type varbinary(128) and if I run:

SELECT * FROM tblLogins

I can see the binary data and I can confirm it's the same 128 character string generated each time from the passed in @Password = 'password' and @Salt = 'wibble'

So far so good...

Now to return the data. Before I get onto doing an actual login I just wanted to check if I can return the data directly in Management Studio.

So I ran:

SELECT * FROM tblLogins WHERE txtPassword = HashBytes('SHA2_512', 'passwordwibble')

Which returned 0 rows.

I also tried:

SELECT * FROM tblLogins WHERE txtPassword = CAST(HashBytes('SHA2_512', 'passwordwibble') AS VARCHAR(128))

Which also returned 0 rows.

I think I've got the data type correct, the hashed and salted password is being stored correctly and is being generated the same for the same passed in password and salt; but I can't return the data.

Am I missing something glaringly obvious here?

Thanks,
Simon

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-29 : 10:49:04

Is @Password or @Salt NVARCHAR by chance?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA