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

Forms authentication against users in database table - Part 92

Suggested Videos
Part 89 - Windows authentication and folder level authorization
Part 90 - Forms authentication using user names list in web.config
Part 91 - Forms authentication and user registration

In Part 90, we have discussed about authenticating users against a list stored in web.config file. In Part 91, we have discussed about, registering users, if they do not have a username and password to log in. In this session, we will disuss about authenticating users against a list stored in a database table.

This is continuation to Part 91. Please watch Part 91, before proceeding with this video. Authenticating users against a list stored in web.config file is very easy. FormsAuthentication class exposes a static method Authenticate(), which does all the hardwork of authenticating users. 

If we want to authenticate users against a list stored in a database table, we will have to write the stored procedure and a method in the application to authenticate users.



First let us create a stored procedure, that accepts username and password as input parameters and authenticate users. 
Create Procedure spAuthenticateUser
@UserName nvarchar(100)
@Password nvarchar(100)
as
Begin
Declare @Count int

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

if(@Count = 1)
Begin
Select 1 as ReturnCode
End
Else
Begin
Select -1 as ReturnCode
End
End



Copy and paste the following private method in Login.aspx.cs page. This method invokes stored procedure 'spAuthenticateUser'. 
private bool 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();
        int ReturnCode = (int)cmd.ExecuteScalar();
        return ReturnCode == 1;
    }
}

Invoke AuthenticateUser() method, in the login button click event handler
if (AuthenticateUser(txtUserName.Text, txtPassword.Text))
{
    FormsAuthentication.RedirectFromLoginPage(txtUserName.Text, chkBoxRememberMe.Checked);
}
else
{
    lblMessage.Text = "Invalid User Name and/or Password";
}

9 comments:

  1. Hi,

    If I use the visual studio server or IIS express, the program runs well, but when I used the IIS of the local computer, regular web pages worked but not for those that need SQL server connections. I use window authentication for SQL server, but the integrated security=SSPI method didn't work, and I check the web, that seems to be the case in general. I noticed you used SQL server user id and password, but I don't have one, and I tried to set up, but it didn't work.

    Can you please tell how you did that? You have the localhost/WebApplication address, so you should be using the local IIS other than IIS express or visual studio's IIS.

    Can you answer either here or email me at san.cui@gmail.com Please delete my email address, if you post the question on the web.

    Thanks

    ReplyDelete
  2. In the Inetmgr, -> Application Pool -> DefaultAppPool. Right click on this DefaultAppPool. Go to Advanced settings, change the process Model identity, to LocalSystem. It should work.

    ReplyDelete
  3. Hello Sir,
    Thanks for videos of authentication. Could you give tutorial of authentication against "roles" (not users) in database. Thanks.

    ReplyDelete
    Replies
    1. I mean, in this tutorial, if the table Account has 3 fields (Username, password, role), then the web.config authorizes user based on their "role" to access the site, how can I do that? Thanks.

      Delete
  4. thanks a bunch kudvenkat,but i think it'so better to use asp.net instead of creating users on database,can you explain us ,how to use aspnet membership,it will be very intersting because you are the king of explaination in the world,thanks thanks

    ReplyDelete
  5. Thankyou sir,
    I want to know that how when we can restrict the user to access a page through url even in the same instance of the broswer

    ReplyDelete
  6. When I run Your Code Its getting Error:- Object Reference not set to an instance of an object.

    On Line: 41
    Line 39:
    Line 40: con.Open();
    Line 41: int returncode = (int)cmd.ExecuteScalar();
    Line 42: if (returncode == -1)

    ReplyDelete
  7. Hi, I am using Form authentication with Web Form. it gives me Access is denied error on Login page when I use form authentication.

    ReplyDelete
  8. Here is the procedure code
    /* alter procedure to be used in lesson 92*/
    /* alter procedure to be used in lesson 92*/
    Alter Procedure spAuthenticateUser
    @UserName nvarchar(100) ,
    @Password nvarchar(100)
    as
    Begin
    Declare @Account bit
    Declare @Count int
    Declare @RetryCount int
    Declare @AccountLocked bit

    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 user and password match
    Select @Count = COUNT(UserName)
    from tblUsers
    where [UserName] = @UserName and [Password] = @Password
    if(@Count = 1)
    Begin
    -- reset RetryAttempts
    Update tblUsers
    Set IsLocked = Null, LockedDateTime = null, RetryAttempts = null
    Where [UserName] = @UserName
    Select 0 as AccountLocked, 1 as Authenticated , 0 as RetryAttempts
    End
    else
    -- Match not found
    Select @RetryCount = IsNull(RetryAttempts,0)
    from tblUsers
    where [UserName] = @UserName

    Set @RetryCount = @RetryCount +1
    if (@RetryCount <= 3)
    Begin
    -- if retry attempts not completed
    Update tblUsers
    Set RetryAttempts = @RetryCount
    Where [UserName] = @UserName
    Select 0 as AccountLocked, 0 as Authenticated , 0 as RetryAttempts
    End
    else
    Begin
    -- if retry 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

    ReplyDelete

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