Critical Developers

Programmers Knowledge Base

Login Attempts in ASP.Net and MS-SQL Server

Here I will explain you how to restrict user login for three times/ five times as per you required. The logic is heavily depends on SQL server and ASP.net is used for UI and calling sql procedures. The best part here is, it requires only one procedure to implement failed login attempts. You can inject the logic in your existing application or can implement a new one.

Just read all below commented lines for the same. Its very easy.


-- Create Table User_Master or Alter your existing Login's Table

CREATE TABLE User_Master
(
UserID INT IDENTITY (1, 1) PRIMARY KEY,
First_Name VARCHAR (50),
Last_Name VARCHAR (50),
User_Code VARCHAR (50),
Password VARCHAR (50),
Date_Added_Mod_Del DATETIME NOT NULL,
Status_Flag CHAR (1),
IsAdmin CHAR (1),
-- ========[ADDED COLUMN TO ACHIEVE LOGIN ATTEMPT]=======
Attempts INT DEFAULT ((0)) NOT NULL,
ipAddr VARCHAR (50),
lockInTime DATETIME NULL,
lockOutTime DATETIME NULL,
lastFailTime DATETIME NULL,
lastSuccessTime DATETIME NULL
)


-- Alter your existing Login's Table

ALTER TABLE USER_MASTER
ADD Attempts int NOT NULL DEFAULT (0),
ipAddr varchar(50) NULL, (IPADDR Column has not used)
lockInTime datetime NULL,
lockOutTime datetime NULL,
lastFailTime datetime NULL,
lastSuccessTime datetime NULL


-- SPROC
-- For Eg. Here 5 attempts to Lock the Account and 30 mins to Release the Account Logic/Algorithm are commented in SPROC

CREATE PROCEDURE usp_UsersLogin
@stUserCode VARCHAR(50),
@stPassword VARCHAR(50),
@inUserID INT OUTPUT ,
@chIsAdmin CHAR OUTPUT,
@biSuccess BIT OUTPUT,
@stSQLError VARCHAR(100) OUTPUT
As
BEGIN
SET @biSuccess = 0
SET @stSQLError = 'Invalid UserName And Password.'

-- INVALID USERNAME
IF NOT EXISTS (SELECT 1 FROM User_Master U WHERE U.User_Code = @stUserCode)
BEGIN
SET @biSuccess = 0
SET @stSQLError = 'Invalid UserName.'
RETURN
END

-- VALID USERNAME, INVALID PASSWORD.
IF EXISTS (SELECT 1 FROM User_Master U WHERE U.User_Code = @stUserCode AND ISNULL(U.Password,'') <> ISNULL(@stPassword,''))
BEGIN
-- Reset Locking Parameters if User has not completed his all Attempts within same 1 hour and trying to login again after 1 hour
-- eg. If User fails 4 time and left, after 1 hour he again trying with wrong password then his account should not locked
-- i.e we cannot make that attempt to be the last attempt instead we provide user again 5 attempts
IF EXISTS(SELECT 1 FROM User_Master U WHERE U.User_Code = @stUserCode AND ISNULL(DATEDIFF(mi,U.lastFailTime,GETDATE()),0)>=60)
BEGIN
UPDATE User_Master SET attempts=0,lockInTime=NULL,lockOutTime=NULL,lastFailTime=NULL WHERE User_Code = @stUserCode
END
-- Check Whether the User's Login Attempts Over i.e 5 If Not Increase the Attempts by 1
IF EXISTS(SELECT 1 FROM User_Master WHERE User_Code = @stUserCode AND attempts<4) -- If Attempt Is < 4
BEGIN
-- Increase Login Attempts By 1
UPDATE User_Master SET attempts=attempts+1,lastFailTime=GETDATE() WHERE User_Code = @stUserCode
SET @biSuccess = 0
SET @stSQLError = 'Invalid Password.'
RETURN
END
-- If its Last Attempt i.e- 5th SET lockOutTime Of User to Current Time + 30 mins
ELSE IF EXISTS(SELECT 1 FROM User_Master WHERE User_Code = @stUserCode AND attempts=4)
BEGIN
UPDATE User_Master SET attempts=attempts+1,lastFailTime=GETDATE(),lockInTime=GETDATE(),lockOutTime=DATEADD(mi,30,GETDATE()) WHERE User_Code = @stUserCode
SET @biSuccess = 0
SET @stSQLError = 'Your account has been temporarily suspended for too many login failures. Please try again in 30 mins.'
RETURN
END
-- For Attempts Greater Than 5 We will not update theirs lockOutTime
ELSE
BEGIN
UPDATE User_Master SET lastFailTime=GETDATE() WHERE User_Code = @stUserCode
SET @biSuccess = 0
SET @stSQLError = 'Your account has been temporarily suspended for too many login failures. Please try again in 30 mins.'
RETURN
END
END

-- VALID USERNAME & PASSWORD.
IF EXISTS (SELECT 1 FROM User_Master U WHERE U.User_Code = @stUserCode AND isNULL(U.Password,'') = isNULL(@stPassword,''))
BEGIN
--Check User's lockOutTime - If its Over 30 mins since he/she was locked allow him/her succesful Login
IF EXISTS(SELECT 1 FROM User_Master WHERE User_Code = @stUserCode AND GETDATE()>=lockOutTime OR lockOutTime IS NULL)
BEGIN
UPDATE User_Master SET attempts=0,lockInTime=NULL,lockOutTime=NULL,lastFailTime=NULL,lastSuccessTime=GETDATE() WHERE User_Code = @stUserCode -- Reset All locking parameters
SELECT @inUserID=U.UserID ,@chIsAdmin = U.IsAdmin FROM User_Master U WHERE U.User_Code = @stUserCode
SET @biSuccess = 1
SET @stSQLError = ''
RETURN
END
ELSE
BEGIN
SET @biSuccess = 0
SET @stSQLError = 'Your account has been temporarily suspended for too many login failures. Please try again in 30 mins.'
RETURN
END

END

END -- First End Here

Lets move to devenv (i.e Visual Studio)
1) Login.aspx (HTMLIZATION)
Here I am not writing the UI of whole page as you are smart in drag & drop.
What we need at here, are:- (LoginId/Password TextBoxes, Button to Submit the form,
and finally Validation of form - the way you like to validate)

<asp:TextBox runat="server" ID="txtLoginID" maxlength="50" /></asp:TextBox>


<asp:TextBox runat="server" ID="txtPwd" TextMode="Password" maxlength="50" /></asp:TextBox>


<asp:Button ID="btnSubmit" runat="server" UseSubmitBehavior="false"OnClick="btnSubmit_Click"
                  Text="Login" ValidationGroup="login" />


<asp:RequiredFieldValidator ID="valtxtLoginID" ControlToValidate="txtLoginID"
                                         SetFocusOnError="true" runat="server" Display="None"
                                         ErrorMessage="Enter Login ID."ValidationGroup="login">
</asp:RequiredFieldValidator>


<asp:RequiredFieldValidator ID="valtxtPwd" ControlToValidate="txtPwd"
                                         SetFocusOnError="true" runat="server" Display="None"
                                         ErrorMessage="Enter Password."ValidationGroup="login">
</asp:RequiredFieldValidator>


<asp:ValidationSummary ID="ValidationSummary1" ValidationGroup="login" runat="server"
                                    HeaderText="Please rectify the following error(s):"
                                    ShowMessageBox="True" ShowSummary="False" />

2) Login.aspx.cs (Code Behind)

protected void btnSubmit_Click(object sender, EventArgs e)
{
LoginClass loDBAccess=new LoginClass(); // create new instance of your DataAccess Class
int? liUserId = null;
char? lchIsAdmin=null;
bool lbSuccess = false;
string lsSQLError = string.Empty;
lbSuccess = loDBAccess.UserLogin(txtLoginID.Text.Trim(), txtPwd.Text.Trim(), out liUserId, out lchIsAdmin, out lsSQLError);
if (lbSuccess == true)
{
// Code Here, Your Success Logic
// And Finally
Response.Redirect("HomePage.aspx");
}
else
{

// Alert lsSQLError (OutParam of SQL SPROC)

}
}

NOTE:- Now you can simply use IPAddress Column if you really need Visitors IP Address

==[END]=====

Comments (2) -

  • Naveen

    29-04-2013 10:58:10 | Reply

    Can you pls send me the complete code for Login Attempts in MS-SQL Server with ASP.Net. actually i want to implement this concept in my Application. i tried lots of code but i didn't achieve the desire output. if it is possible to pls send me the complete code. this code help me to clear my concept about this problem after then i can implement in my application.....

    Thanks,
    Regards,
    Naveen  

Loading