I created a stored procedure to generate a SHA1 password hash:
alter procedure GetPasswordSp (
@pPassword char(50) output
if @pusername is null
set @pPassword = SUBSTRING(master.dbo.fn_varbintohexstr(HASHBYTES('SHA1', ltrim(rtrim(@pPasswordClear)))),3,50)
Its simple enough... when I build a test it runs fine and the value that is returned is what I expect... when I run it as a stored procedure I get a different return.
I assumed that there was an extra character begin padded in my code, so I changed it from a hash to simply display the hex results so I could see the extra chars
I test manually again: username=username, pw = Buckwheat
RETURN VALUE: 0x4275636b7768656174
Again, this is what I expected...
I copy/paste the same code into the SP
master.dbo.fn_varbintohexstr(CAST(ltrim(rtrim('@pPasswordClear')) AS varbinary))
and pass the same values
RETURN VALUE: 0x4200750063006b0077006800650061007400
NOTICE THAT IF YOU TAKE OUT THE zero-zeros THE VALUE IS THE SAME....
Any suggestions as to how to fix?
The hash key (SHA1) is going to be generated in SQL and also on a web page to validate passwords, if they don't match, well, you can imagine the problems.