locked
How to Authenticate Users with the SQL Server database table using C# with Example? RRS feed

  • Question

  • User-1113204588 posted

    Hi all,

    Will anyone tell me how to Authenticate Users with the SQL Server database table using C#  with Example?

    I've tried/used
    Membership API controls for Login and Registering Users for authentication,

    but i need/require to store Username/Password in a DatabaseTable(SQL SERVER table) in Register.aspx and then Match them against the DB table in Login.aspx page.Plz forward the two files.

    I'm doing having great difficulty to accomplish that, Plz anyone provide me with the "Complete Example"  Easy to understand and simple code 

    to authenticate against a DB table  

    Thanks

     

    Monday, April 21, 2008 8:28 AM

Answers

  • User1046815810 posted

    check this out

    http://www.asp.net/learn/security/tutorial-06-cs.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 21, 2008 1:43 PM
  • User177932033 posted

    hi there. you may try these codes.(sorry, its in vb)

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If (Session("Check") <> Nothing) And Convert.ToBoolean(Session("Check")) Then

        If (User.Identity.IsAuthenticated) Then

         Response.Redirect("f_main.aspx")

         End If
    End If
    End Sub

    Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate

    Dim blnResult As Boolean = False

    blnResult = SiteSpecificAuthenticationMethod(Login1.UserName, Login1.Password)

    If (blnResult) Then

    Session("Check") = True

    e.Authenticated = True

    Else

    Session("Check") = False

    e.Authenticated = False

    End If
    End Sub

    Protected Function SiteSpecificAuthenticationMethod(ByVal UserName As String, ByVal Password As String) As Boolean

    Dim log_statement As String = "SELECT * FROM [user] where username='" & UserName & "' and password='" & Password & "'"

    Dim con2 As System.Data.SqlClient.SqlCommand

    Dim con1 As System.Data.SqlClient.SqlConnection

    Dim reader As System.Data.SqlClient.SqlDataReadercon1 = New System.Data.SqlClient.SqlConnection(SqlDataSource1.ConnectionString)

    con1.Open()

    con2 =
    New System.Data.SqlClient.SqlCommand(log_statement, con1)

    reader = con2.ExecuteReader()

    If reader.Read Then

    Return True

    Else

    Return False

    End If

    End Function

    thats the codes i used for my system. hope that it'll help you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 22, 2008 10:05 PM

All replies

  • User1046815810 posted

    check this out

    http://www.asp.net/learn/security/tutorial-06-cs.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 21, 2008 1:43 PM
  • User177932033 posted

    hi there. you may try these codes.(sorry, its in vb)

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If (Session("Check") <> Nothing) And Convert.ToBoolean(Session("Check")) Then

        If (User.Identity.IsAuthenticated) Then

         Response.Redirect("f_main.aspx")

         End If
    End If
    End Sub

    Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate

    Dim blnResult As Boolean = False

    blnResult = SiteSpecificAuthenticationMethod(Login1.UserName, Login1.Password)

    If (blnResult) Then

    Session("Check") = True

    e.Authenticated = True

    Else

    Session("Check") = False

    e.Authenticated = False

    End If
    End Sub

    Protected Function SiteSpecificAuthenticationMethod(ByVal UserName As String, ByVal Password As String) As Boolean

    Dim log_statement As String = "SELECT * FROM [user] where username='" & UserName & "' and password='" & Password & "'"

    Dim con2 As System.Data.SqlClient.SqlCommand

    Dim con1 As System.Data.SqlClient.SqlConnection

    Dim reader As System.Data.SqlClient.SqlDataReadercon1 = New System.Data.SqlClient.SqlConnection(SqlDataSource1.ConnectionString)

    con1.Open()

    con2 =
    New System.Data.SqlClient.SqlCommand(log_statement, con1)

    reader = con2.ExecuteReader()

    If reader.Read Then

    Return True

    Else

    Return False

    End If

    End Function

    thats the codes i used for my system. hope that it'll help you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 22, 2008 10:05 PM
  • User1110789954 posted

    Hi There,

    I'm new here. And i would like to say thanks to acit about his post I would translate that in C#.
    As i belive there are lots of other people who wants in C# and most of the places provide too complecated codes.
    This one is the simplest and to the point. I just converted it into C#. Please see below setp by step.

    Step 1:

    Create a page with the name of login.aspx
    Place login control on it. It's default id will be login1
    Create a another with name main_page.aspx (this page will be you home or default page) 

    Step 2:

     Codding for login.aspx.cs file

    using System;
    using System.Data;
    using System.Configuration;
    using System.Collections;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls;

    public partial class _Default : System.Web.UI.Page
    {
             
    protected void Page_Load(object sender, EventArgs e)
              {
                    // Check if the user is already loged in or not
                    if ((Session["Check"] != null) && (Convert.ToBoolean(Session["Check"]) == true))
                    {
                          // If User is Authenticated then moved to a main page
                          
    if (User.Identity.IsAuthenticated)
                                    Response.Redirect(
    "main_page.aspx");
                    
    }
               }
             
               protected
    void Login1_Authenticate(object sender, AuthenticateEventArgs e)
               {
                     Boolean blnresult;
                     blnresult =
    false;

                     // Pass UserName  and Password from login1 control to an authentication function which will check will check the user name and password from sql server.
                     // Then will retrun a true or false value into blnresult variable

                     blnresult = Authentication(Login1.UserName, Login1.Password);

                     // If blnresult has a true value then authenticate user 
                     if
    (blnresult == true)
                     {
                          // This is the actual statement which will authenticate the user
                          e.Authenticated =
    true;
                          // Store your authentication mode in session variable 
                         Session[
    "Check"] = true;
                   
    }
                  
    else
                        // If user faild to provide valid user name and password
                       
    e.Authenticated = false;
               }

               // Function name Authentication which will get check the user_name and passwrod from sql database then return a value true or false
              
    protected static Boolean Authentication(string username, string password)
              
    {
                    
    string sqlstring;
                     sqlstring =
    "Select user_name, password from [user_table] where user_name='" + username + "' and password ='" + password + "'";
                     
                     // create a connection with sqldatabase 
                     System.Data.SqlClient.
    SqlConnection con = new System.Data.SqlClient.SqlConnection(
                                  " Data Source=datebaseservername;Initial Catalog=datebasename;UserID=databaseusername;Password=databasepassword;Connect Timeout=10;TrustServerCertificate=True " ); 

                    // create a sql command which will user connection string and your select statement string
                    System.Data.SqlClient.
    SqlCommand comm = new System.Data.SqlClient.SqlCommand(sqlstring,con);
     
                    // create a sqldatabase reader which will execute the above command to get the values from sqldatabase
                   
    System.Data.SqlClient.
    SqlDataReader reader;

                    // open a connection with sqldatabase
                    con.Open();

                    // execute sql command and store a return values in reade
                   
    reader = comm.ExecuteReader();

                    // check if reader hase any value then return true otherwise return false
                   
    if (reader.Read())
                      
    return true;
                   
    else
                      
    return false;
               }

    }

    coding need to be added in web.config this code will redirect a user to login.aspx page if user is not logged in and also restrict anonymous users.

    <system.web>
            <
    authentication mode="Forms">
                       <
    forms loginUrl="login.aspx" name="login" protection="All"/>
            </
    authentication>
            <
    authorization>
                    <
    deny users="?"/>
           </
    authorization>
    </system.web>

    If you need more help you can e-mail me at zeeskt74@hotmail.com or visit www.zeeshanfaisal.com

    Thanks

    Friday, June 27, 2008 1:35 PM
  • User1850392437 posted

    thank you a lot acit for your post, it will help me to advance in my project, but i have a problem is that the type of 'System.Data.SqlClient.SqlDataReadercon1' is not defiened. do you have any idea if i must download a framework or how to solve the problem? Your help will make me advance more in my project. thanks in advance   

    Saturday, September 6, 2008 8:26 AM
  • User177932033 posted

    hi mysterenet.
    basically the 'system.data.sqlclient.sqldatareadercon1' was to define the connection string that the system will connect to.
    i want to simplify my code therefore i used the existed connection string which is 'sqldatasource1'.
    there's no need for you to download a framework or anything.
    you only need to do a few modification to your codes only.

    Tuesday, September 9, 2008 10:31 PM
  • User-772185867 posted
    Before I even start A BIG THANKS IS IN order for putting up the code!!!  I have used the code and am having problems with the

    Data Source=ServerName;Initial Catalog=Database;UserID=userid;Password=passwordi;Connect Timeout=10;TrustServerCertificate=True);

    When I run the project this the error I get -->

    Compilation Error

    Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

    Compiler Error Message: CS1026: ) expected

    Source Error:

    Line 53:                  // create a connection with sqldatabase 
    Line 54:                  System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(
    Line 55:                                       Data Source=Confirm;Initial Catalog=IMB;UserID=sa;Password=mmi;Connect Timeout=10;TrustServerCertificate=True); 
    Line 56:                                       
    Line 57:                 // create a sql command which will user connection string and your select statement string 

    Any suggestions????

    Here is what my project is -  I have a SQL user database that I would like to use to Authenticate Users to login into my wesite, after they login I need to run a few start procedures based upon a user.

    Thanks 

    Wednesday, October 22, 2008 6:29 PM
  • User1110789954 posted

    Hi Quakerjacks,
                            First i want to thank you. Because you found a bug/error in my code.
                            And i apologies for any inconvenience.
                           
                           It's a small mistake you just need to add double quotes before and after the connection string.
                           See the following correct code.

                            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(
                                          " Data Source=Confirm;Initial Catalog=IMB;UserID=sa;Password=mmi;Connect Timeout=10;TrustServerCertificate=True " );
                           
                            In the above string you can see quotes in red color before and after the connection string that's all you need to put in ur code and you are good to go.
                            I also fixed my above code.
    feel free to ask if you have any problem.
    Thanks.

    Thursday, October 23, 2008 12:58 AM
  • User-772185867 posted

    Your Awsome thanks!!!![:D]

    Can I run something by you??  This is kind of new to me I am a Java/PhP guy.  So this is what I am trying to accomplish I have a web server running SQL Server 2005 with a database and a user table.  I need to create a login webpage using the user name and password from the database and then run a SQL Start procedure based upon a user in the table.  Sorry I know it’s kind of long but was wondering if you could point me in the right direction????

    Thanks A Million!!!!!!!!!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

     

    Friday, October 24, 2008 4:05 PM
  • User1110789954 posted

    Hi Quakerjacks,

                          As i understand you are already able to authenticate user. Now u want to call a stored procedure from sql database for that user.

    Read the following article. It explains how to call sql procedure. 

    http://support.microsoft.com/kb/320916

    thanks

    Friday, October 24, 2008 5:18 PM
  • User-1910540858 posted

    Hi ,

    i have used this code and this is working. But i have another link for sign up and it should redirect to the page for completing sign up but this link is not working.please give me a solution for this.  

     

    Tuesday, June 2, 2009 2:20 PM
  • User1110789954 posted

    Hi there,

    You can use following code by using link Button to redirect to your login page.

              Response.Redirect("myloginpage.aspx");

    Thanks

    Wednesday, June 3, 2009 11:52 PM
  • User1032829669 posted

    Hi acit i've used your code aswell, it was exactly what I was looking for, but I got the same error as mysterynet. I havent created a database as yet though, would creating one solve my problem?

    Tuesday, September 1, 2009 10:34 PM
  • User1850392437 posted

    Smile hiiiiiiii if i still rememeber because i don't use asp no more. you choose where you have dropdownlist...., you choose sqldatasource. you put it in your graphic. you make the connection and it works automatically. i hope to help you even if i'm begginer in this field 

    Wednesday, September 2, 2009 8:02 AM
  • User1032829669 posted

    Hey

    Thanks a lot, i think u've just solved my problemSmile

    Wednesday, September 2, 2009 9:46 AM
  • User1850392437 posted

    you're welcome, that's make me happy that the problem was solved Laughing,  good programing

    Wednesday, September 2, 2009 9:58 AM
  • User-1317541885 posted

    I'm having a problem with the c sharp example specifically the line

            blnresult = Authentication(Login1.username, Login1.Password);

     

    Error 1 The name 'Login1' does not exist in the current context C:\Documents and Settings\Administrator\Desktop\WebApplication1\WebApplication1\login.aspx.cs 32 36 WebApplication1

    The compiler tells me "The name 'Login1" does not exist in the current context". I'm using VS2010. On login aspx I've added a login control called Login1 so it should be seen. Any ideas?

    Thursday, November 11, 2010 7:28 AM
  • User1110789954 posted

    Hi There,

    Just double click on your Login1 control and then copy this code inside that function. If still doesn't work send me an email i'll send you a sample project.

    Regards, 

    Zeeshan

    Thursday, November 11, 2010 1:40 PM
  • User1394008366 posted

    thanks a lot  sir........

    Sunday, March 13, 2011 4:14 PM
  • User1800615666 posted

    WARNING, DON'T USE THIS CODE ANYWHERE! It has a trivial but HUGE SQL injection vulnerability. See method Authentication and how you're forming the SQL statement. Never pass user input straight to SQL statements!

    See this for more information:

    http://en.wikipedia.org/wiki/SQL_injection

    Monday, June 4, 2012 5:27 AM