生成ASP.Net会员密码哈希纯T-SQL [英] Generate ASP.Net Membership password hash in pure T-SQL

问题描述

我试图建立在ASP.Net会员制默认SHA-1密码哈希的纯T-SQL重新presentation。理想情况下，我会得到会是这样的：

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

请注意：这是假的基地-64的文字出现。我已经得到了正确的base64_en code和德code函数往返。这里是我的尝试，它不工作：

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

我已经尝试了一些关于这个主题的变化，都无济于事。我需要做的这个纯T-SQL;涉及到一个控制台应用程序或类似的东西将增加一倍的工作。

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.

所以，如果有人能提供什么precisely语法应该复制从ASP.Net成员资格的东西，密码，我将不胜AP preciate它。

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.

推荐答案

如果您运行的是2005或更高版本，可以创建一个CLR（.NET）UDF：

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;

的类必须是公开即可。

打造.dll文件，然后运行以下命令（每个数据库要调用UDF）的SQL语句：

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') 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。你可能要与输入参数和返回值大小一塌糊涂。

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.

然后调用UDF用T-SQL：

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

对我的作品：）

这篇关于生成ASP.Net会员密码哈希纯T-SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！