Security models in ASP.NET. Authentication.
Author: Sergei Baklanov aspnetman@aspnetmania.com

Windows Authentication
Forms Authentication
Forms Authentication using XML file
Forms Authentication using web.config file
Forms Authentication using database
Forms Authentication with Web services
Passport Authentication

Forms authentication using database

Let's review the example of work with one more users data storage - MS SQL Server database. Majority of dynamic sites use databases to store site content. Users data are not exception and can take their place in common data pool. To see with your own eyes, how all this happens, let's develop test application which will be based also on the login page used in the previous examples. First of all, you need to prepare database, to do it, open SQL Query Analyzer utility and execute in it the following code written in tSQL language:

Let's review the example of work with one more users data storage - MS SQL Server database. Majority of dynamic sites use databases to store site content. Users data are not exception and can take their place in common data pool. To see with your own eyes, how all this happens, let's develop test application which will be based also on the login page used in the previous examples. First of all, you need to prepare database, to do it, open SQL Query Analyzer utility and execute in it the following code written in tSQL language:

--Create database �FormAuthUsers� and add table �Users�
CREATE DATABASE FormAuthUsers
GO
USE FormAuthUsers
GO
CREATE TABLE [Users] (
	[ID] [int] IDENTITY (1, 1) NOT NULL,
	[UserName] [nvarchar] (50),
	[Password] [nvarchar] (50),
	CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
	([ID]) ON [PRIMARY]
) ON [PRIMARY]

GO

--Fill table �Users�
INSERT INTO Users (UserName, Password)
VALUES(�John�, �one�)
GO
INSERT INTO Users (UserName, Password)
VALUES(�Mike�, �two�)
GO
INSERT INTO Users (UserName, Password)
VALUES(�Bill�, �three�)

GO

--Create procedure �FindUser�
CREATE PROCEDURE FindUser
	@Name nvarchar(50),
	@Password nvarchar(50)
AS
	SELECT COUNT(ID) FROM Users
	WHERE UserName = @Name AND Password = @Password

GO

After this code execution database named FormAuthUsers should be created on your SQL Server, you can find Users table containing three records and FindUser.protected procedure. This procedure returns the number of users that satisfy request which is formed of parameters, passed to this procedure.

Now, when our database is ready we can start creating our page that will work with it. To do it, basing on the previous examples replace button click event handler on login.aspx page:

Imports System.Data.SqlClient
Imports System.Web.Security

Public Class WebForm1
    Inherits System.Web.UI.Page

    Protected WithEvents txtName As System.Web.UI.WebControls.TextBox
    Protected WithEvents txtPassword As System.Web.UI.WebControls.TextBox
    Protected WithEvents lbl As System.Web.UI.WebControls.Label
    Protected WithEvents btnLogin As System.Web.UI.WebControls.Button

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub btnLogin_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnLogin.Click
        Dim cn As New SqlConnection("server=localhost;database=FormAuthUsers;uid=sa;pwd=;")
        Dim cm As New SqlCommand("FindUser", cn)
        Dim n As Integer

        ' Open connection
        Try
            cn.Open()
        Catch ex As SqlException
            Response.Write(ex.Message)
            Exit Sub
        End Try

        ' Set command type
        cm.CommandType = CommandType.StoredProcedure

        ' Add name parameters
        Dim prmName = New SqlParameter("@Name", SqlDbType.NvarChar, 50)
        prmName.Value = txtName.Text
        cm.Parameters.Add(prmName)
        ' Add password parameter
        Dim prmPass = New SqlParameter("@Password", SqlDbType.NvarChar, 50)
        prmPass.Value = txtPassword.Text
        cm.Parameters.Add(prmPass)

        ' Execute request(query)
        n = cm.ExecuteScalar

        If n > 0 Then
            ' If we found somebody, it means, registration was successful
            FormsAuthentication.RedirectFromLoginPage(txtName.Text, False)
        Else
            ' if no, then error 
            lbl.Visible = True
        End If

        cn.Close()
    End Sub
End Class

For this page to work correctly, make sure, that connection string matches your configuration. Let's sort out what happens here. First, SqlConnection connection object is created, which accepts database connection string as a parameter:

Dim cn As New SqlConnection(�server=localhost;database=FormAuthUsers;uid=sa;pwd=;�)

Afterwards, instance of SqlCommand object is created, which serves to execute data management commands. Following lines of the code open connection, but possible exceptions that are caught by Try exceptions handler, are taken into account.

' Open connection
Try
    cn.Open()
Catch ex As SqlException
    Response.Write(ex.Message)
    Exit Sub
End Try

If any errors occur while opening connection, user will get appropriate message and operation will be terminated.

After it, SqlCommand object is set to execute protected procedure, and parameters to start this procedure are being prepared. Of course, for this 7 code line there is an alternative consisted of 1 code line:

cm.CommandText = "exec FindUser '" & txtName.Text & "', '" & txtPassword.Text & "'"

But in spite of the fact that this code is shorter, run-time will be longer. Why? Because with this line you are executing operative request, i. e. you are writing SQL-command which calls protected procedure. As a result, you send over the net additional data in form of "exec" command, which, in its turn, take its place in the net traffic; as soon as this request reaches server, "exec" command will be executed first, and only then FindUser protected procedure that we need, will be started. Apart from this, even more difficult way exists, the essence of which is the refuse from protected procedures and inserting all necessary request code directly into SqlCommand object, as a result we have even more additional information sent over the net traffic. You may think, what is plus-minus couple of dozens bytes, but if you have to work with huge requests with the code much longer than our procedure has? That is why, the best way is using protected procedures, because in this case you get clear division of responsibilities, when client page is responsible only for data input-output in the form available to the user; SQL Server executes all data management operations; and IIS acts as a mediator between client and server - you got three-level architecture. Don't overload delicate client machines with extra work - you better trust heavy artillery, i. e. server, to do it.

But let's go back, to our code and continue its discussion: after setting command type and preparing all parameters we successfully execute this command, please note that ExecuteScalar function is used while execution. It should be used when you get scalar value as a result, i. e. figure, and that is actually what is happening in our FindUser procedure - it returns the number of records that satisfy given condition:

SELECT COUNT(ID) FROM Users
WHERE UserName = @Name AND Password = @Password

Back to top