Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

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.

9 comments:

  1. Venkat,

    I look forward for your videos everyday. You are doing great job. Keep up the good work.

    Regarding this topic: Wouldn't SQL Server Policy Management be another approach to resolve this issue. This way we can set the security policy to be 3 attempts across all the databases in the enterprise and also set up unlock rules to remove the lock if it is past 3 attempts. Just a thought.

    Thanks,
    Lisa

    ReplyDelete
  2. Dear Venkat, Can you help me with my problem in this tutorial,
    WHEN I ENTER WRONG USERNAME IT LOCKED THE ACCOUNT even if the account is not register,,
    tnx more power!

    ReplyDelete
    Replies
    1. dear arvin , cheak the store procedure u created may be u set the returncode=1 instead of -1.
      if (@count=1) then select returncode=1 else select returncode=-1

      Delete
  3. Hi Arvin,
    Check for a valid Username against the database first and then check whether the account is locked or not. That should take care of the issue.

    ReplyDelete
  4. Hi venkat sir is anyother way is there to unclock the user name

    ReplyDelete
  5. Dear Sir,
    Thanks to your videos, they are very helpfull and also interesting.
    I want to use master password instead.
    Means if any user's account is locked and he enters master password in password field then it should unlock his account.

    ReplyDelete
  6. Dear Sir,

    Thank you Very Much

    Sir,
    I want to create a SqlServer Job, But i dont find SQL SERVER AGENT in my SSMS.
    Please Help.
    Thank you

    ReplyDelete
    Replies
    1. SQL SERVER AGENT is not available in ssms Express edition.....

      Delete
  7. SQL SERVER AGENT will available in ssms if we run as admin.

    ReplyDelete

It would be great if you can help share these free resources