LogoASPAlliance: Articles, reviews, and samples for .NET Developers
Forms Authentication Using SQL - Part 1
by Jason N. Gaylord
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 30575/ 92

Have you ever wondered how sites authenticate users using forms? This article will explain how to by using ASP.Net and SQL 2000.

First, you need to prepare your computer to run the authentication. Be sure you already have IIS installed and running on a Windows 2000 (or better) computer or server. You also need to install the .Net Framework which is a free download available from Microsoft. After the .Net Framework is installed, you must configure IIS to run the framework properly. You can do so by going to IIS and setting the default user to ASPNET. More experienced users might want to edit the config files on the host to add additional users or permissions.

Secondly, you need to establish a database in SQL. If you do not have SQL, the SQL desktop engine (MSDE) is available as a free download from Microsoft. You can download this engine by going to and downloading the ASP.Net Community built Web Matrix. This will include steps to downloading MSDE. Once you have downloaded and installed your MSDE, you must setup a database.
Section 1: Setting up SQL
At this point, all of the necessary work has been done to establish the base of your project. Then create a table and procedure to hold and query users and passwords. For this example, I used the following:

1:    CREATE TABLE [dbo].[User] (
2:    username char(25),
3:    password char(25),
4:    )
7:    CREATE PROCEDURE MyAuthentication
9:    (
10:   @username Char( 25 ),
11:   @password Char( 25 )
12:   )
13:   As
15:   DECLARE @actualPassword Char( 25 )
17:   SELECT
18:   @actualPassword = Password
19:   FROM [Users]
20:   Where username = @username
22:   IF @actualPassword IS NOT NULL
23:   IF @password = @actualPassword
24:   RETURN 1
25:   ELSE
26:   RETURN -2
27:   ELSE
28:   RETURN -1
29:   GO

The table called users holds the username and passwords of visitors. The procedure called MyAuthentication is the gateway to the table users. It allows 2 parameters to be passed, username and password. The procedure then queries the table for entries where the passed username (@username) is equal to the username in the table (username). The If..Else statement works similar to any other programming if..else statement. If no results are returned from the select statement, the procedure will return a -1. If results are returned, the next If..Else statement is executed. This statement checks to see if the passed password (@password) is equal to the actual password in the table (@actualPassword). If it is, 1 is returned. Otherwise, a -2 will be returned.
Section 2: Setting Up The Web.Config File
Now that the SQL side is setup, we can begin programming the ASP.Net pages. We will begin by configuring our web application. The configuration is stored in a file called web.config. A sample of the configuration is shown below:

1:    <configuration>
4:       <!-- This section stores your SQL configuration as an appsetting called 
5:           conn. You can name this setting anything you would like. 
6:       -->
7:       <appSettings>
8:          <add key="conn" _ 
9:          value="server=localhost;database=MySite;uid=sa;pwd=password;" />
10:      </appSettings>
13:      <!-- This section sets the authentication mode to forms authentication 
14:          and routes all traffic to the specified page. It also specifies a 
15:          timeout. The authorization section below denies all users not 
16:          authenticated. For testing purposes, custom errors was turned off. 
17:          The section below allows pages to be trace enabled for debugging 
18:      -->
19:      <system.web>
21:         <authentication mode="Forms">
22:            <forms name="MyFormsAuthentication" loginUrl="login.aspx" _ 
23:            protection="All" timeout="720" />
24:         </authentication>
26:         <authorization>
27:            <deny users="?" />
28:         </authorization>
30:         <customErrors mode="Off" />
32:         <trace enabled="true" requestLimit="0" pageOutput="true" />
34:       </system.web>
37:   </configuration>

This will setup the basic web.config file that we will need.
Section 3: Creating The ASP.NET Page
The login ASP.Net page will include two sections, a code block and an html block. For my example, I used the following login page:

1:    <%@ Import Namespace="System.Data" %>
2:    <%@ Import Namespace="System.Data.SqlClient" %>
4:    <Script Runat="Server">
6:       Sub Login_Click( s As Object, e As EventArgs )
7:          If IsValid Then
8:             If MyAuthentication(txtUsername.Text,txtPassword.Text) > 0 Then 
9:                 FormsAuthentication.RedirectFromLoginPage (txtUsername.Text,False) 
10:            End If 
11:         End If 
12:      End Sub
14:      Function MyAuthentication(strUsername As String, _ 
15:        strPassword As String) As Integer
17:         ' Variable Declaration
18:         Dim myConn As SQLConnection
19:         Dim myCmd As SQLCommand
20:         Dim myReturn As SQLParameter
21:         Dim intResult As Integer
22:         Dim conn As String
24:         ' Set conn equal to the conn. string we setup in the web.config
25:         conn = ConfigurationSettings.AppSettings("conn")
26:         myConn = New SQLConnection(conn)
28:         ' We are going to use the stored procedure setup earlier
29:         myCmd = New SQLCommand("MyAuthentication",myConn)
30:         myCmd.CommandType = CommandType.StoredProcedure
32:         ' Set the default return parameter
33:         myReturn = myCmd.Parameters.Add("RETURN_VALUE",SqlDbType.Int)
34:         myReturn.Direction = ParameterDirection.ReturnValue
36:         ' Add SQL Parameters
37:         myCmd.Parameters.Add("@username",strUsername)
38:         myCmd.Parameters.Add("@password",strPassword)
40:         ' Open SQL and Execute the query
41:         ' Then set intResult equal to the default return parameter
42:         ' Close the SQL connection
43:         myConn.Open()
44:            myCmd.ExecuteNonQuery()
45:            intResult = myCmd.Parameters( "RETURN_VALUE" ).Value
46:      myConn.Close()
48:         ' If..then..else to check the userid.
49:         ' If the intResult is less than 0 then there is an error
50:         If intResult < 0 Then
51:            If intResult = -1 Then
52:               lblMessage.Text = "Username Not Registered!<br><br>"
53:            Else
54:               lblMessage.Text = "Invalid Password!<br><br>"
55:            End If
56:         End If
58:         ' Return the userid
59:         Return intResult
61:      End Function
63:   </Script>
65:   <html>
67:   <head>
68:      <title>Authentication Sample</title>
69:   </head>
71:   <body>
72:      <form Runat="Server">
73:         <asp:table runat="Server" HorizontalAlign="Center">
74:            <asp:tablerow>
75:               <asp:tablecell ColumnSpan="2">
76:                  <h2>Please Login:</h2>
77:                  <asp:label ID="lblMessage" ForeColor="Crimson" Font-Bold="True" 
                      Runat="Server" />
78:               </asp:tablecell>
79:            </asp:tablerow>
80:            <asp:tablerow>
81:               <asp:tablecell CssClass="FormText">
82:                  <b>Username:</b>   
83:               </asp:tablecell>
84:               <asp:tablecell CssClass="FormText">
85:                  <asp:TextBox ID="txtUsername" MaxLength="25" Runat="Server"
                      CssClass="FormElement" />
87:                  <asp:RequiredFieldValidator ControlToValidate="txtUsername"
                      Text="Required!" Runat="Server" />
88:               </asp:tablecell>
89:            </asp:tablerow>
90:            <asp:tablerow>
91:               <asp:tablecell CssClass="FormText">
92:                  <b>Password:</b>   
93:               </asp:tablecell>
94:               <asp:tablecell CssClass="FormText">
95:                  <asp:TextBox ID="txtPassword" TextMode="Password" 
                      MaxLength="25" Runat="Server" CssClass="FormElement" />
97:                  <asp:RequiredFieldValidator ControlToValidate="txtPassword"
                      Text="Required!" Runat="Server" />
98:               </asp:tablecell>
99:            </asp:tablerow>
100:            <asp:tablerow>
101:               <asp:tablecell ColumnSpan="2">
102:                  <br>
103:                  <asp:Button Text="Login!" OnClick="Login_Click" 
                      Runat="Server" />
104:               </asp:tablecell>
105:            </asp:tablerow>
106:         </asp:table>
107:      </form>
108:   </body>
110:   </html&gl;

The html contains a form with three basic elements: a username field, a password field, and a login button. RequiredFieldValidator controls were used to make sure the username and password fields were completed before submit. These controls use javascript templates found in a directory under your web root called aspnet_client. If you do not have this folder, you must copy and rename the folder called ASP.NETClientFiles under your C:\{Windows Root}\Microsoft.NET\Framework\{Framework Version} folder to the root of your web (usually c:\inetpub\wwwroot).

The server button contains an OnClick method that calls a subroutine called Login_Click. The Login_Click subroutine checks the validity of the form and then checks to be sure the authentication was validated. If it was, the form will redirect the user to the page that forwarded the request to the login.aspx page. If not, the user will remain on the page until the credentials are correct.

The authentication is checked using a function called MyAuthentication. This function calls the SQL settings from the web.config file. It then declares that a stored procedure will be used. Finally, input and return parameters are added to the procedure declaration. When the stored procedure is executed, the return value is checked for errors. Remember that our stored procedure returns a negative number for an incorrect username or password. The If..Else statement is setup to check this. If the credentials are incorrect, a message will be displayed on the screen. If they are correct, the function will return a positive value and continue in the subroutine.

Product Spotlight
Product Spotlight 

©Copyright 1998-2018  |  Page Processed at 2018-05-26 6:14:15 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search