Script to create an ASP.NET membership provider. User

Question

I created a bunch of insert scripts to add a new user to the aspnet_Membership and aspnet_User tables. I cannot claim that he cannot find my user.

Has anyone tried to create a new membership user through T-SQL inserts? I have to do it like this because our C # / ASP.NET user code is currently not working.

Answer 1

From MSDN:

declare @now datetime set @now= GETDATE() exec aspnet_Membership_CreateUser 'MyAppName','admin1',' pass@word1 ', '',' [e-mail] ','','',1,@now,@now,0,0,null 

http://msdn.microsoft.com/en-us/library/gg252020(v=office.14).aspx

Answer 2

This is most likely because the membership provider hashes the password (and its salts) to store and retrieve (the hash is compared, not an unmanaged password).

I assume that you started the aspnet_Membership_CreateUser stored procedure. This will result in an unconfirmed password (and the wrong salt).

In other words, you need to use the membership provider / API to create, receive, and authenticate.

Answer 3

Try the following scripts:

Step 1. User-defined function for encoding password and salt

 CREATE FUNCTION [dbo].[base64_encode] (@data VARBINARY(MAX)) RETURNS VARCHAR(MAX) WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT BEGIN RETURN ( SELECT [text()] = @data FOR XML PATH('') ) END GO 

Step 2. Saved Membership User Creation Procedure

 CREATE PROCEDURE [dbo].[CreateUser] @UserName nvarchar(256) , @ClearTextPassword nvarchar(128) , @Email nvarchar(256) , @pUserId uniqueidentifier AS BEGIN DECLARE @ApplicationName nvarchar(256) DECLARE @PasswordFormat int DECLARE @UnencodedSalt uniqueidentifier DECLARE @Password nvarchar(128) DECLARE @PasswordSalt nvarchar(128) DECLARE @Now DATETIME DECLARE @UniqueEmail int DECLARE @UserId uniqueidentifier SET @ApplicationName = 'ApplicationName' --Find in aspnet_Applications.ApplicationName SET @PasswordFormat = 1 SET @UnencodedSalt = NEWID() SET @PasswordSalt = dbo.base64_encode(@UnencodedSalt) SET @Password = dbo.base64_encode(HASHBYTES('SHA1', CAST(@UnencodedSalt as varbinary(MAX)) + CAST(@ClearTextPassword AS varbinary(MAX)) )) SET @Now = getutcdate() SET @UniqueEmail = 1 SET @ UserId=@pUserId BEGIN TRANSACTION --DECLARE @UserId uniqueidentifier EXECUTE [dbo].[aspnet_Membership_CreateUser] @ApplicationName ,@UserName ,@Password ,@PasswordSalt ,@Email ,NULL ,NULL ,1 -- IsApproved == true ,@Now ,@Now ,@UniqueEmail ,@PasswordFormat ,@UserId OUTPUT COMMIT END GO 

Step 3. Create a user

 DECLARE @UserId uniqueidentifier SET @UserId = NewId() EXECUTE [dbo].[CreateUser] 'UserName' --@UserName ,' UserP@ssword ' --@ClearTextPassword ,' [e-mail] ' --@Email ,@UserId --User uniqueidentifier 

Additional notes:

• Check if your application name is spelled correctly in the web.config membership element.
• Remember to create roles and add a user to a role.