Forms Authentication Against SQLServer

Forms Authentication Against SQLServer...

In this article I'll cover how we can extend the inbuilt facilites of ASP.NET a little to provide authentication against a SQLServer database. Forms authentication is the method we shall focus on, in particular integrating a SQLServer authority.

By: Chris Sully

Date: July 28, 2003

Download the code.

Introduction

A DotNetJohn reader recently expressed an interest in an article concerned with how one might proceed with performing forms authentication against SQLServer. Here it is! If you have any suggestions for topics you'd like to see covered within this site let webmaster@dotnetjohn.com know.

One of my earlier articles for DotNetJohn was entitled Securing an ASP.NET Application � this offered an overview of the facilities ASP.NET and .NET provides for securing applications and introduced a (reasonably) secure login facility utilizing the inbuilt features of ASP.NET. I suggest you review this article if you haven't done so previously, although this is not necessary for the present article to be useful. In this article I'll cover how we can extend the inbuilt facilities a little to provide authentication against a SQLServer database. Forms authentication is the method we shall focus on, in particular integrating a SQLServer authority.

An important point to remember is that Forms based authentication and subsequent authorisation via web.config secures only ASP.NET resources. It doesn't protect HTML files, for example. Just because you have secured a directory using ASP.NET doesn't mean you have secured all files in that directory. To do this you could look at features available via IIS, amongst others. I may return to this subject in a future aritcle.

Forms Authentication

As introduced in my previous article, this is cookie-based authentication by another name and with a nice wrapper of functionality around it provided by ASP.NET. Such authentication is commonly deemed sufficient for large, public Internet sites. Forms authentication works by redirecting unauthenticated requests to a login page (typically username and a password are collected) via which the credentials of the user are collected and validated. If validated a cookie is issued which contains information subsequently used by ASP.NET to identify the user. The longevity of the cookie may be controlled: for example you may specify that the cookie is valid only for the duration of the current user session.

Forms authentication is flexible in the authorities against which it can validate, from an XML file to a SQLServer database. It is the latter which is our focus of interest in this article. If we select SQLServer as our data source for authentication the developer needs then to write bespoke code for validating user credentials against the database.

Typically you will then have a registration page to allow users to register their login details which will then be stored in SQLServer for use when the user then returns to a protected resource and is redirected to the login page by the forms authentication, assuming the corresponding cookie is not still in existence.

Let's introduce some code.

Forms Authentication using SQLServer

So, the authentication provider for forms authentication can be anything: a text file, Microsoft Active Directory, Microsoft SQLServer, etc. Because of its performance, scalability and reliability SQLServer is one of the most popular datastores under Windows. SQLServer can cope with web sites having millions of users � not true of the other data stores.

The developer needs to write the bespoke code for validating the users credential against SQLServer and we shall now develop a simple SQLServer-supported, forms authentication system.

First we need the database where we shall store the user related data. We shall need one table, here logins, and I'm going to create this with a database called 'DotNetJohn', feel free to name as you see fit and create the logins table in your preferred manner, for example by executing the following TSQL against your database:

CREATE TABLE [dbo].[logins] (
  [UserID] [int] IDENTITY (1, 1) NOT NULL ,
  [EmailAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  [Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

As commonly employed we shall use the email address as a 'username'.

Next we are going to need a stored procedure to validate entered user details against this table.

CREATE PROCEDURE USP_ValidateUser
  @email varchar(50),
  @password varchar(50),
  @loginID int = 0 OUTPUT
AS
  SELECT @loginID = UserId from logins
  WHERE EmailAddress=@email and Password = @password

if @@ROWCOUNT < 1
  SELECT @loginID = 0

Typically you will have a facility for a user to register (e.g. register.aspx) with the site linked from the login page and elsewhere. You would need to implement a stored procedure to insert the corresponding data into the logins table executed within register.aspx but I'll leave both the creation of the stored procedure and the register.aspx web form as an exercise for the reader.

We'll therefore need some data in the logins table to validate against if I'm not going to introduce an automated procedure. By your preferred means enter some login data into the database, i.e. an email address and password to use for testing.

Now we need the code in our login page (login_sqlserver.aspx) to utilize forms authentication and call the USP_ValidateUser stored procedure via ADO.NET as our bespoke piece of authentication code.

Note that you'll also need to create a new SQLServer login and give this login access to the database and execute access to the stored procedure. This login will be used to connect users to the database in login_sqlserver.aspx, as you can see from the code below.

login_sqlserver.aspx:

<%@ Page Language="VB" Trace="false" Debug="false" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<%@ import Namespace="System.Web.Security " %>
<%@ import Namespace="System.IO" %>

<script runat="server">

  Private Sub Login_Click(sender As Object, E As EventArgs)

    dim ConnString as string ="server=localhost;database=dotnetjohn;uid=john;pwd=dotnet;"

    dim conn as SQLConnection = new SQLConnection(ConnString)
    dim cmd as SQLCommand = new SQLCommand("USP_ValidateUser", conn)
    cmd.CommandType = CommandType.StoredProcedure

    dim paramEmail as new SQLParameter("@email", SqlDbType.VarChar, 50)
    paramEmail.value=UserEmail.value
    cmd.Parameters.Add(paramEmail)

    dim paramPassword as new SQLParameter("@password", SqlDbType.VarChar, 50)
    paramPassword.value=UserPass.value
    cmd.Parameters.Add(paramPassword)

    dim paramUserID as new SqlParameter("@LoginID", SqlDbType.Int, 4)
    paramUserID.Direction = parameterDirection.Output
    cmd.Parameters.Add(paramUserID)

    conn.open()
    cmd.ExecuteScalar()

    trace.write(paramUserID.value)

    If (paramUserID.value>0) Then
      FormsAuthentication.RedirectFromLoginPage(UserEmail.value, false)
    else
      lblMsg.Text = "Invalid credentials: Please try again"
    End If
  End Sub

</script>

<html>
<head>
<title>Forms/ SQLServer Authentication</title>
</head>
<body>
<form runat="server">
<h3>Login Page
</h3>
<table>
  <tbody>
    <tr>
      <td>Email:</td>
      <td><input id="UserEmail" type="text" runat="server" /></td>
      <td>
        <ASP:RequiredFieldValidator id="RequiredFieldValidator1" runat="server" ControlToValidate="UserEmail" Display="Static" </ASP:RequiredFieldValidator>
      </td>
    </tr>
    <tr>
      <td>Password:</td>
      <td><input id="UserPass" type="password" runat="server" /></td>
      <td>
        <ASP:RequiredFieldValidator id="RequiredFieldValidator2" runat="server" ControlToValidate="UserPass" Display="Static" </ASP:RequiredFieldValidator>
      </td>
    </tr>
  </tbody>
</table>
<input type="submit" value="Login" runat="server" onserverclick="Login_Click" />
<p>
</p>
<asp:Label id="lblMsg" runat="server" forecolor="red"></asp:Label>
</form>
</body>
</html>

The Configuration File

Below is presented a web.config file suitable for use with the information and code thus far presented:

Thus we deny unauthenticated users access to any of the applications .aspx files except register.aspx because we want all users to be able to register to use our application. As a bonus segment of functionality we also introduce an administration file admin.aspx, which might, for example, list all registered users e-mail addresses. As we do not want this information freely available only the designated admin user is permitted access.

To test the security application create a simple .aspx page within the application and view it in a web browser. You should be redirected to the login page, returning to the original page when you have inputted the correct details.

Conclusion

I hope this article has provided a useful introduction as to how to go about authenticating against SQLServer via ASP.NET.

We're happy to consider suggestions for future articles so let dotnetjohn.com have your ideas.

References

ASP.NET: Tips, Tutorial and Code
Scott Mitchell et al.
Sams

Developing and implementing web applications with VB.Net and VS.Net
Mike Gunderloy
Que

You may download the code here.