SQL创建.Net成员资格提供程序用户 [英] SQL to create .Net Membership Provider users

问题描述

我有一个SQL脚本，可以在数据库中创建用户. 它使用.Net成员身份存储的proc.

I have a SQL script that creates users in in my database. It uses the .Net membership stored procs.

目前，它工作正常.

唯一的问题是密码是保存的明文.在这里我应该更改为 salted/encrypted (不确定在这里使用什么术语)

The only issue is that the passwords are saved clear text. What should I change here to they are salted/encrypted (Not sure what term to use here)

GO

DECLARE @return_value int,
  @UserId uniqueidentifier


EXEC @return_value = [dbo].[aspnet_Membership_CreateUser]
  @ApplicationName = N'Theater',
  @UserName = N'sam.sosa',
  @Password = N'mypassword',
  @PasswordSalt = N'eyhKDP858wdrYHbBmFoQ6DXzFE1FB+RDP4ULrpoZXt6f',
  @Email = N'[e-mail]',
  @PasswordQuestion = N'Whats your favorite color',
  @PasswordAnswer = N'Fusia',
  @IsApproved = 1,
  @CurrentTimeUtc = '2010-03-03',
  @CreateDate = '2010-03-03',
  @UniqueEmail = 1,
  @PasswordFormat = 0,
  @UserId = @UserId OUTPUT

SELECT @UserId as N'@UserId'

SELECT 'Return Value' = @return_value

GO

谢谢！

推荐答案

对于哈希"密码存储模式，它只是简单地计算:

Under the hood it looks like for the "Hashed" password storage mode, it simply calculates:

SHA1(盐+密码)

SHA1(Salt + Password)

Salt以Base64编码存储，因此在与(Unicode)密码连接之前必须先对其进行解码.最后，对结果进行Base64编码以进行存储.

The Salt is stored Base64 encoded, so it must be decoded prior to being concatenated with the (Unicode) password. Finally, the result is Base64 encoded for storage.

以下(可怕的)SQL将输出适当编码的值，该值可以替换您当前拥有的"mypassword".您还必须将@PasswordFormat设置为1，以指示密码是散列存储的.

The following (horrible) SQL will output a suitably encoded value which can be substituted in place of the "mypassword" that you currently have. You must also set @PasswordFormat to 1 to indicate that the password is stored hashed.

declare @salt nvarchar(128)
declare @password varbinary(256)
declare @input varbinary(512)
declare @hash varchar(64)

-- Change these values (@salt should be Base64 encoded)
set @salt = N'eyhKDP858wdrYHbBmFoQ6DXzFE1FB+RDP4ULrpoZXt6f'
set @password = convert(varbinary(256),N'mypassword')

set @input = hashbytes('sha1',cast('' as  xml).value('xs:base64Binary(sql:variable(''@salt''))','varbinary(256)') + @password)
set @hash = cast('' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable(''@input'')))','varchar(64)')

-- @hash now contains a suitable password hash
-- Now create the user using the value of @salt as the salt, and the value of @hash as the password (with the @PasswordFormat set to 1)

DECLARE @return_value int, 
  @UserId uniqueidentifier 

EXEC @return_value = [dbo].[aspnet_Membership_CreateUser] 
  @ApplicationName = N'Theater', 
  @UserName = N'sam.sosa', 
  @Password = @hash, 
  @PasswordSalt = @salt, 
  @Email = N'[e-mail]', 
  @PasswordQuestion = N'Whats your favorite color', 
  @PasswordAnswer = N'Fusia', 
  @IsApproved = 1, 
  @CurrentTimeUtc = '2010-03-03', 
  @CreateDate = '2010-03-03', 
  @UniqueEmail = 1, 
  @PasswordFormat = 1, 
  @UserId = @UserId OUTPUT 

SELECT @UserId as N'@UserId' 

SELECT 'Return Value' = @return_value 

这篇关于SQL创建.Net成员资格提供程序用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！