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

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
-
-
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 = FalseblnResult = SiteSpecificAuthenticationMethod(Login1.UserName, Login1.Password)
If (blnResult) ThenSession(
"Check") = Truee.Authenticated =
True ElseSession(
"Check") = Falsee.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 Functionthats 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
-
-
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 = FalseblnResult = SiteSpecificAuthenticationMethod(Login1.UserName, Login1.Password)
If (blnResult) ThenSession(
"Check") = Truee.Authenticated =
True ElseSession(
"Check") = Falsee.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 Functionthats 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.comThanks
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 theData 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
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 problem
Wednesday, September 2, 2009 9:46 AM -
User1850392437 posted
you're welcome, that's make me happy that the problem was solved
, 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 WebApplication1The 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:
Monday, June 4, 2012 5:27 AM