在纯 T-SQL 中生成 ASP.Net 会员密码哈希

Question

我正在尝试在 ASP.Net 会员系统中创建默认 SHA-1 密码哈希的纯 t-sql 表示。理想情况下，我会得到这样的结果：

UserName           Password              GeneratedPassword
cbehrens           34098kw4D+FKJ==       34098kw4D+FKJ==

注意：那是伪造的 base-64 文本。我有正确往返的 base64_encode 和解码函数。这是我的尝试，但不起作用：

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

我尝试了该主题的多种变体，但无济于事。我需要在纯 T-Sql 中执行此操作；涉及控制台应用程序或类似的东西将使工作量加倍。

因此，如果有人能够提供从 ASP.Net 会员资料中复制该密码的准确语法，我将不胜感激。

Answer 1

我通过对这里的 C# 代码进行反向工程编写了一个哈希存储过程 ASP.NET Identity 默认密码哈希器，它是如何工作的，安全吗？ 以及这里的一些出色的 PBKDF2 SQL 函数 PBKDF2 是否有 SQL 实现？

首先创建这两个函数，取自 PBKDF2有SQL实现吗？

create FUNCTION [dbo].[fn_HMAC]
(
        @hash_algorithm varchar(25),
        @key VARCHAR(MAX),
        @message VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)


AS
BEGIN
    --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))

END

GO

然后

CREATE function [dbo].[fn_PBKDF2] 
(
@hash_algorithm varchar(25),
@password varchar(max),
@salt varchar(max),
@rounds int,
@outputbytes int
)
returns varchar(max)
as
begin

declare @hlen int
select @hlen = len(HASHBYTES(@hash_algorithm, 'test'))
declare @l int 
SET @l = (@outputbytes +@hLen -1)/@hLen
declare @r int 
SET @r = @outputbytes - (@l - 1) * @hLen
declare @t varchar(max), @u varchar(max), @block1 varchar(max)

declare @output varchar(max) 
SET @output = ''

declare @i int 
SET @i = 1
while @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 ))


end
GO

创建存储过程来生成哈希密码

CREATE PROCEDURE [dbo].[EncryptPassword2]
    @passwordIn AS VARCHAR(MAX),
    @passwordOut VARCHAR(max) OUTPUT
AS

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

最后执行存储过程使用

DECLARE @NewPassword varchar(100)
DECLARE @EncryptPassword VARCHAR(max)

select @NewPassword = 'password12344'                                           

EXECUTE EncryptPassword2 @NewPassword, @PasswordOut = @EncryptPassword OUTPUT;

PRINT @EncryptPassword

请注意，对于更高版本的 SQL Server，存储过程可能需要更改，因为这是专门为 2005 年编写的，我相信在更高版本中转换为 base64 是不同的。

Answer 2

如果您运行的是 2005 或更高版本，则可以创建 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));
  }
}

您需要在类中包含以下命名空间：

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

该类必须是 public。

构建 .dll，然后运行以下（每个要调用 UDF 的数据库）SQL 语句：

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

IF 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 UDF

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

CREATE FUNCTION EncodePassword(
  @pass NVARCHAR(4000),
  @salt NVARCHAR(4000)
)
RETURNS NVARCHAR(4000)
-- return NULL if any input parameter(s) are NULL
WITH RETURNS NULL ON NULL INPUT
AS
EXTERNAL NAME UDF.[NAMESPACE.CLASSNAME].EncodePassword
GO

显然，将“NAMESPACE.CLASSNAME”替换为您的类的命名空间（如果有）和名称。您可能想弄乱输入参数和返回值的大小。

然后使用 T-SQL 调用 UDF：

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

对我有用:)

Answer 3

您可以在 SQL 中创建此函数，而不是使用 CLR。在此页面上，您会发现非常好的示例：

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

PS byte[] src = Convert.FromBase64String(salt);是正确的方法...

狐狸

Answer 4

OP 请求“纯”sql - 我认为使用 CLR 是作弊；）我很固执，必须自己弄清楚，所以这就是我所做的。

注意：先备份！！

Select * into dbo.aspnet_Membership_BACKUP from [dbo].[aspnet_Membership]

计算哈希值的函数：

/*
    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

我正在将会员数据库从“清晰”密码更改为更安全的哈希格式 - 像这样调用它：

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

即使我的数据库最初设置为“清晰”密码，盐值也是为每条记录创建的，但是，如果由于某种原因您没有盐值，您可以使用以下命令创建它们：

/*
    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

然后像这样使用它：

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

享受吧！

Answer 5

根据 this SO post， 这是他们用来编码/散列您的密码/盐的过程。

public string EncodePassword(string pass, string salt)
{
    byte[] bytes = Encoding.Unicode.GetBytes(pass); //HERE
    byte[] src = Encoding.Unicode.GetBytes(salt); //and HERE
    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);
    HashAlgorithm algorithm = HashAlgorithm.Create("SHA1");
    byte[] inArray = algorithm.ComputeHash(dst); //then they has the bytes not the string...
    return Convert.ToBase64String(inArray);
}

我可能是错的，但看起来您错过了获取密码和盐字节的步骤。您可以尝试添加它并看看它是否有效？