Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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

6 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

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.