Unlocking the locked user accounts - Part 94

Suggested Videos
Part 91 - Forms authentication and user registration
Part 92 - Forms authentication against users in database table
Part 93 - Forms authentication and locking user accounts

In Part 93, of this video series we have discussed about locking user accounts, if a user repeatedly enters the wrong password. The accounts are locked to prevent hackers from guessing passwords and dictionary attacks. Please watch Part 93, before proceeding with this video.



In this video, we will discuss about unlocking the locked user accounts. There are several ways to unlock the user accounts.
Approach 1: The end user calls the technical help desk. The authorised person can issue a simple update query to remove the lock.

Update tblUsers 
set RetryAttempts = null, IsLocked = 0, LockedDateTime = null
where username='CallersUserName'

However, running UPDATE queries manually against a production database is not recommended, as it is error prone and we may un-intentionally modify other rows that we do not intend to update.



Approach 2: Another approach would be to provide a web page that lists all the locked user accounts. From this page, the helpdesk agent, can unlock the account by clicking a button. This is not as dangerous as running a manual update query, but still a manual process and may be in-efficient. If you know how to write basic ADO.NET code, this approach should not be very difficult to achieve. If you are new to ADO.NET, Click here for a video series that I have recorded on ADO.NET

Approach 3: Another approach would be, to create a SQL Server job. This job checks tblUsers table for locked accounts periodically and then unlocks them. The frequency at which the job should run is configurable. 

In this video, we will discuss about creating and scheduling the SQL Server Job to unlock user accounts.

First let us write the update query to unlock the user accounts. For example, The organization's policy is that, the user account can only be unlocked after 24 hours, since the account is locked. The update query to satisfy the organization's policy is shown below. DateDiff function is used in the update query. If you are new to DateTime functions in SQL Server, please check this video by clicking here.

Update tblUsers 
set RetryAttempts = null, IsLocked = 0, LockedDateTime = null
where IsLocked = 1
and datediff(HOUR,LockedDateTime,GETDATE()) > 24

Let us now, schedule this update query to run every 30 minutes, every day. This can be very easily done using sql server agent jobs. In this video, we will discuss about creating and scheduling sql server agent jobs, for sql server 2008.
1. Open sql serevr management studio
2. In the object explorer, check if "SQL Server Agent" is running.
3. If "SQL Server Agent" is not running, right click and select "Start".
4. Click on the "+" sign, next to "SQL Server Agent" to expand.
5. Right click on "Jobs" folder and select "New Job".
6. In the "New Job" dialog box, provide a meaningful name. Let us call it, "Unlock user accounts job".
7. Fill in Owner, Category and Description fields accordingly. Make sure the Enabled checkbox is selected.
8. Select "Steps" tab, and click "New" button
9. In the "New Job Step" dialog box, give a meaningful step name. Let us call it "Execute Update Query"
10. Select Transact-SQL Script as "Type"
11. Select the respective Database.
12. In the "Command" text box, copy and paste the UPDATE query, and click OK
13. In the "New Job" dialog box, select "Schedules" and click "New" button
14. In the "New Job Schedule" dialog box, give a meaningful name to the schedule. Let us call it "Run Every 30 Minutes Daily"
15. Choose "Recurring" as "Schedule type"
16. Under "Frequency", set "Occurs" = "Daily" and "Recurs every" = "1" Days.
17. Under "Daily Frequency", set "Occurs every" = "30" Minutes.
18. Finally fill in the schedule start and end dates, under "Duration"
19. Click OK, twice and you are done.

This job, will run every 30 minutes daily, and unlocks the accounts that has been locked for more than 24 hours.