jump to navigation

Unlock User or Reset Password via Database query – ASP.NET Membership February 13, 2010

Posted by codinglifestyle in ASP.NET, CodeProject.
Tags: , , , , , ,
trackback

This morning I was logging in to my website and couldn’t log in.  My personal site uses the out-of-the-box ASP.NET v2 membership and roles.  This took a while to determine what was wrong because my own website didn’t tell me much, using a blanket unsuccessful message for any problem.  This lead me to believe my password was wrong or worse that my site had been hacked and the password changed!

It turned out I entered the wrong password too many times and locked myself out.  However, my site wasn’t programmed to tell me I was locked out (see here for improvement).  I probably entered the right password loads of times, but couldn’t tell because my account was locked.  Once I figured this out the easiest way to unlock the user was via the SQL query window as my site is deployed on an ISP.  You can unlock programatically, but I wasn’t sure how to via the database directly.  Luckily, a quick look through the sprocs revealed what I was looking for and the day was saved:

DECLARE @return_value int

EXEC @return_value = [dbo].[aspnet_Membership_UnlockUser]

@ApplicationName = N‘applicationName’,

@UserName = N‘user’

SELECT ‘Return Value’ = @return_value

GO

If you don’t know your application name, the query below can be handy.  If you need to reset your password you can use the information obtained by this query along with the sproc below.  First, create a new user or you can use an existing user with a known password.  Next, execute the query below.

SELECT au.username, aa.ApplicationName, password, passwordformat, passwordsalt

FROM aspnet_membership am

INNER JOIN aspnet_users au

ON (au.userid = am.userid)

INNER JOIN aspnet_applications aa

ON (au.applicationId = aa.applicationid)

Now that you have a valid password, salt, and password type you can set that password information to the account which needs to be reset.  So take the valid password, salt, and password format and put it in the sproc below along with the application name and user which needs to be reset.

–Prepare the change date

DECLARE @changeDate datetime

set @changeDate = getdate()

–set the password

exec aspnet_Membership_setPassword ‘applicationName’,

‘user’,

‘password’,

‘passwordsalt’,

@changeDate,

Passwordformat

Execute.  Now both users have the same password.  Good luck!

Ref: http://aquesthosting.headtreez.com/doc/b873561c-ab7a-4a8e-9934-cc9366af8a81,http://mitchelsellers.com/Blogs/tabid/54/EntryID/23/Default.aspx, http://msdn.microsoft.com/en-us/library/system.web.security.membershipuser.unlockuser.aspx

About these ads

Comments»

1. khurram - June 8, 2010

good………..nice, but can we reset without giving any already useraccount….mean to set a new password???

2. Mark - January 3, 2013

You can set a new password by using the plaintext password format.
UPDATE aspnet_Membership SET Password=’Password’, PasswordFormat=0 WHERE ID=”

3. Pete - April 2, 2014

The stored procedure stores the password as clear text in the database, whereas my other passwords are hashed. How can I update my password but do it hashed?

codinglifestyle - April 3, 2014

The idea is to take an existing user with a known password (or create a new user with a known password) and take their hashed password to use in place of the locked account.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: