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

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?

create FUNCTION [dbo].[fn_HMAC](        @hash_algorithm varchar(25),        @key VARCHAR(MAX),        @message VARCHAR(MAX))RETURNS VARCHAR(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), @position INT        SET @position = 1        SET @i_key_pad = ''        SET @o_key_pad = ''    --splice ipad & opod with key    WHILE @position <= LEN(@key)       BEGIN        SET @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 + 1       END     --pad i_key_pad & o_key_pad        SET @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))ENDGO

and

CREATE function [dbo].[fn_PBKDF2] (@hash_algorithm varchar(25),@password varchar(max),@salt varchar(max),@rounds int,@outputbytes int)returns varchar(max)asbegindeclare @hlen intselect @hlen = len(HASHBYTES(@hash_algorithm, 'test'))declare @l int SET @l = (@outputbytes +@hLen -1)/@hLendeclare @r int SET @r = @outputbytes - (@l - 1) * @hLendeclare @t varchar(max), @u varchar(max), @block1 varchar(max)declare @output varchar(max) SET @output = ''declare @i int SET @i = 1while @i <= @l    begin    set @block1 = @salt +cast(cast(@i as varbinary(4)) as varchar(4))    set @u = dbo.fn_HMAC(@hash_algorithm,@password,@block1)    set @t = @u    declare @j int     SET @j = 1    while @j < @rounds        begin        set @u = dbo.fn_HMAC(@hash_algorithm,@password,@u)        declare @k int         SET @k = 0         DECLARE @workstring varchar(max)         SET @workstring = ''        while @k < @hLen            begin            set @workstring = @workstring + char(ascii(substring(@u,@k+1,1))^ascii(substring(@t,@k+1,1)))            set @k = @k + 1            end        set @t = @workstring        set @j = @j + 1        end        select @output = @output + case when @i = @l then left(@t,@r) else @t end    set @i = @i + 1    end  return master.dbo.fn_varbintohexstr(convert(varbinary(max), @output ))endGO

then create the stored proc to generate the hash password

CREATE PROCEDURE [dbo].[EncryptPassword2]    @passwordIn AS VARCHAR(MAX),    @passwordOut VARCHAR(max) OUTPUTAS        -- Generate 16 byte salt    DECLARE @saltVarBin VARBINARY(max)    SET @saltVarBin = (SELECT CAST(newid() AS binary(16)))    -- Base64 encode the salt    DECLARE @saltOut VARCHAR(max)    SET @saltOut = cast('' as xml).value('xs:base64Binary(sql:variable("@saltVarBin"))', 'varchar(max)')    -- Decode salt to pass to function fn_PBKDF2    DECLARE @decodedsalt varchar(max)    SET @decodedsalt = convert(varchar(max),(SELECT CAST('' 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 hashing    DECLARE @passwordVarBinStr VARCHAR(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 64    DECLARE @passwordVarBin VARBINARY(max)    SET @passwordVarBin =  (select cast('' as xml).value('xs:hexBinary( substring(sql:variable("@passwordVarBinStr"), sql:column("t.pos")) )', 'varbinary(max)') from (select case substring(@passwordVarBinStr, 1, 2) when '0x' then 3 else 0 end) as t(pos))    SET @passwordOut = cast(''as xml).value('xs:base64Binary(sql:variable("@passwordVarBin"))', 'varchar(max)')RETURN

Finally execute the stored proc using

DECLARE @NewPassword varchar(100)DECLARE @EncryptPassword VARCHAR(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.

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)]public static string EncodePassword(string pass, string salt) {  byte[] bytes = Encoding.Unicode.GetBytes(pass);  byte[] src = Convert.FromBase64String(salt);  byte[] dst = new byte[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', 1GORECONFIGUREGOIF OBJECT_ID (N'dbo.EncodePassword', N'FS') IS NOT NULL  DROP FUNCTION dbo.EncodePassword;    IF EXISTS (SELECT name FROM sys.assemblies WHERE name='UDF')DROP ASSEMBLY UDFCREATE ASSEMBLY UDF FROM 'FULL_PATH_TO.dll' WITH PERMISSION_SET=SAFE    GOCREATE FUNCTION EncodePassword(  @pass NVARCHAR(4000),  @salt NVARCHAR(4000))RETURNS NVARCHAR(4000)-- return NULL if any input parameter(s) are NULLWITH RETURNS NULL ON NULL INPUTASEXTERNAL NAME UDF.[NAMESPACE.CLASSNAME].EncodePasswordGO

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 :)

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