SQL HashBytes vs .Net HashAlgorithm

Question

I am working on integrating a new Authentication rest API that our web dev team wrote. The last piece I need to get working will return a User's User_Id GUID when given a username and password hash that matches the SHA1 Base64Encoded password hash stored in our user database.

Using the following VB.Net code with a hardcoded username and password salt, I am able to create a valid password hash.

.....
.....
Dim passwordHash As String = EncodePassword("Testing1234", "CQ4znO+Dd28GRle54xSW6A==")

Public Function EncodePassword(ByVal pass As String, ByVal saltBase64 As String) As String
    Dim bytes As Byte() = Encoding.Unicode.GetBytes(pass)
    Dim src As Byte() = Convert.FromBase64String(saltBase64)
    Dim dst As Byte() = New Byte(src.Length + bytes.Length - 1) {}
    Buffer.BlockCopy(src, 0, dst, 0, src.Length)
    Buffer.BlockCopy(bytes, 0, dst, src.Length, bytes.Length)
    Dim algorithm As HashAlgorithm = HashAlgorithm.Create("SHA1")
    Dim inArray As Byte() = algorithm.ComputeHash(dst)
    Dim passwordHash AS String = Convert.ToBase64String(inArray)
    Return passwordHash
End Function

However, I cannot create the same password hash in SQL using the following code.

DECLARE @newPassword NVARCHAR(128) = 'Testing1234'
DECLARE @encoded_salt NVARCHAR(128) = 'CQ4znO+Dd28GRle54xSW6A=='
DECLARE @encoded_hashed_password NVARCHAR(128) = dbo.base64_encode(HASHBYTES('SHA1',
                                                     CONVERT(VARBINARY(MAX), @encoded_salt)
                                                     + CONVERT(VARBINARY(MAX), @newPassword)))
SELECT @encoded_hashed_password AS Password_Hash

The function code for dbo.base64_encode is here:

ALTER FUNCTION [dbo].[base64_encode] (@data VARBINARY(MAX))
RETURNS VARCHAR(MAX)
WITH SCHEMABINDING,
RETURNS NULL  ON NULL INPUT
BEGIN
    RETURN (SELECT [text()] = @data FOR XML PATH(''));
END;

Corresponding decode function (not used in example, but included for completeness)

ALTER FUNCTION [dbo].[base64_decode] (@base64_text VARCHAR(MAX))
RETURNS VARBINARY(MAX)
WITH SCHEMABINDING,
RETURNS NULL ON NULL INPUT
BEGIN
    DECLARE @x XML;
    SET @x = @base64_text; 
    RETURN @x.[value]('(/)[1]', 'VARBINARY(max)');
END;

Can anyone help me figure out why the SQL script that appears to me to be equivalent to the VB.Net code does not produce the same result?

Answer 1

I ended up getting it to work a couple hours after I posted the question, and figured I'd provide an update for any future readers that come across this post.

The encode/decode functions that I shared previously were working. The T-SQL script that I shared was also working, but I was using the wrong salt value. What I ended up figuring out, was that the salt value stored in our aspnet_membership table was not actually used to salt the password. In actuality, the value used as a salt was a UNIQUEIDENTIFIER generated by a call to NEWID(). The salt value I shared ("CQ4znO+Dd28GRle54xSW6A==") was actually the base64 representation of the UNIQUEIDENTIFIER, used to "salt" the password. Once I figured that out, I was able to use the [base64_decode] function I previously shared to decode the base64 string to its VARCHAR representation, then convert the VARCHAR value back to the original UNIQUEIDENTIFIER.

  --Shows the conversion from salt string back to original UNIQUEIDENTIFIER
  SELECT CONVERT(UNIQUEIDENTIFIER, [dbo].[base64_decode]  
  ('CQ4znO+Dd28GRle54xSW6A==');

Once I had the original UNIQUEIDENTIFIER, I used THAT value to salt that password using the TSQL script I shared earlier and presto the salt values matched.

 DECLARE @salt UNIQUEIDENTIFIER = NEWID()
 DECLARE @encoded_salt NVARCHAR(128) = dbo.base64_encode(@salt)
 DECLARE @encoded_hashed_password NVARCHAR(128) = dbo.base64_encode(HASHBYTES('SHA1',
                                                      CONVERT(VARBINARY(MAX), @salt)
                                                      + CONVERT(VARBINARY(MAX), 
                                                      @NewPassword)))