Generate ASP.Net Membership password hash in pure T-SQL

Question

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           Password              GeneratedPassword
cbehrens           34098kw4D+FKJ==       34098kw4D+FKJ==

Note: that's bogus base-64 text there. I've got base64_encode and decode functions that round-trip correctly. Here's my attempt, which doesn't work:

SELECT UserName, Password, dbo.base64_encode(HASHBYTES('SHA1', dbo.base64_decode(PasswordSalt) +  'test')) As TestPassword FROM aspnet_Users U JOIN aspnet_membership M ON U.UserID = M.UserID

I've tried a number of variations on the theme, to no avail. I need to do this in pure T-Sql; involving a console app or something like that will double the work.

So if anyone can supply what precisely the syntax should be to duplicate that password from the ASP.Net membership stuff, I would greatly appreciate it.

Answer 1

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_BACKUP from [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
*/
Create Function dbo.AspNetHashCreate (@clearPass nvarchar(64), @encodedSalt nvarchar(64))
Returns nvarchar(128)
as
begin
    declare @binSalt varbinary(128)
    declare @binPass varbinary(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 password
    Select @binSalt = CAST(N'' as XML).value('xs:base64Binary(sql:column("bin"))','VARBINARY(128)') 
        from (Select @encodedSalt as bin) as temp;

    --  Hash the salt + pass, then convert to Base64 for the output
    Select @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 @result
end

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
*/
Create Function dbo.AspNetSaltCreate (@RndId uniqueidentifier)
    Returns nvarchar(24)
as
begin
    return
        (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!