Forms Authentication Using SQL - Part 1
Published: 06 Oct 2003
Unedited - Community Contributed
This article provides some basic code to set up forms authentication within your web. Tested: ASP.NET 1.0 Included: Examples
by Jason N. Gaylord
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 30759/ 88


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.

User Comments

Title: c# code   
Name: raj
Date: 2011-05-25 7:51:10 AM
Hi I want the code in C#, can u please send it?
Title: login authentication in   
Name: khyati
Date: 2009-07-13 1:09:45 AM
very good!
Title: Good stuff   
Name: jes
Date: 2007-04-16 3:31:45 AM
I got an error in this line
intResult = myCmd.Parameters( "RETURN_VALUE" ).Value
when i converted the code to c#.error is
intResult = myCmd.Parameters( "RETURN_VALUE" ).Value

how do i solve this
Title: Nice done but...   
Name: Edgar
Date: 2006-09-12 4:25:17 PM
I just have a little question. What if i want to know the username of the user in other wepages???.
Title: good   
Date: 2006-03-27 5:40:52 AM
easy to understand . lot of thanks
Title: Woooow   
Name: Eryk
Date: 2006-02-02 4:50:17 PM
Very Very Good.
Title: very good   
Name: Tuncay
Date: 2006-01-14 2:20:40 AM
thanks, this tutorial is very good.
Title: RE:   
Name: Bryan
Date: 2005-11-15 3:46:14 PM
Convert sqlCommand2.Parameters["RETURN_VALUE"].Value to a integer
Title: a question!   
Name: mohammad
Date: 2005-11-12 10:15:36 AM
hello ,thanx for this grat code ,but i have a prob!!
i have converted this code to C# and every thing is fine exept this statment
ntResult = sqlCommand2.Parameters["RETURN_VALUE"].Value;
it say can nit implicit convert from in to object ,
so can u help me in doning it in c#?
Title: excellent   
Name: jh
Date: 2005-10-03 5:21:58 PM
very nice work - thanks much!
Title: need an import though   
Name: jh
Date: 2005-10-03 5:14:52 PM
it neededs this at the top of the page though:

Imports System.Web.Security
Title: Perfect   
Name: Jacques
Date: 2005-07-29 4:49:45 AM
Thank you. Just what i needed.
Title: WellDone   
Name: Man
Date: 2005-06-16 10:29:39 PM
This was very well done and cover a whole lit of stuff that I actually was after!

Title: Gant   
Name: Gant Man
Date: 2005-05-28 11:30:31 PM

Just what I was after
Title: Good   
Name: Majun
Date: 2005-02-10 2:56:41 AM
Very Good.

Product Spotlight
Product Spotlight 

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2018  |  Page Processed at 2018-04-20 3:49:05 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search