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";
}