Generate Asp.net Membership Password Hash In Pure T-sql

October 22, 2023 Post a Comment

I'm attempting to create a pure t-sql representation of the default SHA-1 password hashing in the ASP.Net Membership system. Ideally, what I would get would be this: UserName

Solution 1:

I wrote a hashing stored proc by reverse enginering the C# code from here ASP.NET Identity default Password Hasher, how does it work and is it secure? and some fantastic PBKDF2 SQL functions from here Is there a SQL implementation of PBKDF2?

First create these two functions taken from Is there a SQL implementation of PBKDF2?

createFUNCTION [dbo].[fn_HMAC]
(
        @hash_algorithm varchar(25),
        @keyVARCHAR(MAX),
        @messageVARCHAR(MAX)
)
RETURNSVARCHAR(MAX)


ASBEGIN--HASH key if longer than 16 characters
    IF(LEN(@key) >64)
        SET@key= HASHBYTES(@hash_algorithm,@key)


    DECLARE@i_key_pad VARCHAR(MAX), @o_key_pad VARCHAR(MAX), @positionINTSET@position=1SET@i_key_pad =''SET@o_key_pad =''--splice ipad & opod with key
    WHILE @position<= LEN(@key)
       BEGINSET@i_key_pad =@i_key_pad +CHAR(ASCII(SUBSTRING(@key, @position, 1)) ^54) 
        SET@o_key_pad =@o_key_pad +CHAR(ASCII(SUBSTRING(@key, @position, 1)) ^92) 
        SET@position=@position+1END--pad i_key_pad & o_key_padSET@i_key_pad =LEFT(@i_key_pad + REPLICATE('6',64),64)
        SET@o_key_pad =LEFT(@o_key_pad + REPLICATE('\',64),64)


RETURN HASHBYTES(@hash_algorithm,CONVERT(VARBINARY(MAX),@o_key_pad) + HASHBYTES(@hash_algorithm,@i_key_pad +@message))

END

GO

and

CREATEfunction [dbo].[fn_PBKDF2] 
(
@hash_algorithm varchar(25),
@passwordvarchar(max),
@saltvarchar(max),
@roundsint,
@outputbytesint
)
returnsvarchar(max)
asbegindeclare@hlenintselect@hlen= len(HASHBYTES(@hash_algorithm, 'test'))
declare@lintSET@l= (@outputbytes+@hLen-1)/@hLendeclare@rintSET@r=@outputbytes- (@l-1) *@hLendeclare@tvarchar(max), @uvarchar(max), @block1varchar(max)

declare@outputvarchar(max) 
SET@output=''declare@iintSET@i=1
while @i<=@lbeginset@block1=@salt+cast(cast(@iasvarbinary(4)) asvarchar(4))
    set@u= dbo.fn_HMAC(@hash_algorithm,@password,@block1)
    set@t=@udeclare@jintSET@j=1
    while @j<@roundsbeginset@u= dbo.fn_HMAC(@hash_algorithm,@password,@u)


        declare@kintSET@k=0DECLARE@workstringvarchar(max) 
        SET@workstring=''
        while @k<@hLenbeginset@workstring=@workstring+char(ascii(substring(@u,@k+1,1))^ascii(substring(@t,@k+1,1)))
            set@k=@k+1endset@t=@workstringset@j=@j+1endselect@output=@output+casewhen@i=@lthenleft(@t,@r) else@tendset@i=@i+1endreturn master.dbo.fn_varbintohexstr(convert(varbinary(max), @output ))


end
GO

then create the stored proc to generate the hash password

CREATEPROCEDURE [dbo].[EncryptPassword2]
    @passwordInASVARCHAR(MAX),
    @passwordOutVARCHAR(max) OUTPUT
AS-- Generate 16 byte saltDECLARE@saltVarBinVARBINARY(max)
    SET@saltVarBin= (SELECTCAST(newid() ASbinary(16)))

    -- Base64 encode the saltDECLARE@saltOutVARCHAR(max)
    SET@saltOut=cast(''as xml).value('xs:base64Binary(sql:variable("@saltVarBin"))', 'varchar(max)')

    -- Decode salt to pass to function fn_PBKDF2DECLARE@decodedsaltvarchar(max)
    SET@decodedsalt=convert(varchar(max),(SELECTCAST(''as xml).value('xs:base64Binary(sql:variable("@saltOut"))', 'varbinary(max)')))

    -- Build the password binary string from 00 + salt binary string + password binary string created by 32 byte 1000 iteration ORC_PBKDF2 hashingDECLARE@passwordVarBinStrVARCHAR(max)
    -- Identity V1.0 and V2.0 Format: { 0x00, salt, subkey } SET@passwordVarBinStr='0x00'+ REPLACE(master.dbo.fn_varbintohexstr(@saltVarBin) + (SELECT dbo.fn_PBKDF2('sha1', @passwordIn, @decodedsalt, 1000, 32)),'0x','')
    -- Identity V3.0 Format: { 0x01, prf (UInt32), iter count (UInt32), salt length (UInt32), salt, subkey } (comment out above line and uncomment below line)--SET @passwordVarBinStr = '0x01000000010000271000000010' + REPLACE(master.dbo.fn_varbintohexstr(@saltVarBin) + (SELECT dbo.fn_PBKDF2('SHA2_256', @passwordIn, @decodedsalt,10000, 32)),'0x','')-- Convert the password binary string to base 64DECLARE@passwordVarBinVARBINARY(max)
    SET@passwordVarBin=  (selectcast(''as xml).value('xs:hexBinary( substring(sql:variable("@passwordVarBinStr"), sql:column("t.pos")) )', 'varbinary(max)') from (selectcasesubstring(@passwordVarBinStr, 1, 2) when'0x'then3else0end) as t(pos))
    SET@passwordOut=cast(''as xml).value('xs:base64Binary(sql:variable("@passwordVarBin"))', 'varchar(max)')

RETURN

Finally execute the stored proc using

DECLARE@NewPasswordvarchar(100)
DECLARE@EncryptPasswordVARCHAR(max)

select@NewPassword='password12344'EXECUTE EncryptPassword2 @NewPassword, @PasswordOut=@EncryptPassword OUTPUT;

PRINT @EncryptPassword

Please note that the stored proc may need to be changed for later versions of SQL server as this was written specifically for 2005 and I belive conversion to base64 is different in later versions.

Solution 2:

if you are running 2005 or higher, you can create a CLR (.NET) UDF:

[SqlFunction(
  IsDeterministic = true, IsPrecise = true, 
  DataAccess = DataAccessKind.None,
  SystemDataAccess = SystemDataAccessKind.None
)]
publicstaticstringEncodePassword(string pass, string salt) {
  byte[] bytes = Encoding.Unicode.GetBytes(pass);
  byte[] src = Convert.FromBase64String(salt);
  byte[] dst = newbyte[src.Length + bytes.Length];
  Buffer.BlockCopy(src, 0, dst, 0, src.Length);
  Buffer.BlockCopy(bytes, 0, dst, src.Length, bytes.Length);
  using (SHA1CryptoServiceProvider sha1 = new SHA1CryptoServiceProvider()) {
    return Convert.ToBase64String(sha1.ComputeHash(dst));
  }
}

you need to include the following namespaces in your class:

using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

the class must be public.

build the .dll then run the following (per database you want to call the UDF) SQL statement:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

IF OBJECT_ID (N'dbo.EncodePassword', N'FS') ISNOTNULLDROPFUNCTION dbo.EncodePassword;    
IF EXISTS (SELECT name FROM sys.assemblies WHERE name='UDF')
DROP ASSEMBLY UDF

CREATE ASSEMBLY UDF FROM'FULL_PATH_TO.dll'WITH PERMISSION_SET=SAFE    
GO

CREATEFUNCTION EncodePassword(
  @pass NVARCHAR(4000),
  @salt NVARCHAR(4000)
)
RETURNS NVARCHAR(4000)
-- return NULL if any input parameter(s) are NULLWITHRETURNSNULLONNULL INPUT
ASEXTERNAL NAME UDF.[NAMESPACE.CLASSNAME].EncodePassword
GO

obviously, replace 'NAMESPACE.CLASSNAME' with the namespace (if any) and name of your class. and you might want to mess with the input parameter and return value sizes.

then call the UDF with T-SQL:

SELECT UserName,Password
,dbo.EncodePassword('PASSWORD', PasswordSalt) As TestPassword FROM aspnet_Users U 
JOIN aspnet_membership M ON U.UserID = M.UserID

works for me :)

Solution 3:

Instead of using CLR you can create this function in SQL. On this page you will find very nice example:

http://svakodnevnica.com.ba/index.php?option=com_kunena&func=view&catid=4&id=4&Itemid=5&lang=en#7

P.S. byte[] src = Convert.FromBase64String(salt); is correct way...

Fox

Solution 4:

OP requested "pure" sql - I think using CLR is cheating ;) I was stubborn and had to figure it out for myself so here's what I did.

NOTE: Make a backup first!!

Select * into dbo.aspnet_Membership_BACKUPfrom[dbo].[aspnet_Membership]

Function to calculate the hashes:

/*
    Create compatible hashes for the older style ASP.Net Membership

    Credit for Base64 encode/decode: http://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql
*/CreateFunction dbo.AspNetHashCreate (@clearPass nvarchar(64), @encodedSalt nvarchar(64))
Returns nvarchar(128)
asbegindeclare@binSaltvarbinary(128)
    declare@binPassvarbinary(128)

    declare@result nvarchar(64)

    Select@binPass=CONVERT(VARBINARY(128), @clearPass)

    --  Passed salt is Base64 so decode to bin, then we'll combine/append it with passwordSelect@binSalt=CAST(N''as XML).value('xs:base64Binary(sql:column("bin"))','VARBINARY(128)') 
        from (Select@encodedSaltas bin) as temp;

    --  Hash the salt + pass, then convert to Base64 for the outputSelect@result=CAST(N''as XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'NVARCHAR(64)')
        from (Select HASHBYTES('SHA1', @binSalt+@binPass) as bin) as temp2;

    --  Debug, check sizes--Select DATALENGTH(@binSalt), DATALENGTH(@binPass), DATALENGTH(@binSalt + @binPass)return@resultend

I was changing a Membership database from "clear" passwords to the more secure hashed format - call it like this:

Update [dbo].[aspnet_Membership] set PasswordFormat =1, Password = dbo.AspNetHashCreate(password, PasswordSalt) where PasswordFormat =0

Even with my database originally set to "clear" passwords, the salt values were created with each record, however, if for some reason you don't have salt values you can create them with this:

/*
    Create compatible salts for the older style ASP.Net Membership (just a 16 byte random number in Base64)

    Note: Can't use newId() inside function so just call it like so: dbo.AspNetSaltCreate(newId())

    Credit for Base64 encode: http://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql
*/CreateFunctiondbo.AspNetSaltCreate (@RndId uniqueidentifier)
    Returnsnvarchar(24)
asbeginreturn
        (Select CAST(N'' as XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'NVARCHAR(64)')
            from (select cast(@RndId as varbinary(16)) as bin) as temp)
end

Then use it like this:

Update [dbo].[aspnet_Membership] set PasswordSalt = dbo.AspNetSaltCreate(newId()) where PasswordSalt =''

Enjoy!

Solution 5:

According to this SO post, this is the process they use to encode/hash your password/salt.

publicstringEncodePassword(string pass, string salt)
{
    byte[] bytes = Encoding.Unicode.GetBytes(pass); //HEREbyte[] src = Encoding.Unicode.GetBytes(salt); //and HEREbyte[] dst = newbyte[src.Length + bytes.Length];
    Buffer.BlockCopy(src, 0, dst, 0, src.Length);
    Buffer.BlockCopy(bytes, 0, dst, src.Length, bytes.Length);
    HashAlgorithm algorithm = HashAlgorithm.Create("SHA1");
    byte[] inArray = algorithm.ComputeHash(dst); //then they has the bytes not the string...return Convert.ToBase64String(inArray);
}

I could be wrong but it looks like you are missing the step where you get the bytes for the password and salt. Can you try adding that and see if it works?

Lab Sql