Asp.net

在純 T-SQL 中生成 ASP.Net Membership 密碼雜湊

  • April 7, 2016

我正在嘗試在 ASP.Net Membership 系統中創建預設 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 會員資料中複製該密碼的確切語法，我將不勝感激。

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

享受！

我通過從此處 ASP.NET Identity 預設密碼雜湊器對 C# 程式碼進行逆向工程來編寫雜湊儲存過程，它是如何工作的，它是否安全？還有一些很棒的 PBKDF2 SQL 函式 PBKDF2有 SQL 實現嗎？

首先創建這兩個函式取自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)


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 是不同的。

引用自：https://stackoverflow.com/questions/5033886

相關問答

Asp.net

對象“aspnet_CheckSchemaVersion”、數據庫“XXX”的 EXECUTE 權限被拒絕

  • October 10, 2011
檢視問答
Asp.net

如何手動創建 asp.net 會員提供者散列密碼？

  • March 31, 2010
檢視問答
Asp.net

ASP.NET 會員鹽？

  • August 12, 2009
檢視問答
Asp.net

將集合和可選參數傳遞給儲存過程 T-SQL - ASP.NET

  • September 21, 2020
檢視問答
Asp.net

在 asp 中用於 Windows 身份驗證的簡單自定義角色。網？

  • July 17, 2019
檢視問答
Asp.net

如何在不使用 MembershipProvider 的情況下使用 ASP.NET 登錄控制項？

  • October 14, 2018
檢視問答