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.