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

Forms authentication and locking user accounts - Part 93

Suggested Videos
Part 90 - Forms authentication using user names list in web.config
Part 91 - Forms authentication and user registration
Part 92 - Forms authentication against users in database table

Please watch Parts 90, 91 and 92 before proceeding. In this video we will discuss about locking or disabling user accounts, after repeated invalid attempts to login. 

For example, if a user enters wrong username and password, he will be given 3 more chances, to enter the correct password. After the 3 chances are elapsed, the account will be locked. After the account is locked, the user will not be able to log in, even, if he provides a correct user name and password.

Most of the banking applications does this for security reasons.



Drop the table, tblUsers, that we have created in Part 90. Recreate tblUsers table using the script below.
Create table tblUsers
(
[Id] int identity primary key,
[UserName] nvarchar(100),
[Password] nvarchar(200),
[Email] nvarchar(100),
[RetryAttempts] int,
[IsLocked] bit,
[LockedDateTime] datetime
)



Since, we have changed the structure of the table. The stored procedure 'spRegisterUser' that we created in Part 91, will break. The corrected stored procedure is show below.
Alter proc spRegisterUser  
@UserName nvarchar(100),  
@Password nvarchar 200),  
@Email nvarchar 200)  
as  
Begin  
 Declare @Count int  
 Declare @ReturnCode int  
   
 Select @Count = COUNT(UserName)   
 from tblUsers where UserName = @UserName  
 If @Count > 0  
 Begin  
  Set @ReturnCode = -1  
 End  
 Else  
 Begin  
  Set @ReturnCode = 1  
  --Change: Column list specified while inserting
  Insert into tblUsers([UserName], [Password], [Email]) 
  values  (@UserName, @Password, @Email)  
 End  
 Select @ReturnCode as ReturnValue  
End  

Stored procedure - 'spAuthenticateUser', that we created in Part 92, needs to be changed as shown below, to support the Account locking functionality.
Alter proc spAuthenticateUser
@UserName nvarchar(100),
@Password nvarchar(200)
as
Begin
Declare @AccountLocked bit
Declare @Count int
Declare @RetryCount int

Select @AccountLocked = IsLocked
from tblUsers where UserName = @UserName

--If the account is already locked
if(@AccountLocked = 1)
Begin
Select 1 as AccountLocked, 0 as Authenticated, 0 as RetryAttempts
End
Else
Begin
-- Check if the username and password match
Select @Count = COUNT(UserName) from tblUsers
where [UserName] = @UserName and [Password] = @Password

-- If match found
if(@Count = 1)
Begin
-- Reset RetryAttempts 
Update tblUsers set RetryAttempts = 0
where UserName = @UserName

Select 0 as AccountLocked, 1 as Authenticated, 0 as RetryAttempts
End
Else
Begin
-- If a match is not found
Select @RetryCount = IsNULL(RetryAttempts, 0)
from tblUsers
where UserName = @UserName

Set @RetryCount = @RetryCount + 1

if(@RetryCount <= 3)
Begin
-- If re-try attempts are not completed
Update tblUsers set RetryAttempts = @RetryCount
where UserName = @UserName

Select 0 as AccountLocked, 0 as Authenticated, @RetryCount as RetryAttempts
End
Else
Begin
-- If re-try attempts are completed
Update tblUsers set RetryAttempts = @RetryCount,
IsLocked = 1, LockedDateTime = GETDATE()
where UserName = @UserName

Select 1 as AccountLocked, 0 as Authenticated, 0 as RetryAttempts
End
End
End
End

Copy and Paste the following version of AuthenticateUser() method in Login.aspx.cs page.
private void AuthenticateUser(string username, string password)
{
    // ConfigurationManager class is in System.Configuration namespace
    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    // SqlConnection is in System.Data.SqlClient namespace
    using (SqlConnection con = new SqlConnection(CS))
    {
        SqlCommand cmd = new SqlCommand("spAuthenticateUser", con);
        cmd.CommandType = CommandType.StoredProcedure;

        //Formsauthentication is in system.web.security
        string encryptedpassword = FormsAuthentication.HashPasswordForStoringInConfigFile(password, "SHA1");

        //sqlparameter is in System.Data namespace
        SqlParameter paramUsername = new SqlParameter("@UserName", username);
        SqlParameter paramPassword = new SqlParameter("@Password", encryptedpassword);

        cmd.Parameters.Add(paramUsername);
        cmd.Parameters.Add(paramPassword);

        con.Open();
        SqlDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            int RetryAttempts = Convert.ToInt32(rdr["RetryAttempts"]);
            if (Convert.ToBoolean(rdr["AccountLocked"]))
            {
                lblMessage.Text = "Account locked. Please contact administrator";
            }
            else if (RetryAttempts > 0)
            {
                int AttemptsLeft = (4 - RetryAttempts);
                lblMessage.Text = "Invalid user name and/or password. " +
                    AttemptsLeft.ToString() + "attempt(s) left";
            }
            else if (Convert.ToBoolean(rdr["Authenticated"]))
            {
                FormsAuthentication.RedirectFromLoginPage(txtUserName.Text, chkBoxRememberMe.Checked);
            }
        }
    }
}

Invoke AuthenticateUser() method in the click event handler of the login button control.
AuthenticateUser(txtUserName.Text, txtPassword.Text);

In the next video session, we will discuss about enabling the disabled user accounts.

6 comments:

  1. IF THE USER DOES NOT HAVE ACCOUNT AND ENTERED NAME, WHICH DOES NOT EXIST IN DATABASE THEN .. WHAT WILL HAPPEN ?

    ( wasim )

    ReplyDelete
  2. In this case the following message will appear:

    "Account locked. Please contact administrator"

    The reason is because
    Select @AccountLocked = IsLocked
    from tblUsers where UserName = @UserName
    does not match in this case.

    You can alter the procedure
    by asking first:

    select @count = COUNT(UserName)
    from tblUsers
    where Username = @UserName

    if (@count = 0)
    Begin
    Select 0 as AccountLocked, 0 as Authenticated, 0 as RetryAttempts
    End
    else
    .... (the rest of procedure)

    In the Programm login.aspx.cs
    you add an else-condition:

    if (Convert.ToBoolean(rdr["AccountLocked"]))
    {
    ...
    }
    else
    {
    lblMessage.Text = "User does not exist! Please register first!";
    }

    Kind regards

    ReplyDelete
  3. Please ,how i enter an invalid username and password
    lblmessage text become "Account locked. Please contact administrator
    "
    but i want to make it "Username does not exist"?

    ReplyDelete
  4. create proc spAuthenticateUser
    @UserName nvarchar(100),
    @Password nvarchar(200)
    as
    Begin
    Declare @AccountLocked bit
    Declare @Count int
    Declare @RetryCount int
    Select @count=count(username) from tblusers where username=@UserName
    If(@count=0)
    Begin
    Select 0 as AccountLocked, 0 as Authenticated, 0 as RetryAttempts
    End
    Else
    Begin
    Select @AccountLocked = isnull(IsLocked,0)
    from tblUsers where UserName = @UserName


    if(@AccountLocked = 1)
    Begin
    Select 1 as AccountLocked, 0 as Authenticated, 0 as RetryAttempts
    End
    Else
    Begin

    Select @Count = COUNT(UserName) from tblUsers
    where [UserName] = @UserName and [Password] = @Password


    if(@Count = 1)
    Begin

    Update tblUsers set RetryAttempts = 0
    where UserName = @UserName

    Select 0 as AccountLocked, 1 as Authenticated, 0 as RetryAttempts
    End
    Else
    Begin

    Select @RetryCount = IsNULL(RetryAttempts, 0)
    from tblUsers
    where UserName = @UserName

    Set @RetryCount = @RetryCount + 1

    if(@RetryCount <= 3)
    Begin

    Update tblUsers set RetryAttempts = @RetryCount
    where UserName = @UserName

    Select 0 as AccountLocked, 0 as Authenticated, @RetryCount as RetryAttempts
    End
    Else
    Begin

    Update tblUsers set RetryAttempts = @RetryCount,
    IsLocked = 1, LockedDateTime = GETDATE()
    where UserName = @UserName

    Select 1 as AccountLocked, 0 as Authenticated, 0 as RetryAttempts
    End
    End
    End
    End
    End

    ReplyDelete
  5. I can register successfully but when i am trying to login with same username and password even for first time it not login
    it lock the account why
    As i done same code

    ReplyDelete
  6. initial check needed as in

    alter proc spAunthenticateUser
    @UserName nvarchar(25),
    @Password nvarchar(15)
    as
    Begin
    Declare @AccountLocked bit
    Declare @Count int
    Declare @RetryCount int

    select @count = count(UserName)
    from tblUsers

    if(@Count = 0 )
    begin
    select -1 as ReturnValue
    end
    else
    begin
    Select @AccountLocked = IsLocked
    from tblUsers where UserName = @UserName

    --If the account is already locked
    if(@AccountLocked = 1)
    Begin
    Select 1 as AccountLocked, 0 as Authenticated, 0 as RetryAttempts
    End
    Else
    Begin
    -- Check if the username and password match
    Select @Count = COUNT(UserName) from tblUsers
    where [UserName] = @UserName and [Password] = @Password

    Rest as above by Venkat

    ReplyDelete

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