How to Authenticate Users with the SQL Server database table using C# with Example? : The Official Microsoft ASP.NET Forums

Reply

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

Apr 21, 2008 12:28 PM|LINK

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

Moved by ecbruck on Apr 21, 2008 12:41 PM

Reply

Re: How to Authenticate Users with the SQL Server database table using C# with Example?

Apr 21, 2008 05:43 PM|LINK

check this out

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

Marked as answer by XiaoYong Dai – MSFT on Apr 25, 2008 08:34 AM

Reply

Re: How to Authenticate Users with the SQL Server database table using C# with Example?

Apr 23, 2008 02:05 AM|LINK

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.

ask and being asked.
answer and being answered.

Marked as answer by XiaoYong Dai – MSFT on Apr 25, 2008 08:34 AM

Reply

Re: How to Authenticate Users with the SQL Server database table using C# with Example?

Jun 27, 2008 05:35 PM|LINK

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

Zeeshan Faisal
http://www.zeeshanfaisal.com
Web Application Developer
Discover Communication Inc.

Edited by zeeca on Jun 27, 2008 07:31 PM
Edited by zeeca on Oct 23, 2008 05:01 AM
Edited by zeeca on Apr 05, 2012 01:26 AM
Edited by zeeca on Apr 05, 2012 01:29 AM

Reply

mysterenet

Member

8 Points

12 Posts

Re: How to Authenticate Users with the SQL Server database table using C# with Example?

Sep 06, 2008 12:26 PM|LINK

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

Thank you for any attempt to help me and good programming to everyone

Reply

acit

Member

472 Points

96 Posts

Re: How to Authenticate Users with the SQL Server database table using C# with Example?

Sep 10, 2008 02:31 AM|LINK

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.

ask and being asked.
answer and being answered.

Reply

Quakerjacks

Member

4 Points

2 Posts

Re: How to Authenticate Users with the SQL Server database table using C# with Example?

Oct 22, 2008 10:29 PM|LINK

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

Reply

zeeca

Member

230 Points

56 Posts

Re: How to Authenticate Users with the SQL Server database table using C# with Example?

Oct 23, 2008 04:58 AM|LINK

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.

Zeeshan Faisal
http://www.zeeshanfaisal.com
Web Application Developer
Discover Communication Inc.

Edited by zeeca on Oct 23, 2008 05:00 AM

Reply

Quakerjacks

Member

4 Points

2 Posts

Re: How to Authenticate Users with the SQL Server database table using C# with Example?

Oct 24, 2008 08:05 PM|LINK

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!!!!!!!!!

Reply

zeeca

Member

230 Points

56 Posts

Re: How to Authenticate Users with the SQL Server database table using C# with Example?

Oct 24, 2008 09:18 PM|LINK

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

Zeeshan Faisal
http://www.zeeshanfaisal.com
Web Application Developer
Discover Communication Inc.

Edited by zeeca on Oct 24, 2008 10:21 PM

How to Authenticate Users with the SQL Server database table using C# with Example? RSS

19 replies

bilal_hru

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

srulyt

Re: How to Authenticate Users with the SQL Server database table using C# with Example?

acit

Re: How to Authenticate Users with the SQL Server database table using C# with Example?

zeeca

Re: How to Authenticate Users with the SQL Server database table using C# with Example?

mysterenet

Re: How to Authenticate Users with the SQL Server database table using C# with Example?

acit

Re: How to Authenticate Users with the SQL Server database table using C# with Example?

Quakerjacks

Re: How to Authenticate Users with the SQL Server database table using C# with Example?

Compilation Error

zeeca

Re: How to Authenticate Users with the SQL Server database table using C# with Example?

Quakerjacks

Re: How to Authenticate Users with the SQL Server database table using C# with Example?

zeeca

Re: How to Authenticate Users with the SQL Server database table using C# with Example?

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft