Article C0012 C# .NET
SQL Server as Authentication Provider

This article describes how to use Microsoft SQL Server as authentication provider for your ASP.NET applications.

The ingredients I have used are the following:
    SQL Server 2008 Express Edition
    Microsoft Internet Information Services 7.0
    Visual Studio 2008

First prepare SQL Server to maintain .NET User account store. This command is available within the Microsoft .NET Framework 2.0 folder:

Prepare SQL Server for User Accounts
SystemFolderCommand
x86%windir%\Microsoft.NET\Framework\v2.0.50727aspnet_regsql -S .\SQLExpress -E -A m
x64%windir%\Microsoft.NET\Framework64\v2.0.50727aspnet_regsql -S .\SQLExpress -E -A m

Although not used within this article, the next table shows how to prepare the database for using .NET Roles as well:

Prepare SQL Server for Roles
SystemFolderCommand
x86%windir%\Microsoft.NET\Framework\v2.0.50727aspnet_regsql -S .\SQLExpress -E -A r
x64%windir%\Microsoft.NET\Framework64\v2.0.50727aspnet_regsql -S .\SQLExpress -E -A r


After running these commands the database named ‘aspnetdb’ will be available. Within this database the user store command has created the following tables:
    dbo.aspnet_Applications
    dbo.aspnet_Membership
    dbo.aspnet_Roles
    dbo.aspnet_SchemaVersions
    dbo.aspnet_Users
    dbo.aspnet_UsersInRoles

Now launch SQL Server Managemetn Studio (SMSS) and open the Security container. Within the Security container select the the Logins container. Use the context menu to create a new login. Create it as a backward compatible login account. Set the User Mapping of this new SQL Server authentication account towards the ‘aspnetdb’-database with [dbo] as its default schema.

User Mapping
Next assign access towards the newly created tables found within the ‘aspnetdb’-database.

ASP.NET tables

Examine the permissions of the ‘aspnetdb’-database. The ‘authaccess’-account is granted the Connect permission, which is sufficient for our purpose.

Connect permission

The newly created account will be used within the connection string which our application requires to communicate with the database.

Now that the database is prepared, create an Application Virtual Directory using the Internet Information Services management console:

IIS Manager context menu
In this case we are going to use Visual Studio 2008 and create a Web Application using the ‘ASP.NET Web Application’-template. Prior being able to handle Forms authentication using SQL Server as provider and the Internet Information Services management console as account manager, the ‘web.config’-file must be modified.

The <connectionStrings />-tag must be replaced with one our application is going to use to connect to the database:

<connectionStrings>
    <add name="SqlConnection" connectionString="Server=.\SQLExpress;
        Database=aspnetdb; Uid=authaccess;Pwd=authaccess;" />
</connectionStrings>
In this scenario both the SQL Server accountname and password are ‘authaccess’. In this case the application and database are on the same server. If the database is on another server change the dot into the correct server name, like ‘dbserver.home.edu\SQLExpress’. If the instance name is different, simply remove SQLExpress and pencil down the correct instance name.

Since the page is going to use Forms authentication, the correct module must be added within the <httpModules>-section of the ‘web.config’-file:

<add name="FormsAuthentication"
    type="System.Web.Security.FormsAuthenticationModule" />
The default configuration of the .NET Framework is equipped with the ‘AspNetSqlRoleProvider’-provider which uses the ‘LocalSqlServer’-connectstring. Using this provider will result with an ‘aspnetdb.mdf’-file place within the ‘App_Data’-folder of the application.

Since we are going to implement our central database as provider, we have to implement the provider and make it leading. This can be done by adding the following section within the ‘web.config’ <system.web>-section:

<membership defaultProvider="SqlProvider" userIsOnlineTimeWindow="15">
    <providers>
        <clear />
        <add
          name="SqlProvider"
          type="System.Web.Security.SqlMembershipProvider,
			System.Web, Version=2.0.3600.0, Culture=neutral,
			PublicKeyToken=b03f5f7f11d50a3a"
          connectionStringName="SqlConnection"
          applicationName="Former"
          enablePasswordRetrieval="false"
          enablePasswordReset="true"
          requiresQuestionAndAnswer="true"
          requiresUniqueEmail="true"
          passwordFormat="Hashed" />
    </providers>
</membership>
This new provider is named SqlProvider and its connectionStringName should match the declaration of this value created in the ‘connectionStrings’-section. The PublicKeyToken must be set prior being able to trust this connection string within the IIS management console.

By default the Default.aspx page will be shown when the web-application is launched. By changing the web-application authentication mode into Forms, it is possible to use a Login page and make this leading for unauthenticated users. This can be done by adding the following authentication-section within the <system.web>-section:

<authentication mode="Forms">
      <forms loginUrl="Login.aspx"
             protection="All"
             timeout="30"
             name=".ASPXAUTH"
             path="/Former"
             requireSSL="false"
             slidingExpiration="true"
             defaultUrl="Default.aspx"
             cookieless="UseDeviceProfile"
             enableCrossAppRedirects="false" />
</authentication>
Although the authentication mode is set to Forms and the Login.aspx page is assigned as loginUrl, it is still possible to access pages by typing in their URI. We can force authorization by denying anonymous access. This can be done by adding the authorization-section within the <system.web>-section as shown here:

<authorization>
      <deny users="?" />
      <allow users="*" />
 </authorization>
 
The deny wildcard ‘?’ means deny anonymous users, while the next allow ‘*’ counts for everyone. This way access is allowed to all authorized users.

Next step within Visual Studio is to create the Login.aspx page. This can be simply done by creating a new ‘Web Form’ and dragging-and-dropping the ‘asp:Login’-control onto the newly created page. In the Source view it would look something similar like this:

<form id="frmLogin" runat="server">
<div>
<asp:Login ID="dlgLogin" runat="server"
    DestinationPageUrl="~/Default.aspx"
    MembershipProvider="SqlProvider">
</asp:Login>
</div>
</form>
Although the Login control will react on its own return query string (which was set in the authentication defaultUrl-property, I found out this the page is not always redirected toward this page after positive validation. That is way the DestinationPageUrl-property is filled with the ‘~/Default.aspx’-page. Simply to ensure that the page is moved away from the Login page after a successful logon has occurred.

It would be nice to add a label in the ‘Default.aspx’-page which will be loaded with the current logged on username. This can be done by adding a label within the Default.aspx page:

<asp:Label ID="lblName" runat="server" Text=""></asp:Label>
Which value is set in its code-behind page Default.aspx.cs:

lblName.Text = HttpContext.Current.User.Identity.Name;
Now publish the web-application towards IIS.

Since we are going to be authenticated using SQL Server, we first have to add an account into the identity store. This can simply be done by using the ‘.NET Users’-icon available when the Application is clicked within the Internet Information Services Manager:

IIS Application menu
Simply use the ‘.NET Users’-icon and the ‘Add .NET User’-dialog will be shown:

Add .NET User-dialog
The ‘.NET Users’-listview will show all available users found within the store:

.NET Users
After adding a user, this new users can be found within the ‘dbo.aspnet_Users’-table. This can be checked using the following SQL-query:

use aspnetdb
go
select * from dbo.aspnet_Users
Now start the web-application using Internet Explorer (or any other Internet browser) and enter the URL towards the new application. In our case this is the URL:

http://win2008std.test.edu/Former/
As soon as the webserver is processing the ‘web.config’-file, it will change the page address by adding a return-querystring:

http://win2008std.test.edu/Former/Login.aspx?ReturnUrl=%2fFormer%2fdefault.aspx
Furthermore, it doesn't show the Default.aspx but the Login.aspx page. This page will show the ‘Log In’-dailog:
Login-dialog

When items are not typed in a red * will appear behind the textbox. By entering invalid credentials the following message will appear:

Your login attempt was not successful. Please try again.

But when entering the credentials as added within the IIS Manager, the default page will be shown with the following text:

    Identity:
    vincent
Looking at the authentication flow, it will run like this:

Authentication flow
Logging Out
Now that we are logged in, let's get logged out.

With traditional Forms authentication it is possible to logout through the use of the FormsAuthentication.SignOut() method. This method must be embedded within the .ASPX page, not the code-behind file. For example, add an OnClick event toward a button like this:

<asp:Button ID="btnLogOut" runat="server" Text="LogOut" 
    onclick="btnLogOut_Click" />
Next, within the same page, add a script handling the OnClick event like this:

<script runat="server">
public void btnLogOut_Click(object sender, EventArgs args)
{
    FormsAuthentication.SignOut();
    FormsAuthentication.RedirectToLoginPage();
}
</script>
After implementing and using this code, it looks as if it’s working properly. But when pressing the Internet Explorers-back button after logging out still provides access as if the user is still logged in. So the traditional Forms manner is not the right choice here.

Within the application we have used the ‘asp:Login’-component, so we can also use the ‘asp:LoginStatus’-component. The component can be found within the toolbox or can be added manually within the .ASPX page like this:

<asp:LoginStatus ID="LoginStatus" runat="server" />
The control will appear as a link showing the state of being logged in or not. When this in the state of being logged in, the control will say so. Now, when the user presses the control, it logs out the user. Pressing Internet Explorer’s-back button will not help and the login page will reappear.

You can download the source from this article right here.

~Edward