Wednesday, December 5, 2012

Forms Authentication with MS Access Database

I recently had a requirement to provide Forms Authentication to a web application which has a Microsoft Access database. If you are thinking that Forms authentication only supports with Microsoft SQL Server databases, No it’s not. ASP.NET supports variety of custom Membership and Roles providers including,
  • SQL Database Support for ASP.NET Membership, Roles and Personalization
  • Access Database Support for ASP.NET Membership, Roles and Personalization
  • MySQL Support for ASP.NET Membership and Roles
  • SQLLite3 Support for ASP.NET Membership and Roles
  • Oracle Support for ASP.NET Membership, Roles and Personalization
Today I am going to show you how to configure Forms Authentication with Microsoft Access. If you have used Forms Authentication with Microsoft SQL Server, you know that we run following command to create “aspnetdb” database in the SQL Server first.

Create aspnetdb in SQL Server
But since here we are using MS Access database, we should have some kind of a access database similar to “aspnetdb”. So for that Microsft has provided us a MS Access .mdb file and some set of classes to implement Forms Authentication with the provided MS Access database file. So let’s see how this works. (I am hoping to upload a sample project to SkyDrive, so you can get all the database files and class files.)

First I will create a new web application and I will add a new class library to the solution.

Solution Explorer : Add Class Library Project
In the class library I will add a folder called “AccessProviders” and I will add the Microsoft provided class files to that folder. Then I am building “MyAccessProvides” class library and to compile I need to add following references to the class library project.
  • System.Web
  • System.Web.ApplicationServices
  • System.configuration
Solution Explorer : AccessProviders
Now I am moving to my Web Application project and in that project I am adding a reference to my class library project. Then to the “App_Data” folder, I am adding the MS Access .mdb file.

Add Project Reference
Solution Explorer : App_Data Folder
Then I am modifying the web.config file as follows.

I am adding new a connection string which will point to the Access .mdb file.
  <add name="AccessFileName" connectionString="~/App_Data/ASPNetDB.mdb" providerName="System.Data.OleDb"/>

Then I am further modifying the web.config file as follows.
<membership defaultProvider="AccessMembershipProvider" >
    <add name="AccessMembershipProvider"
        connectionStringName="AccessFileName" requiresQuestionAndAnswer="false"/>

<profile enabled="true" defaultProvider="AccessProfileProvider">
    <add name="AccessProfileProvider" type="MyAccessProviders.AccessProviders.AccessProfileProvider,MyAccessProviders"
        connectionStringName="AccessFileName" />

<roleManager enabled="true" defaultProvider="AccessRoleProvider">
    <add name="AccessRoleProvider"
        connectionStringName="AccessFileName" />

Now that’s it. A can create a new account and I can see it in the “ASPNetDB.mdb”.

Create New Account
Logged In
User created in the database
I have uploaded a sample of all the class files and the database to SkyDrive. Do check it out and hope this helps.
     Download the Sample

Happy Coding.