Solved

How do I authenticate a user against an SQL database? I am stumped... I thought it would be easier.

Posted on 2007-04-02
3
481 Views
Last Modified: 2012-08-13
I have an ASP.net application and I need to authenticate a user against an SQL database. Essentially, there is a button click that occurs on a demographic information form. There is an SQL table called 'users', which contains the authorization code and the real name of each authorized user. I have a textbox control on the page called 'txtCode' and I want the person to enter their code and then when the button click occurs, I want to validate this code they have entered against the 'user' table in SQL.

If validation is successful:
1. their real name as associated with their code they entered will show up in a textbox called 'username'

0
Comment
Question by:jazjef
3 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 18840901
I trust you realize that there is almost no security with that approach.  Nonetheless, here's how I would do it. First, create a udf to do the lookup.  Then call the stored procedure from VB.

Here's the procedure:
Create Proc usp_GetUserName (@code varchar(10))
AS
Select UserName from Users where AuthCode=@code
GO


Here's the VB code:
  Dim objCon As new SQLConnection SqlConnection ('put connection string here')
  Dim objCmd  as New SQLCommand ('usp_GetUserName',objConn)
  Dim objReader As SqlDataReader
  objCmd.CommandType = CommandType.StoredProcedure
  objCmdParameters.Add("@Code", txtCode)
  objCon.Open()
  objReader = objCmd.ExecuteReader
  if objReader.read then
    me.username=Convert.ToString(sqlReader.GetValue(0))
  else
   msgbox ('Invalid code')
 endif
 objReader.Close()
 objCon.Close()
 objCmd.dispose()
 objCon.dispose()
0
 
LVL 10

Expert Comment

by:ksaul
ID: 18840903
Do you intent to authorize a user based only on a code?  Or is there a userid that was already collected and in a session variable?  Generally there would be a userid and password for authentication.  Either way, I would first create a stored procedure on the database that takes the input(s) and returns a success or failure result.  A stored procedure in this situation is more secure as well as more efficient.  If the login succeeds you would then set the User Name.

The stored procedure could be something like


CREATE Procedure j_sp_ValidateUser(@UserId varchar(20), @Code varchar(20), @Result bit OUTPUT)
 --change parameters to match User table datatypes
AS
IF EXISTS
      (SELECT 1
      FROM Users
      WHERE UserID = @UserID
      AND Code = @Code)
        BEGIN
            SET @Result = 1
        END
      ELSE
        BEGIN
            SET @Result = 0
        END

Then on the server side of the button click, create your database connection and command objects.  Add parameters to the command object using your form variables for the values.  Execute the command and check the output parameter.

If the login succeeds set the form variable to the UserName.
0
 
LVL 3

Accepted Solution

by:
ieciep earned 500 total points
ID: 18842362
This is sample code for a ASP.Net Login page (using Visual Basic.Net code behind) with OleDB connection to an Access Database using ADO.Net.
Pre-Code Setup:
1. Create a New ASP.NET Web Application. I called mine NorthLogin2
2. Rename the default aspx Webform to Login or Index or something more descriptive. I called mine Login2.aspx
3. Assuming you are in Visual Studio.Net right click on this form in the Solution Explorer and Select Set as Start Page.
4. Find your Northwind.mdb file (The classic Northwind Database in Access)
Under Tools, Options, Tables/Queries Tab select Run Permissions as Owner's
5. Create a new table for login requirements. I called mine tblUser
Columns U_id (autonumber), U_Name (text), and U_Password (text with Input Mask of Password)
6. Create Query (i.e. Stored Procedure) to validate login(s). I called mine sp_ValidateUser
In the SQL Designview the code for that query is
        SELECT COUNT(*) AS Num_of_User
         FROM tblUser
         WHERE (((tblUser.U_Name)=[@UserName]) AND ((tblUser.U_Password)=[@Password]));

This query counts the number of users that it retrieves which matches the where clause. The @UserName and @Password are the values passed in to the query from the Webform.
7. Close database, because you can not access it while it is open.

Let the coding begin:
1. Create the API for your Login Webform. You want to have two textboxes for the user to enter their login information. You will also want to have some kind of visual feedback to the user regarding the status of the login; i.e. Is the login successful? Usually that is done by redirecting to the page they are trying to login to. If it is not successful, and whether they provided the information in the two textboxes.

Login2.aspx
        <%@ Page Language="vb" AutoEventWireup="false" Codebehind="Login2.aspx.vb" Inherits="NorthLogin2.WebForm1"%>
        <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
        <html>
              <head>
                  <title>Northwind Database Login</title>
                    <meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
                    <meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
                    <meta content="JavaScript" name="vs_defaultClientScript">
                    <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
                    <!-- <summary>
                          |||||      Style Sheet |||||
                          </summary>
                  --><link title="standard" href="Styles.css" type="text/css" rel="stylesheet">
              </head>
              <body>
                    <!-- |||||      Login Form      ||||| -->
                    <form id="frmlogin" method="post" runat="server">
                        <table id="mainTable">
                            <tr>
                                  <td>
                                        <table class="t_border" id="loginTable" cellspacing="15" cellpadding="0">
                                            <tr>
                                <td><b>Login: </b>
                                 </td>
                                <td><asp:textbox id="txtUserName" runat="server" width="160px"></asp:textbox><asp:requiredfieldvalidator id="rvUserValidator" runat="server" display="None" errormessage="You must supply a Username!"
                                       controltovalidate="txtUserName"></asp:requiredfieldvalidator></td>
                                            </tr>
                                            <tr>
                                <td><b>Password: </b>
                                 </td>
                                <td><asp:textbox id="txtPassword" runat="server" width="160px" textmode="Password"></asp:textbox><asp:requiredfieldvalidator id="rvPasswordValidator" runat="server" display="None" errormessage="Empty Passwords not accepted"
                                       controltovalidate="txtPassword"></asp:requiredfieldvalidator></td>
                                            </tr>
                                            <tr>
                                      <td align="center" colspan="2"><asp:button id="cmdSubmit" runat="server" borderstyle="Solid" text="Submit"></asp:button></td>
                                            </tr>
                                        </table>
                                  </td>
                            </tr>
                            <tr>
                                  <td>
                                        <table id="messageDisplay">
                                            <tr>
                                <td><asp:validationsummary id="Validationsummary1" runat="server" width="472px" displaymode="BulletList"></asp:validationsummary></td>
                                            </tr>
                                        </table>
                                  </td>
                            </tr>
                          </table>
                    </form>
                  <asp:label id="lblMessage" runat="server" width="288px" forecolor="#C00000" font-size="Medium"
                        font-italic="True" font-bold="True"></asp:label>
                  <!--      |||||    End of Form      |||||    -->
              </body>
        </html>
       
       

As you can see by the above code I require my users to supply a Username and Password, the requirefieldvalidator object does just that.

2.Code for the communication to the database:
a.Add the following line of code to the Web.Config file
- start right after
<?xml version="1.0" encoding="utf-8"?>
<configuration>
        <!--    |||||      Application Settings    |||||      -->
          <appSettings>
              <add key="strConn" value="Provider = Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\Northwind.mdb;User ID=Admin;Password=;"/>
          </appSettings>
          <system.web>
         
These lines of code do several things. 1. Eliminates the need for creation of a connection string (strConn) each time you want to connect to the Database(DB). Who wants to write that out each time. Now you have "pseudo-global" variable for use in your entire application. 2. Makes it secure, so no user can see where the DB is actually located. 3. And most importantly tells the application the location of the said DB.

b.Code the "meat" of the Login in page.
- Mine is in the code behind rather than a script block, but the principles are the same.
- Add the following Imports to your code behind, just above the class declaration.
Imports System.Web.Security '   |||||   Required Class for Authentication
        Imports System.Data '   |||||   DB Accessing Import
        Imports System.Data.OleDb   '   ||||||  Access Database Required Import!
        Imports System.Configuration    '   ||||||  Required for Web.Config appSettings |||||
       
This will provide the library imports you need for Authentication, accessing an OleDB (i.e. Access), and accessing the web.config file (contains your connection string)

c. Create a Function to connect to the DB return result(s)
- This is the main function of this entire webform.
        Function DBConnection(ByVal strUserName As String, ByVal strPassword As String) As Boolean
                    [color=#008000]'<sumamry>
                    '   |||||   Declare Required Variables
                ' ||||| Access appSettings of Web.Config for Connection String (Constant)
                    '</summary>
             ' ||||| First is the Connection Object for an Access DB
                    Dim MyConn As OleDbConnection = New OleDbConnection(ConfigurationSettings.AppSettings("strConn"))
                    '   |||||   This is the Connections Object for an SQL DB
                    SqlConnection(ConfigurationSettings.AppSettings("strConn"))
       
                    '<sumamry>
                    '   |||||   Create a OleDb Command Object
                    '   |||||   Pass in Stored procedure
                    '   |||||   Set CommandType to Stored Procedure
                    '</summary>
       
                 ' ||||| To Access a Stored Procedure in Access - Requires a Command Object
                    Dim MyCmd As New OleDbCommand("sp_ValidateUser", MyConn)
                    '   |||||   To Access a Stored Procedure in SQL Server - Requires a Command Object
                
                    MyCmd.CommandType = CommandType.StoredProcedure
                    '   |||||   Create Parameter Objects for values passed in
                    Dim objParam1, objParam2 As OleDbParameter
                    '<sumamry>
                    '   |||||   Add the parameters to the parameters collection of the
               ' ||||| command object, and set their datatypes (OleDbType in this case)
                    '</summary>
                    objParam1 = MyCmd.Parameters.Add("@UserName", OleDbType.Char)
                    objParam2 = MyCmd.Parameters.Add("@Password", OleDbType.Char)
       
                    ''   |||||   Set the direction of the parameters...input, output, etc
                    objParam1.Direction = ParameterDirection.Input
                    objParam2.Direction = ParameterDirection.Input
                    ''   |||||   Set the value(s) of the parameters to the passed in values
                    objParam1.Value = strUserName
                    objParam2.Value = strPassword
       
                    '   |||||   Try, catch block!
                    Try
                        '   |||||   Check if Connection to DB is already open, if not, then open a connection
                        If MyConn.State = ConnectionState.Closed Then
                              '   |||||   DB not already Open...so open it
                                MyConn.Open()
                          End If
       
                        '   |||||   Create OleDb Data Reader
                        Dim objReader As OleDbDataReader
                        objReader = MyCmd.ExecuteReader(CommandBehavior.CloseConnection)
                        '   |||||   Close the Reader and the Connection Closes with it
       
                          While objReader.Read()
                              If CStr(objReader.GetValue(0)) <> "1" Then
                                    lblMessage.Text = "Invalid Login!"
                                Else
                                    objReader.Close()   '   |||||   Close the Connections & Reader
                                    Return True
                                End If
                          End While
                    Catch ex As Exception
                  lblMessage.Text = "Error Connecting to Database!"
                    End Try
       
       
              End Function
              

d. Code the button click event for submitting login to DB
- The code for the onClick event of the Submit button is as follows, but this is very basic and simple. I will be expanding on this further with things like have a maximum number of attempts, and what happens when a user tries to access a page in the application without having logged in; it should redirect the user to the login page/form, otherwise what is the purpose of the login form.
              Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
                    If Page.IsValid Then    '   ||||| Meaning the Control Validation was successful!
                        '   |||||   Connect to Database for User Validation |||||
               If DBConnection(txtUserName.Text.Trim(), txtPassword.Text.Trim()) Then
               FormsAuthentication.RedirectFromLoginPage(txtUserName.Text, False)  '   |||||   default.aspx Page!
                          Else
               Else
                               '   |||||   Credentials are Invalid
                               lblMessage.Text = "Invalid Login!"
                               '   |||||   Increment the LoginCount (attempts)
                               'Session("LoginCount") = CInt(Session("LoginCount")) + 1
                               ' ||||| Determine the Number of Tries
                               'If Session("LoginCount").Equals(intMaxLoginAttempts) Then
                               '      Response.Redirect("Denied.aspx")
                               'End If
              
                               'If CInt(Session("Num_of_Tries")) > 2 Then ' ||||| If Exceeds then Deny!
                               '      Response.Redirect("Denied.aspx")
                               'End If
              
                                 End If
                           End If
              End Sub
       


3. Create the page to send the user to once login is successful
- In Visual Studio.Net go to File -> Add New Item -> Webform
- Name it default.aspx
- For ease at this time, just put a text message saying something like "Successful Login".
- It is this page that ASP.Net will automatically look for once login is successful, so if you do not create it you will get an application error.

4. Compile and run your code!

That is the end of this basic outline of a Login Page using ASP.Net. There were some moderate level of difficulty coding, but I believe my comments inline with the code should clarify any questions you may have. If by chance you have some questions, please post them here.

Please DO NOT POST replies to this thread that say things like - "Mine doesn't work!" without providing details of what errors you got, what does happen, etc.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now