Using the Microsoft Access Providers to Replace the Built-In SQL Server Providers

Update 2011/1/9: I just published a short article that shows you how to use the Microsoft Access Providers in an ASP.NET 4 web site.

The biggest features brought by ASP.NET 2.0 are most likely the new services for membership, roles, personalization and profiles. These services supply you with a lot of functionality out-of-the-box with little to no custom code. The services that ship with .NET 2.0 all use SQL Server as the data store (either the Express edition, or the full commercial versions) by means of a provider. But what if you can't use SQL Server, for example because your hosting company doesn't support it? In that case, you can use the Microsoft Access Providers, a shared source initiative released by Microsoft that allows you to use a Microsoft Access database for all the provider based features.

This article shows you how to acquire, compile and use these Microsoft Access Providers.

Introduction

The greatest benefit of the services that ship with the .NET 2.0 Framework is their ease of use. Normally, enabling a feature such as user authentication and role management is as simple as dropping a Login control on your page.

However, all of this only works if you can use SQL Server, whether it's the free SQL Server 2005 Express Edition, or one of the full 2005 or 2000 editions. (For a description about configuring your application to work with the full SQL Server 2000 and 2005 editions, check out this post made by Scott Guthrie.) But what if you can't use SQL Server? What if you are using an ISP that only allows you to host a Microsoft Access database?

In that case, don't worry. You can use the Access Providers that have been made available by Microsoft. During early betas of the .NET 2.0 Framework, the Access Providers were part of the shipping framework so it was easy for everyone to target a Microsoft Access database. However, in beta 2 and the final (RTM) version, the Access providers were gone, leaving you with SQL Server only. Fortunately, Microsoft released the complete source code for the original Access Providers, enabling you to use an Access database while still making use of all the goodies that come with the ASP.NET 2.0 providers.

The remainder of this article focuses on downloading, compiling, configuring and using the Access Providers in your own ASP.NET 2.0 application. I'll be using Visual C# 2005 Express Edition and Visual Web Developer Express Edition in this article. These two programs can be downloaded for free from the Microsoft site so they are accessible to everyone. But of course if you have a full version of Visual Studio 2005, like the Standard or Professional edition, you can still follow along. In fact, you'll find that most things are identical to what I describe here.

Services Overview

ASP.NET 2.0 includes a number of services that are common to many web based applications. Examples are membership and role management (authentication and authorization), Profiles, Session State, and Personalization. In previous versions of ASP.NET, you often had to write your own code to enable these features, but ASP.NET 2.0 comes with a number of services that you can use out of the box. Figure 1 lists some of these services and gives an overview of how services, providers and data stores are related in ASP.NET 2.0 web applications.

An Overview of Some of the Services and Providers in ASP.NET 2.0
Figure 1: Services and Providers in ASP.NET 2.0

The second layer from the top lists two of the services currently found in the .NET 2.0 Framework: Membership and Profiles. The Other Services box represents the other built-in services and possibly extension services developed by third parties.

Each of the services that requires a persistent data store (e.g. a database, XML files and so on) doesn't talk directly with that data store, but instead talks to a provider. The third layer from the top lists a number of providers. The ones in italic (e.g. the bottom half) are custom providers that you can build or implement yourself. The others are built-in and ship with the RTM version of the .NET Framework. If you don't like the behavior of the existing providers, or they don't do exactly what you want them to do, it's pretty easy to swap a built-in provider with a custom one, like the Access Providers that are discussed in this article.

Provider Overview

Before we dig into making the Access Providers work in your site, let's briefly look at what a provider is. The Microsoft site has a nice definition:

A provider is a software module that provides a uniform interface between a service and a data source. Providers abstract physical storage media, in much the same way that device drivers abstract physical hardware devices. Because virtually all ASP.NET 2.0 state-management services are provider-based, storing session state or membership state in an Oracle database rather than a Microsoft SQL Server database is as simple as plugging in Oracle session state and membership providers. Code outside the provider layer needn't be modified, and a simple configuration change, accomplished declaratively through Web.config, connects the relevant services to the Oracle providers.

This may be a bit hard to digest at first, but it all comes down to the following simple idea: instead of performing certain actions directly from the code in your service (e.g. Membership, role management and so on), such as creating a user in a database, you delegate the actual responsibility of the action (creating the user) to a separate component. This way, you can easily replace the separate component with another version to perform more or less the same task but in a different way.

Let's look at an example. When you call the Membership.CreateUser method in your ASPX page, you end up with a user in your SQL Server database. This user is not created by the CreateUser method. Instead, all this method did was call a similar method on the configured provider that in turn created the user in the SQL Server database. The idea with the provider model is that you can easily reconfigure the application, so it uses a different provider that can create the user in an entirely different data store like an XML file, or, as presented in this article, in a Microsoft Access database.

A lot has been written about providers, so I won't dig any deeper into them in this article. Instead, take a look at the Provider Toolkit. In the toolkit, you'll find a number of useful articles, including an Introduction to the Provider Model and an article that "dives deep into the ASP.NET Providers". The latter article, while pretty technical is definitely worth a read; especially the complete PDF version.

Prerequisites

If you want to follow along with this article, you'll need a copy of Visual Studio 2005 (Standard, Professional, Team System) or a copy of Visual C# 2005 Express Edition. The latter is free and can be downloaded from the MSDN site. Make sure you download the C# version, and not the Visual Basic edition. The code for the Access providers is in C#, so to be able to compile the code you need a C# compiler.

Obtaining and Installing the Microsoft ASP.NET 2.0 Access Providers

You can download the Access providers from the Microsoft MSDN site at this location: http://download.microsoft.com/download/5/5/b/55bc291f-4316-4fd7-9269-dbf9edbaada8/SampleAccessProviders.vsi. This download, a Visual Studio Community Content Installer file installs a project template that is accessible in Visual Studio 2005 or Visual C# Express in the New Project dialog. To install it, simply double click it and follow the instructions. When you get a warning about unsigned code, simply click Yes to proceed. After installation, you'll find a ZIP file called ASP.NET Access Providers.zip with all the relevant files in your My Documents folder under Visual Studio 2005\Templates\ProjectTemplates\Visual Web Developer. You don't have to unpack this file; The format for project templates in Visual Studio 2005 is a zip file, so Visual Studio knows how to handle it.

With the template installed, the next step is to create a new project based on this template.

Opening, Compiling and Tweaking the Project

To create a new project with the Access Providers template, fire up Visual C# Express Edition (remember, you can also do this with the full version of Visual Studio 2005) and choose File | New Project.... In the Project types tree, make sure that Visual C# and Visual Web Developer are expanded and then click Starter Kits. In the Templates box on the right, choose ASP.NET Access Providers and then click OK:

The New Project Dialog in Visual C# Express Edition Showing the Access Providers Template
Figure 2: The New Project Dialog with the Access Providers template

After you click OK, a new project is created for you with all the files you need to build, configure and run an application with the Access Providers. Your Solution Explorer should look similar to this:

The Solution Explorer for the Access Providers Project
Figure 3: The Solution Explorer for the Access Providers Project

The solution contains a number of important files that are listed below:

Filename Description
web.config The file is not used in the Access Provider project itself. However, it contains all the settings you need to configure your ASP.NET 2.0 application to work with the provider. You'll see how this works in the section "Configuring the Access Providers for your Web Site" later in this article.
ASPNetDB.mdb This is the Microsoft Access counterpart of the SQL Server database ASPNETDB.mdf (notice the different extension) that is used in your application. When you use SQL Server 2005, the database is often created automatically for you. This is not the case for the Access Providers, so you need to use this database as the template for your own web site. Again, you'll see how this works later.
*.cs Four of these files contain the actual implementation for four provider based features: Membership, Roles, Personalization and Profiles. The other three files contain helper code that is used in the other files.


You don't need the files readme.txt and eula.rtf so after you read them, you can safely delete them from the project.

The next step you need to carry out is to give a new name to the assembly created by this project. If you chose all defaults when creating the new project, the .DLL file that is being created by this project will be called ASP.NET Access Providers1.dll. The configuration file expects the file to be called SampleAccessProviders.dll so you'll need to configure the project accordingly. To do this, right-click the project in the Solution Explorer and choose Properties (alternatively, choose ASP.NET Access Providers1 Properties from the main Project menu). On the Application tab, change the Assembly name to SampleAccessProviders:

The Project Properties Dialog with the changed Assembly Name
Figure 4: The Project Properties Dialog Showing the Assembly Name

You can close the Properties dialog now. The changes will be saved automatically.

Next, it's time to save the project. Press Ctrl+S and Visual C# will offer you a location to save the project (note that if you're using a version of Visual Studio other than the Express Edition, you won't get this dialog; you have indicated where to save the project when you created it).

The Save Project Dialog
Figure 5: The Save Project Dialog

Remember the path that is listed under Location; you'll need it when you need to locate the generated .DLL file later.

Next, it's time to compile the project. If you're not using Visual C# Express Edition, choose Configuration Manager from the main Build menu. Make sure that Release is selected in the Active solution configuration drop-down list and click Close.

Then, regardless of the version of Visual Studio that you are using, choose Build Solution from the main Build menu. This compiles the project into an assembly called SampleAccessProviders.dll. You'll find the assembly in the folder ASP.NET Access Providers1\bin\Release which you can find at the location where you saved the project.

This is pretty much all that you need to do with the Access Providers project. The compiled DLL is all you need from now on. However, if you're brave, take a look around in the .cs files in the Samples\AccessProviders folder to see how things work. You may be tempted to "personalize" things, for example by changing the namespace in code from Samples.AccessProviders to MyCompany.AccessProviders for example. However, it's recommended not to do that right now. It's much easier to get the providers to work with all the default settings. Once you understand how they work, you can make these kind of changes one by one more easily.

With the assembly built, you're done creating the Access Providers. The next step is creating and configuring your web application so it makes use of the new provider. You can close Visual C# Express Edition now because you don't need it anymore.

Configuring the Access Providers for your Web Site

In this section I'll show you to create a new web site that uses the Access Providers. I'll show you how to create and configure the application and how you can use the Web Site Administration Tool (the WSAT) to create new users and roles that are stored in your new Access database by making use of the Access Providers.

At this stage, you may be tempted to apply this to an existing web site directly. However, it's recommended to create a brand new and empty web site first and configure it correctly. Once you understand how it all fits together, you can redo the steps on an existing web site.

So, fire up Visual Web Developer Express Edition (or the full version of Visual Studio) and choose File | New Web Site. From the Visual Studio installed templates box, choose ASP.NET Web Site. Under Location, make sure File System is selected and verify that the location is set to the path you would like to use.
In the Language drop-down list select the language you want to use. I'll be using Visual Basic in my example, but feel free to use C# if you want. All of the configuration I am showing in this article is the same for a Visual Basic and a C# web site.

The New Web Site Dialog in Visual Web Developer
Figure 6: The New Web Site Dialog

Finally, click the OK button to create the new site. A new web site will appear in the Solution Explorer, with two default files: Default.aspx and web.config. You can delete the latter file because it will be replaced with another version later in this walk through.

It's now time to configure the site for the Access providers. First, right-click the project in the Solution Explorer and choose Add ASP.NET Folder | Bin. This creates a Bin folder in your project that will hold the Access Providers assembly.

Then open a Windows Explorer and locate the Access providers project you compiled earlier in this article. Move the Windows Explorer next to Visual Web Developer so both are visible at the same time. Then drag and drop the file web.config from the Windows Explorer onto the root of the project in Visual Web Developer. Repeat this step, but this time drag the database file ASPNetDB.mdb and drop it in the App_Data folder of the project.

Repeat the drag and drop operation once more, but this time drag the file SampleAccessProviders.dll from the bin\Release folder onto the Bin folder in Visual Web Developer. When you're done, your Solution Explorer should look like this:

The Solution Explorer for the Test Web Site
Figure 7: The Solution Explorer for the Test Web Site

If you're having troubles adding the files using drag and drop, you can also add them directly from within Visual Web Developer. To add a file, right-click either the project or a folder in the Solution Explorer of Visual Web Developer, choose Add Existing Item and browse for the file. So if you want to add the Microsoft Access database, you should right click the App_Data folder and choose Add Existing Item, for the .DLL file you should right-click the Bin folder and for the web.config file you can right-click the project itself

Let's briefly recap what changes you just made to your web site:

  1. You added the sample web.config file that came with the Access Providers project. This config file contains configuration settings that tell the ASP.NET 2.0 run-time to use the Access Providers and the Access database for all provider based features, instead of the default SQL Server based providers.

     
  2. You added the ASPNetDB.mdb Microsoft Access database. This database is similar to the SQL Server 2005 version that is created automatically by the ASP.NET 2 run-time. However, there are also a number of differences that you should be aware of. The section "Differences between the SQL Server Based Providers and the Access Based Providers" later in this article describes these differences in more detail.
     
  3. You added the SampleAccessProviders.dll assembly. This assembly contains all the logic that the providers need to work with the Access database. So, API calls like CreateUser or ValidateUser are redirected to this assembly, that in turns accesses the Access database.

If you get compilation errors in your site after you replaced the web.config file, you can add the following default namespace references to the web.config file, as a sub node of the <system.web> element:

  <pages>
    <namespaces>
      <clear />
      <add namespace="System" />
      <add namespace="System.Collections" />
      <add namespace="System.Collections.Specialized" />
      <add namespace="System.Configuration" />
      <add namespace="System.Text" />
      <add namespace="System.Text.RegularExpressions" />
      <add namespace="System.Web" />
      <add namespace="System.Web.Caching" />
      <add namespace="System.Web.SessionState" />
      <add namespace="System.Web.Security" />
      <add namespace="System.Web.Profile" />
      <add namespace="System.Web.UI" />
      <add namespace="System.Web.UI.WebControls" />
      <add namespace="System.Web.UI.WebControls.WebParts" />
      <add namespace="System.Web.UI.HtmlControls" />
      </namespaces>
    </pages>
  </system.web>
</configuration>

These namespaces are added to your web.config file automatically when you create a new web site in Visual Web Developer. However, they were not part of the sample config file that came with the Access providers project.

If all you want to know is how to configure and use the Access Providers, you may want to skip to the next section that shows how to use the providers. Otherwise, keep reading and you'll learn a bit more about the configuration settings and where the default providers that ship with .NET get their information from.

The web.config file you added to the project contains a number of interesting settings that do the magic. I won't discuss all of them, but instead focus on a few interesting ones.

The connectionStrings Node

At the top of the config file, you see the following element:

<connectionStrings>
  <add name="AccessFileName" connectionString="~/App_Data/ASPNetDB.mdb" 
         providerName="System.Data.OleDb"/>
</connectionStrings>

What's important here is the connection string. As you can see it points to ~/App_Data/ASPNetDB.mdb which means as much as "the ASPNetDB.mdb database in the App_Data folder of my current web site". The tilde (~) in the connection string is expanded at run-time to the full path to your web application. So, if your web site is located at c:\inetpub\wwwroot\MySite, then at run-time the connection string is expanded to c:\inetpub\wwwroot\MySite\App_Data\ASPNetDB.mdb.

The membership Node

Another interesting element to look at is the <membership> element:

<membership defaultProvider="AccessMembershipProvider">
  <providers>
    <clear/>
    <add name="AccessMembershipProvider" 
      type="Samples.AccessProviders.AccessMembershipProvider, 
                SampleAccessProviders" 
      connectionStringName="AccessFileName" 
      enablePasswordRetrieval="false" 
      enablePasswordReset="false" 
      requiresUniqueEmail="false" 
      requiresQuestionAndAnswer="false" 
      minRequiredPasswordLength="1" 
      minRequiredNonalphanumericCharacters="0" 
      applicationName="SampleSite" 
      hashAlgorithmType="SHA1" 
      passwordFormat="Hashed"
    />
  </providers>
</membership>

This element tells the ASP.NET 2 run-time that it should use the AccessMembershipProvider for all membership services. You may wonder why you need to add this now, but not to normal web sites that use a SQL Server Express database instead. The answer is found in the file machine.config in your framework's installation folder (located at %windir%\Microsoft.NET\Framework\v2.0.50727\CONFIG). This machine.config file is like the mother of all .NET applications. Without specific actions, your new web site automatically inherits all the configuration settings defined in this file. For the <membership> section, you see something like this in the machine.config file:

<membership>
  <providers>
    <add name="AspNetSqlMembershipProvider" 
      type="System.Web.Security.SqlMembershipProvider, 
                  System.Web, Version=2.0.0.0, 
                  Culture=neutral, 
                  PublicKeyToken=b03f5f7f11d50a3a" 
      connectionStringName="LocalSqlServer"
      enablePasswordRetrieval="false"
      enablePasswordReset="true"      
      requiresQuestionAndAnswer="true"
      applicationName="/"
      requiresUniqueEmail="false"
      passwordFormat="Hashed"
      maxInvalidPasswordAttempts="5"
      minRequiredPasswordLength="7"
      minRequiredNonalphanumericCharacters="1"
      passwordAttemptWindow="10"
      passwordStrengthRegularExpression=""
    />
  </providers>
</membership>

You can see there are a lot of similarities between your config element and the default one defined in machine.config. The biggest differences can be found in the type, applicationName and connectionstringName attributes.

The type attribute points to the full namespace and assembly name of the provider you want to use. In your application, this points to Samples.AccessProviders.AccessMembershipProvider in the SampleAccessProviders assembly. In the default machine.config, this points to the built-in (e.g. the one that ships with the .NET framework) System.Web.Security.SqlMembershipProvider that you find in the System.Web assembly.

The next difference is the applicationName attribute. The applicationName in the provider definition is used to uniquely identity an application in the database. By design it's possible to host multiple applications in the same provider database. You can have two applications, listening to http://www.yourserver.com/App1 and http://www.yourserver.com/App2 for example that both make use of the same database. However, users from one application cannot access the other application and vice versa. This is accomplished by connecting every user to a unique application name. When the .NET run-time sees that no application name is configured, it will automatically generate a name for you. Therefore, it's recommended to always define an explicit applicationName on the providers. In the sample web.config that comes with the Access Providers, the applicationName has been set to SampleSite for all providers as you can see in the code sample shown earlier. To avoid confusion about names, you should consider giving each application a unique name, like WebShop or Intranet in the web.config file.

The other difference that's worth looking at is the connectionStringName attribute. In the machine.config file, this points to a connection string called LocalSqlServer. This connection string in turn points to a database called aspnetdb.mdf in the App_Data folder:

<connectionStrings>
  <add name="LocalSqlServer" 
    connectionString="data source=.\SQLEXPRESS;
        Integrated Security=SSPI;
        AttachDBFilename=|DataDirectory|aspnetdb.mdf;
        User Instance=true" 
    providerName="System.Data.SqlClient" />
</connectionStrings>

The SQL Server Providers are smart enough to create the necessary database objects at run-time when the database that is used doesn't have them already. That's why you often see a delay when you run an application for the very first time. Inside an internal helper method called Install the necessary SQL scripts are executed against the database to create the tables, views, stored procedures and so on that are required for the providers' operation.

The Access Providers presented in the article don't create the database structure for you. So, the database that your connection string points to has to exist and contain all the relevant tables. You saw the <connectionString> element for the Access database earlier, but take another look so you can focus on the actual connection sting:

<connectionStrings>
  <add name="AccessFileName" connectionString="~/App_Data/ASPNetDB.mdb" 
         providerName="System.Data.OleDb"/>
</connectionStrings>

As I explained earlier, ~/App_Data/ASPNetDB.mdb points to an Access database called ASPNetDB.mdb in your App_Data folder. Not coincidentally, this is the database you copied there earlier in this article. This database, that you need to deploy to App_Data (or another location, referenced by your connectionString) for each and every web application that uses the Access Providers, has the following schema:

A Partial Schema of the Microsoft Access Database Used by the Access Providers
Figure 8: Part of the Database Schema of the Access Providers Database

 

This database schema lists most of the important tables in the database. These tables are used to store users, roles, profile information and so on. Besides these tables, you also find a number of queries in the database that are used to create and modify the data in the database.

In most cases, you don't need to change the structure of this database, or mess with the queries. However, should you decide to change or extend the behavior of the Access providers, it's nice to know where you need to look at.

Using the Access Providers in your own Web Site

Once you have set up the site with the previous instructions (e.g. copied the database to App_Data, copied the DLL to the Bin folder and changed the web.config file) you're ready to use the providers in your own site. The good thing about the provider model is that everything you have learned so far about the features that come with the providers is still valid. That is, you can still create users with the CreateUserWizard control, you can still let users login with an <asp:Login> control, you can still call the CreateUser method to create a user programmatically and you can still use the Web Site Administration Tool (the WSAT) to manage your users and roles. Under the hood, ASP.NET will talk to your configured provider which in turn talks to your database. But for you, as a page developer, this is all transparent. All you do is talk to the provider's APIs and they'll handle the hard work for you.

To test out your new provider, choose Website | ASP.NET Configuration from the main menu in Visual Web Developer, assuming you still have your web site open. Once the WSAT is open, click the Provider tab and then click the "Select a different provider for each feature (advanced)" link. On the screen that follows, you'll see that the Access Provider is now the selected provider for both the Membership and the Roles feature:

The Website Administration Tool
Figure 9: The Web Site Administration Tool

At this point you may be wondering how the WSAT is able to see your custom providers. The answer is in the web.config file. As explained earlier, all web applications inherit their settings from the master machine.config file. This file contains default settings for all the providers as shown earlier. However, you can choose not to use the defaults by overriding certain settings. That is exactly what is being done by the settings in the sample web.config file that came with the Access Providers. For each of the four services supported by the Access Providers, a new <providers> element is added to the web.config file. This element tells the .NET run-time where the custom provider is located (assembly and namespace) and how it should be configured (settings like applicationName, requiresUniqueEmail and requiresQuestionAndAnswer for the AccessMembershipProvider for example).

At run-time, the ASP.NET Framework looks at this web.config file to find out what provider to use. The same applies to the WSAT; it looks in the web.config file for all supported and configured providers and lists them on the Provider tab. You can also see how the inheritance principle works for your site. Although your own web.config file doesn't contain settings for providers like the AspNetSqlRoleProvder, you can still see them in the WSAT because they have been defined in the master machine.config file that applies to your site as well.

A final note about the settings in the web.config file may help you in understanding how the provider knows what to do. For example, let's look at the settings for the Membership provider. You see a number of settings in the web.config file like requiresUniqueEmail and passwordFormat. At run-time, these settings are passed to the provider in its Initialize method. The provider then knows what to do with them. For example, a setting of Hashed for the passwordFormat attribute will tell the provider that all password should be hashed before they are stored in the database. With a value of Encrypted all password are stored in a reversible encrypted format, while with the Clear setting all passwords are stored as clear text, in a user readable format. If you want to know how this works, look at methods like CheckPassword and EncodePassword in the AccessMembershipProvider file. Of course this is only one settings, but the principle applies to other settings for all providers as well. You configure them in the web.config file; at run-time they are passed into the provider and then the provider determines what to do with them.

When you've verified that the correct providers are listed (as shown in Figure 8), click the Security tab and create a Role called Administrators. Next, create a user (called Admin for example) and assign that user to the Administrators roles.

Then close the WSAT and go back to Visual Web Developer. Add a new page to your project and call it Login.aspx. From the Visual Web Developer Toolbox drag a Login control on the page.

Next, open the web.config file again and change the authentication element so it looks as follows:

<authentication mode="Forms">
  <forms loginUrl="Login.aspx" defaultUrl="Default.aspx"/>
</authentication>

Finally, in the Default.aspx, add a Label control and add the following code in the Page_Load event in the code behind:

Protected Sub Page_Load(ByVal sender As Object, _
         ByVal e As System.EventArgs) Handles Me.Load
Dim result As String = _ "Hello {0}. Member of Admin role: {1}" Label1.Text = String.Format(result, _ User.Identity.Name, User.IsInRole("Administrators"))

This code sets the Text property of the label with the user's name and a value indicating whether the current user is an Administrator or not.

To test it out, open the page Login.aspx in your browser and login with the account you created earlier. If all turned out as planned, you should see the following after you logged in:

The The Test Page in the browser Showing the User's Name and Group Membership
Figure 10: The Test Page in the browser Showing the User's Name and Group Membership

While I am sure these 39 characters aren't the most exciting things you've ever seen, it's still quite a miracle you see them at all. Although it took you some time to configure the application, you haven't actually coded a lot. Simply by dragging and dropping, and declaratively adding controls and settings to your application, you have a complete security framework at your disposal. Personally, I see this as the greatest strength of .NET: a lot of power is available out of the box. If you don't like the standard behavior, it's still pretty easy to change certain aspects of the built-in functionality and replace them with custom components.

While it may seem that the Access Providers are a great asset to your developer's toolbox (and they are), there are also a few differences with the "real", SQL Server based providers that you need to be aware off.

Differences between the SQL Server Based Providers and the Access Based Providers

The biggest difference is the database schema. In the SQL Server implementation GUIDs (Globally Unique IDentifiers) are used to uniquely identify objects like Users, Roles and Applications. In the Microsoft Access database and the Access Providers, numeric values (Integers) are used. In itself this is not a problem as long as you stick to the Access Providers. However, it's not unlikely that over time your web site grows and you'll find that your Access implementation no longer cuts it. When you then want to upsize to SQL Server, you'll run into a few conversion issues. While it should be possible to upgrade the data to the SQL Server format, you'll need to carry out a number of "data massaging" tasks that may be time-consuming. Note that I haven't actually tried to upsize the Access database to the SQL format yet, so be sure to test this out thoroughly if you think you'll take this route in the future.

Another difference is the absence of certain features like enforcing password strength requirements in the Membership provider. The readme.txt that comes with the Access Providers lists all of the missing features and "known issues".

The final difference is not as much related to the Access Providers, as it is to Microsoft Access itself: performance. While an Access database is useful in many situations, it's just not as powerful as its bigger brothers SQL Server 2000 and SQL Server 2005. For many small to medium-sized web sites, you'll find that Access works fine, though. Just don't use it to run the next Yahoo killer web site on it....

Although the Access providers have different behavior than, say, the SQL providers, the services that ASP.NET 2.0 provides are not aware of this. For example, both the custom AccessMembershipProvider and the built-in SqlMembershipProvider have the same set of methods, as you can in the following diagram:

The Class Diagram of Two Membership Providers
Figure 11: The Class Diagram for the Access and SQL Based Membership Provider

As you can see, both providers stick to a contract and implement the same set of methods, like CreateUser, GetAllUsers and so on. This contract is enforced by the abstract MembershipProvider class that both implementations inherit from.

Summary

The Access Providers used in this article may be a welcome tool for many developers. When you can't use SQL Server, but still want to use the built-in features for membership, role management, profiles and personalization, you have to look at other means to store your data. The Access Providers that I used in this article do exactly that: they allow you to use the complete ASP.NET 2.0 feature set, with a Microsoft Access database as the backing store.

This article didn't focus on the inner workings of the providers. In fact, the good thing about the provider model is that often you don't even have to look at the inner workings of things. As long as you know how to configure a new component, you can easily reuse functionality created by others.

So instead, this article focused on making the Access Providers work in your web application. You saw how to acquire them from the MSDN site. You then saw how to customize and compile them, so they're ready to be used in your own web sites. You then saw how to configure your ASP.NET 2.0 web application to make use of these new providers.

The article ended with a quick tour of using the providers and an overview of the biggest differences between the Access Providers and the SQL Server providers that ship with ASP.NET 2.0.

If you want your web site to work with the Microsoft Access providers, this article has shown you everything you need to know and do to embed them in your web application.

References and Related Reading

The following links may help you to better understand the .NET provider model, or help you find and fix problems related to security that you may run into when you install and use the Access Providers.

The Provider Model
 

Security
 

Download Files


Where to Next?

Wonder where to go next? You can read existing comments below or you can post a comment yourself on this article .


Consider making a donation
Please consider making a donation using PayPal. Your donation helps me to pay the bills so I can keep running Imar.Spaanjaars.Com, providing fresh content as often as possible.



Feedback by Other Visitors of Imar.Spaanjaars.Com

On Monday, August 28, 2006 8:58:06 PM Robert Searing said:
FINALLY an "all-encompassing" article that not only helps explain the ASP.NET new features, but provides a very good understanding of what is going on "underneath the hood" along with a tool box to tweak things.

I just have one question---for newbies, such as myself, that weren't ever provided a very good understanding of namespace--could you dig down just a bit deeper on your comment:

You may be tempted to "personalize" things, for example by changing the namespace in code from Samples.AccessProviders to MyCompany.AccessProviders for example.

What would this do?  Why would I want to change it?  It works fine as it is....but I'm sure there is a reason I would want to change it.

Is there a way you could provide just a basic understanding (very basic) of namespace and then perhaps explain why the Samples.AccessProviders works and then what changing it would do?

Kind Regards,
Rob Searing
On Monday, August 28, 2006 9:38:11 PM Imar Spaanjaars said:
Hi Robert,

Simply put: namespaces are simply naming containers to avoid name collisions. You and I could both come up with a control called SomeCoolControl. If a page developer tried to add your and my control to a page at the same time, the compiler woulddn't now what SomeCoolControl we were talking about. With a namespace, you can avoid this problem. You call yours Searing.SomeCoolControl, I call mine Spaanjaars.SomeCoolControl and voila: instant unique names....

I have seen many people (including myself) that wanted to make third party code "theirs". A namespace is a great example of that: Instead of Samples.AccessProviders it would be easy to use find and replace and change it to Searing.AccessProviders or Spaanjaars.AccessProviders making it look like we did program all that code ourselves. Looks good for your colleagues, your boss and so on. Until things break apart, you don't know how to fix it and everyone realizes your ripped this code from some place else ;-) Just kidding.

But whatever reason you may have to change it; you can do it pretty easily. It wouldn't effect the quality of the code. You could use find and replace and put the provider in the Searing.AccessProviders namespace and everything would still work.

But, I recommended not to do it, because it also means changing other things in the web.config file.
If you know what you're doing: by all means change the namespace. But if you're not sure, leave as much things as they are; the less chance you have of breaking important stuff...

Hope that answers your question...

Imar
On Monday, August 28, 2006 10:24:18 PM Robert Searing said:
Almost--and I promis not to belabor the point...answering this should help.

So, when you state: System.Web.Security.SqlMembershipProvider

Where is that physically located?  Does this help the compiler know where to find the file?

Same with Samples.AccessProviders.AccessMembershipProvider,
                SampleAccessProviders

How does the compiler know where to find it---and, lastly, by changing the namespace--am I just changing the "Samples" or "Samples.AccessProviders".

Thanks so much,
Rob Searing
On Tuesday, August 29, 2006 6:02:26 AM Imar Spaanjaars said:
Hi Robert,

System.Web.Security.SqlMembershipProvider is probably located in System.Web (not sure).

Usually, it's recommended to name an assembly after the root namespace, but this is not required. So, you *could* store the namespace Samples.AccessProviders in an assembly called WhatEver.dll, but obviously, this is pretty confusing.

Either way, the compiler doesn't care. It doens't help the compiler to find things. You reference an assembly, whatever it's called, and the compiler can see all the namespaces defined in that assembly.

And if you want to change the namespace, you can change both. So, Searing.AccessProviders would work, as would AccessProviders as would Searing.Confusing.Namespace.That.Doesnt.Say.Much

This is exactly what I meant in my article: at this point, you probably want to leave the namespace as is .... ;-)

Cheers,

Imar
On Saturday, September 16, 2006 7:34:13 PM Mick said:
Hi
excellent article, I am at long last able to add an access db to my website, but I keep getting configuration error messages when I build the site, I've added the suggested namespace references from your article but still no joy :-)
On Sunday, September 17, 2006 10:58:48 AM Imar Spaanjaars said:
Hi Mick,

What error messages do you get? Are they compile errors or compile warnings?

Imar
On Sunday, September 24, 2006 9:00:57 PM Ron Heimann said:
Thank you very much :]
On Wednesday, October 04, 2006 2:03:03 AM Allan Parson said:
Hello Imar,
Thank you for a great article.
I ran the sample web site on my workstation, and it worked fine.
But when I uploaded the same site to my hosting company,
it generated "HTTP 500 - Internal server error " right after
I enter my credentials in the Login.aspx page and click Login button.
Although, if I enter the wrong credentials, it asks me to enter the right ones which means that it works to some point.

Could you kindly tell me what might be a problem?

Thanks a lot.

Allan
Allan
On Wednesday, October 04, 2006 2:09:32 AM Imar Spaanjaars said:
Hi Allan,

This sounds like a security issue. A failed Login doesn't write to the database, while a successfull login does which means the account used by the webserver needs write permission to the database and its folder. Check out this FAQ for solutions to this problem:

http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=263

Cheers,

Imar
On Wednesday, October 04, 2006 3:46:34 AM Allan Parson said:
Hi Imar,

You was exactly right. That was a 'write' permission to a DB file problem.
Thanks a lot!
Allan
On Saturday, October 14, 2006 10:05:25 PM Mohamed Salem Korayem said:
Excellent all-in-one article....really appreciate it
On Sunday, October 15, 2006 2:30:58 PM Gabe said:
Hi Imar

Thank you for this posting. I was having problems with the sql server i am hosting my site at. I needed something like this.

I have 2 questions.

1. i created a PasswordRecovery object on my form. when i am running the application, i try to retreave a user's password but i get the following error:

Parser Error Message: Provider cannot retrieve hashed password

Source Error:


Line 11:         [clear/]
Line 12:         [add name="AccessMembershipProvider"
Line 13:      type="Samples.AccessProviders.AccessMembershipProvider, SampleAccessProviders"
Line 14:      connectionStringName="AccessFileName"
Line 15:      enablePasswordRetrieval="true"

-any ideas?

2. when i create an user using the CreateUserWizard how can i forze the new user to be in a default role?

Thank you!!!

Gabe
On Sunday, October 15, 2006 2:37:38 PM Imar Spaanjaars said:
Hi Gabe,

You can't use enablePasswordRetrieval with a value of true when you're hashing passwords. Hashed password cannot be converted back to their original clear text version.
Instead, you can use enablePasswordReset="true" which gives the user a new, random password.

Alternatively, you can choose not to use password hashing, but use encryption instead. Personally, I wouldn't do that as hashing is a bit safer.

You can assign a user to a role with the follow code *in the CreatedUser event for example)

Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object,
      ByVal e As System.EventArgs)
      Handles CreateUserWizard1.CreatedUser
  Roles.AddUserToRole(CreateUserWizard1.UserName, "Default Role")
End Sub

Hope this helps,

Imar
On Sunday, October 15, 2006 5:27:26 PM Gabe said:
Thank you for your help. I got that to work.

Now i have another problem. I had everything working the way i wanted but i needed to install SSL for my domain. right after i did that when i try to use my login.aspx, i get an http 500 error after i click on login.

Most likely causes:
The website is under maintenance.
The website has a programming error.

any ideas?
On Sunday, October 15, 2006 5:40:35 PM Imar Spaanjaars said:
Hi Gabe,

You can take a look here: http://www.spaanjaars.com/QuickDocId.aspx?quickdoc=264 to see how to turn generic 500 error messages into more useful information.

If that doesn't help, I suggest you post your problem at a forum like the one at http://p2p.wrox.com. It's a much better platform for posting technical questions than my web site.

Cheers,

Imar
On Sunday, October 15, 2006 6:01:15 PM Gabe said:
Thank you again...

i just get "Cannot write to DB File" , i looked at the code from the provider and it looks like this:
       HttpContext context = HttpContext.Current;
                if (context != null)
                {
                    context.Response.Clear();
                    context.Response.StatusCode = 500;
                    context.Response.Write("Cannot write to DB File");
                    context.Response.End();
                }

i just do not anderstand why after enabling SSL the connection seems to get lost.

I am going to try posting where you suggested.

Thank you so much for all your help.
On Sunday, October 15, 2006 6:35:20 PM Imar Spaanjaars said:
Hi Gabe,

You're welcome. When you've posted your question there, send me the link and I'll take another look. I haven't run into into issue myself, so I cannot give much advice, though....

Imar
On Tuesday, October 17, 2006 3:04:10 AM Gabe said:
Hi Imar

I wanted to get back to you since you helped me so much.

The problem is related to write access to the App_Data folder. when i enabled IIS it reseted all the permissions set on my folders. My hosting company has a way for the users to set different permission levels for each folder. I cleaned everything i did before, then i re-uploaded my code and reset permissions.

You don't really have to do all that but since i was testing with different settings i had a big mess. You could just set "write" permissions to when your db files are.

Thank you again.

Gabe
On Tuesday, October 17, 2006 6:26:39 AM Imar Spaanjaars said:
Hi Gabe,

Thanks for the follow up. Glad it's all working now. In case you hadn't seen it, Allan asked a similar question and I referred him to http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=263
Might be useful in the future.....

Cheers,

Imar
On Friday, October 27, 2006 2:48:41 AM Cornelius said:
Excellent Article Imar!!. Exactly what I need.  Thanks.
On Tuesday, October 31, 2006 12:41:49 AM cjonex said:
i looked high and low for this crap and gave up a month ago
On Tuesday, October 31, 2006 7:38:17 AM Imar Spaanjaars said:
Hi cjonex,

You should have searched harder. This "crap" has been here for more than two months.... ;-)

Imar
On Thursday, November 02, 2006 11:26:37 PM Xino said:
Excellent article! Comprehensive, communicative. I found all the answers in it. Thanks
On Saturday, November 04, 2006 12:43:50 PM Rick North said:
Many thanks for this.  I wasted a lot of time on an alternative approach and was about to consign the MS Access idea to the "hopeless, forget it" bucket until I happened on your site.  Great stuff and clear writing.
On Saturday, December 02, 2006 6:51:11 PM Paulo Ricardo Ferreira said:
Hi, Imar!

I've followed all the steps til this point:

When you've verified that the correct providers are listed (as shown in Figure 8), click the Security tab and create a Role called Administrators. Next, create a user (called Admin for example) and assign that user to the Administrators roles.

After verifying that the correct providers are listed, I clicked the Security tab and got this error message:

There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem: Could not load file or assembly 'SampleAccessProviders' or one of its dependencies. The system cannot find the file specified. (C:\Documents and Settings\Paulo Ricardo\My Documents\Visual Studio 2005\WebSites\AccessProviders\web.config line 13)

I'd like a help from you so I could go further and finish the project.

Hope you understand and help me.

Greetings,
Paulo Ricardo Ferreira
On Saturday, December 02, 2006 7:19:14 PM Imar Spaanjaars said:
Hi Paulo,

Are you sure you added the correct assemblies to your project? Is the file with the web.config file a true IIS application? What files do you have in the Bin folder of the app?

Imar
On Sunday, December 03, 2006 8:15:19 PM Paulo Ricardo Ferreira said:
Are you sure you added the correct assemblies to your project? Is the file with the web.config file a true IIS application? What files do you have in the Bin folder of the app?
Hi, Imar!

I'm quite sure I've added the correct assemblies to my project.

What do you mean qith web.config file being a true IIS application?

In Bin folder of the app I have ASP.NET Access Providers1.dll and ASP.NET Access Providers1.pdb files.

Imar, thanks for your time.

Paulo Ricardo Ferreira
Rio de Janeiro - Brasil
On Sunday, December 03, 2006 9:43:03 PM Imar Spaanjaars said:
In that case, you missed a step. About a quarter down in the article, you find instructions on changing the default assembly name from ASP.NET Access Providers1.dll to SampleAccessProviders.dll. It explains why you need to do that, and how to do it.

Cheers,

Imar
On Sunday, December 03, 2006 10:32:46 PM Paulo Ricardo Ferreira said:
Hi, Imar!

Maybe I've misunderstood and missed that step.

I'll fix it and try it again later.

Thanks, again, for your time.

Best regards,
Paulo Ricardo Ferreira
Rio de Janeiro - Brasil
On Tuesday, December 05, 2006 10:04:53 AM Tom said:
Hi, Imar, thanks for the excellent article. I've been looking for this solution for a couple of weeks.

I was wondering if it's possible to include the Membership, Roles, etc. into an existing Access database. I imagine it's a matter of copying the tables, queries, etc. and then using the config to point to the correct .mdb file, right? Or am I missing something?
And I assume I can include a password for pw-protected .mdb files, right?

Once again, thanks a bundle.
On Tuesday, December 05, 2006 10:27:50 AM Imar Spaanjaars said:
Hi Tom,

You're welcome....

When you want to use a custom database, you indeed need to copy the aspnet_* tables and the vw_aspnet_* queries from the default one into your custom database.

Then you also need to change the connection string in the web.config file to point to your database. With regards to a user name / password proteced database, take a look here for examples of connection strings that you can use: http://www.connectionstrings.com/?carrier=access

Hope this helps,

Imar
On Tuesday, December 12, 2006 9:53:08 PM Smileyman said:
Hi. Great article.. I have seached for something like this for some time, and never found something usefull. Luckely someone was a better searcher than me and gave me this link :-)

All is working now, except profiles.. I cant seem to add anything to my profiles, but I am not sure if it is related to something from this article or something i Did. I will take a good look at it tomorrow.

Regards
   smiley
On Saturday, December 16, 2006 9:29:43 PM Surya said:
This article is a brilliant article..the author not only knows the techie part, but the way it was narrated is flaw less..

Cheers
On Sunday, December 17, 2006 12:27:20 PM Imar Spaanjaars said:
Hi Surya,

Thank you for your kind words. I am glad you like the article...

Cheers,

Imar
On Monday, December 18, 2006 2:31:06 PM Smileyman said:
It turned out, that profiles work just fine. It was an error in my code :-)
On Monday, December 18, 2006 11:05:25 PM Imar Spaanjaars said:
Hi Smileyman,

Glad it's all working now.....

Cheers,

Imar
On Monday, December 25, 2006 6:18:30 AM Surya said:
Hi Imar,

I am back...I was trying to figure out if I can add more custom fields like First Name, Last Name in the CreateUserWizard?

Any thoughts
Surya.
On Monday, December 25, 2006 2:19:45 PM Imar Spaanjaars said:
Hi Surya ,

Take a look at this article by Scott Guthrie: http://weblogs.asp.net/scottgu/archive/2005/10/18/427754.aspx

It explains how to tweak the CreateUserWizard and use the Profile feature to accomplish this.

Cheers,

Imar
On Sunday, January 07, 2007 5:48:48 PM david said:
thanks a lot . very useful and very clear
keep up the great work
david
On Wednesday, January 10, 2007 5:14:45 PM MKumar said:
Hi,
First of all, I want to say that this is a great article since all the tutorials etc use SQL Server. I am a newbie, so please forgive if I ask a stupid question. Also, FYI, I do the development on my desktop and my files and databse are on a network server. I am getting the same error message as "Paulo Ricardo" :
--------
After verifying that the correct providers are listed, I clicked the Security tab and got this error message:

There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem: Could not load file or assembly 'SampleAccessProviders' or one of its dependencies. The system cannot find the file specified. (C:\Documents and Settings\Paulo Ricardo\My Documents\Visual Studio 2005\WebSites\AccessProviders\web.config line 13)

------------
Also, do I import my tables for the applications into ASPNetDB.mdb???

I'd appreciate any help from you as I am stuck & would like to  proceed to finish the project
Thanks!
On Wednesday, January 10, 2007 8:38:52 PM Imar Spaanjaars said:
Hi MKumar,

And did you make the same mistake as Paulo? Did you put the assembly with the correct name in your Bin folder? What's the name of your DLL?

The database that comes with the project already has all the tables you need; there's no need to import tables for the providers to work.

Cheers,

Imar
On Wednesday, January 17, 2007 1:48:11 PM sergio alabi said:
Thanks a lot, i was wasted a lot of time to find anything about ASP Net Access Providers and when I found, dont´t work it better, but, when i found this article, my problemas about this was solve.

Do you know about Provider for SQL Server 2005 Compact Edition for this case?
On Wednesday, January 17, 2007 6:51:01 PM Imar Spaanjaars said:
Hi Sergio,

Glad you found the article useful.

It's been a while since I did anything "compact" in .NET, so no, nothing to say about providers for SQL Server 2005 Compact Edition, unfortunately.

Imar
On Friday, January 19, 2007 7:13:50 PM MKumar said:
Yes I did..Everything is exactly as it is shown in figures in the article.
The dll is SampleAccessProviders.dll
I started from scratch again and now get the error message:

----

There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem: Request for the permission of type 'System.Web.AspNetHostingPermission, System, Version =2.0.0., Culture=neutral, PublicKeyToken=b77a5c561934e089'failed (L:\Inetpub\wwwroot\ABC\FGH\website1\web.config line 13)

-----

Any help is appreciated!
On Friday, January 19, 2007 7:18:35 PM Imar Spaanjaars said:
Hi MKumar,

>> L:\Inetpub\wwwroot...

Looks like you're running the app from a network drive. Do you get the same exception when you run it from your local disk?

Imar
On Monday, January 22, 2007 5:29:59 PM MKumar said:
Yes I am running from a network drive
I had mentioned in my earlier post that I do the development on my desktop and my files and database are on a network server


If I copy the website on my local workstation then I don't get the error.

Thanks
On Monday, January 22, 2007 7:26:16 PM Imar Spaanjaars said:
Hi MKumar,

In that case, make sure that the account used by the database server has sufficient access permissions to the network drive.

I am also not 100% sure if files on a network drive are supported. I think so, but you may want to look into this a little further.

Cheers,

Imar
On Sunday, January 28, 2007 9:40:17 PM J. Rettig said:
Hi Imar,

     Thank you for all this information on how to run the security off an access database.  My web host only allows uploads of access databases so it saved me a lot of work trying to create my own.

I got everything to work on the local host but after uploading all the files to the server and setting the Bin directory as an application directory I try to login and after clicking the login button it gives me a "System.Exception: AccessFile is not valid" exception and the top line of the stack trace is "Samples.AccessProviders.AccessConnectionHelper.EnsureValidMdbFile(String fileName) +129".  I know that the connection string is working because there are other pages working off of it.

Here are the pages I am talking about:

broken login:  http://fsukappasigma.org/support/Login.aspx
working page:  http://fsukappasigma.org/brothers.aspx

I am a beginner at all this database stuff and getting just the pages that work to work was a pain and now the login is also being a pain.

Thanks in advance,

-J. Rettig
On Sunday, January 28, 2007 10:16:07 PM Imar Spaanjaars said:
Hi J. Rettig,

How does your connection string look like? Are you using the folder expansion |DataDirectory| in the connection string?

If so, try changing it to something like ~/App_Data/ASPNetDB.mdb as I am not sure the providers understand the expansion placeholder to refer to App_Data.

Otherwise, take a look here: http://forums.asp.net/thread/1237061.aspx

Cheers,

Imar
On Monday, January 29, 2007 8:51:34 PM J. Rettig said:
Hi again Imar,

I figured out that without a ProviderName="..." it uses the connection string as the database path.  I still can't get it to work after adding that in.  Under /\ConnectionStrings\/ I changed the line to

/\add name="ProviderConnStr" connectionString="Provider=sqloledb;Data Source=mssql05.1and1.com,1433;Initial Catalog=dbxxxxxxx;User Id=dboxxxxxxxxxxx;Password=xxxxxxxxxx;" providerName="System.Data.OleDb"\/

and I copied the connectionString part in quotes off what the server said it connection string was.  Now it gives me the error "Must declare the variable '@AppName'.".  I am assuming the SampleAccessProviders.dll code isn't bad so I must still have a problem figuring out what the connection string should be for a sql server that is on a different machine than the host.  I looked on forums for connection strings but none of em work.  You can see the stack trace of the page by going to http://fsukappasigma.org/support/Login.aspx and typing in any user name and password.

Thanks for the help and quick response time,
-J. Rettig
On Monday, January 29, 2007 9:36:21 PM Imar Spaanjaars said:
Hi J. Rettig,

Sorry for the errors you got. You can't post HTML, as you found out.... ;-)

Anyway, @AppName is used in the AccessConnectionHelper class in the file AccessConnectionHelper.cs.

It tries to perform a select against the aspnet_Applications table, and if there is no application, it tries to create one.

However, your connection string looks like a SQL Server connection string, NOT like an Access connection string. The whole point of the Access providers is that they target Microsoft Access, so you'll need to use a connection string (as defined in the sample web.config) that points to a valid Access database. For example:

[connectionStrings]
  [add name="AccessFileName"
      connectionString="~/App_Data/ASPNetDB.mdb"
      providerName="System.Data.OleDb"
  /]
[/connectionStrings]

This points to the ASPNetDB.mdb database in the site's App_Code folder.

So, what is it what you're trying to do? Target Microsoft Access? Or target Microsoft SQL Server??

Imar
On Tuesday, January 30, 2007 6:41:25 AM J. Rettig said:
Hi Imar,


Thanks for your help.  Not to long ago I finally figured out how to get it to work.  You were correct and 1and1 is a sql server and it needs the sql connection string.  What threw me off was that they only allow updates of access mdb files and it only exports mdb files.  so i thought it was running of a access database.  Now just incase anyone else runs into this problem here was my solution:

In order to get the tables, procedures, and views loaded onto the server I ran "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50215>aspnet_regsql -S mssql##.[host].net -U [username] -P [password] -sqlexportonly c:\name2.sql -A all -d [dbname]" in the cmd prompt to give me the sql script.  I ran that on the server's query tool w/ a copy and paste from the file.  My db then had all that stuff in there.

It still wont work though.  It needs a user to be created to populate the application table.  Then I was able to use a user and role manager I got from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/ASP2memroleman.asp that has a tool that works like the WAT.  I created my user and added an admin role to it.  I changed the web.config to only allow admin roles and went to the page and the sign in worked.

So much time and it now seems so easy.

Thank you again for all your help Imar,

-J. Rettig
On Tuesday, January 30, 2007 5:05:46 PM Imar Spaanjaars said:
Right, I see.

At the top of the article, I linked to a post by Scott Guthrie: http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

That post describes in more detail how to set up a database to work with Membership, Roles and other providers.

Cheers,

Imar
On Thursday, February 08, 2007 1:06:43 PM rahul said:
i want to import ms access database into sql server in c# . plz send me code
On Thursday, February 08, 2007 1:23:26 PM Imar Spaanjaars said:
Hi rahul,

How is this related to the original topic - using the Microsoft Access Providers??

Imar
On Sunday, February 18, 2007 9:44:27 PM Tom Regan said:
Thanks for the article, you saved me a lot of time, I need to use MS Access as membership and role provider.

Everything works, except I found that I could not use the Asp.Net web site administration tool to manage rolls.  I kept getting an error complaining that I lacked a connection string named "LocalSqlServer."  (Oddly enough, I could add users just fine, but not roles).

This complaint was justified--I deliberately removed "LocalSqlServer" from my web.config and used the "clear" node, because I wanted to stop asp.net from automatically creating the SQL Server Express AspNetDb.mdf in my App_Data folder! However, this breaks the Web Site Administration Tool.

I found that if I simply created a "LocalSqlServer" connection string and pointed it to my MS Access membership provider database it solved the problem (eg, under [connectionStrings]:
[add name="LocalSQLServer" connectionString="~/App_Data/ASPNetDB.mdb" providerName="System.Data.OleDb"/]

Thanks again for your excellent article.
On Sunday, February 18, 2007 9:49:47 PM Imar Spaanjaars said:
Hi Tom,

Yes, that's one way of doing it.

But if you look at the web.config file that comes with the provider, there's another solution. For each provider based feature, the settings are overriden in this file. Instead of the default localSqlServer connection string, each provider gets a new connectionString attribute that points to the connection string that in turn points to the local Access database.

Hope this helps,

Imar

P.S. Sorry for the errors you got; you can't post HTML / XML tags here.
On Sunday, February 18, 2007 11:33:40 PM Tom Regan said:
I know what you mean, and I do not have anything in my web.config that points to "LocalSqlServer."  

This is a bug in Asp.Net or the Ms Access role provider class, or something is going on that I do not understand, because I cannot run the application without a connection string named "LocalSqlServer" in my web.config if my role manager is enabled and pointing to MsAccess.  When I try I get this error:

Runtime Error at http://localhost:1436/Roadc/Home.aspx: System.Configuration.ConfigurationErrorsException: The connection name 'LocalSqlServer' was not found in the applications configuration or the connection string is empty. (C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Config\machine.config line 131)
   at System.Web.Security.Roles.Initialize()
   at System.Web.Security.Roles.get_CacheRolesInCookie()
   at System.Web.Security.RoleManagerModule.OnEnter(Object source, EventArgs eventArgs)
   at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)  

The error points to the line in my machine.config folder which instantiates the AspNetSqlRoleProvider.  I'm not using the AspNetSqlRoleProvider in my application's web.config (I'm using the AspNetAccessProfileProvider, which points to the MS Access database connection string).

The error disappears when I add a ConnectionString to my web.config called "LocalSqlServer" that points to my Ms Access database connection string.  It does not make any sense--which makes me suspect a bug.
On Monday, February 19, 2007 12:17:09 AM Tom Regan said:
Stupid of me--I forgot to add a [clear] node under [roleManager] before adding the [provider] node for the MsAccessRoleProvider in my web.config.  The lack of the [clear] node caused Asp.net to (rightly and justly) attempt to instantiate the AspNetSqlRoleProvider in machine.config.  

Obviously there is no bug here.
On Friday, March 02, 2007 7:39:32 AM Per Magnusson said:
Excellent article, that made it easy for me to implement everything I needed.

However, I would like to improve performance and scalabillity and move to a database server. MySQL is available by the hosting company. Do you know any similair way to connect to MySQL?

Thanks,
Per
On Friday, March 02, 2007 6:08:00 PM Imar Spaanjaars said:
Hi Per,

I never used it myself, but there is a MySQL implementation for Membership and Roles here:

http://www.codeproject.com/aspnet/MySQLMembershipProvider.asp

Cheers,

Imar
On Friday, March 09, 2007 1:24:58 PM Rene said:
Hey Imar,

First of all i'd like to thank you for this very good article, it helped me alot.
I've got everything to work, but i have one question: in Visual Web i get about 100 messages with pretty much the same stuff: "couldn't find schema information for the element "http://schemas.microsoft.com/.NetConfiguration/V2.0:configuration".
Like i said, everything is working as supposed to, but those messages are bugging and i can't seem to find a proper solution for this.
I'm looking forward to your reply, greets
Rene, NL
On Friday, March 09, 2007 7:24:07 PM Imar Spaanjaars said:
Hi Rene ,

You can remove the following attibute from the root configuration element in the web.config file and then save it:

xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"

Cheers,

Imar
On Monday, March 19, 2007 8:18:33 AM John said:
Imar,

I have a question about the access database. I've designed a database where customers of our company can update their orders. But, unfortunately, the problem is that every company can see the orders of everyone. I've tried to add a custom sql query to the database to allow only to see their own products. I can see if someone is logged in (done like in this tutorial) and where this user is browsing (custom code).
Can you help me with this problem?

cheers, John.
On Monday, March 19, 2007 8:30:22 AM Imar Spaanjaars said:
Hi John,

You need a way to map a user to a customer. How you do this depends on your design. You could have a UserCustomer table where you map each individual user to a customer. You can then use "Membership.GetUser.ProviderUserKey" to get the ID of the current user. You can use that ID (an Integer or a Guid depending on the implementation) to join it on your UserCustomer table which in turn can be used in a JOIN on the Order table.

Alternatively, you could store customer info in the user's Profile and then use a Profile property to get at the user's customer ID which is then used to retrieve correct orders.

If this doesn't help, may I suggest you post this question with more detail on a forum like http://p2p.wrox.com ?

Cheers,

Imar
On Tuesday, March 20, 2007 3:47:20 AM Nigel said:
Imar... thank you also for a detailed and well presented article.

I have got SQLMembership functionality running on a hosted SQL server, i was eager to be able to use the membership classes in my access based web sites... with the only issue that MS removed this from the beta and subsequent releases of Visual Studio (apparently)

Anyway, after some interesting ventures including downloading and installing Visual C# 2005 "All for one .dll" :-) Anyway i am pleased i have, as i wanted to play around with some more desk top Apps.

Anyway, I carefully followed your instrcutions and was well pleased to see the 40 letters on my default.aspx ... 40 letters? Yes, because i didn't pluralise the role "Administrator" and got :

Hello admin, Member of Admin role: False ... done a quick tweak and then got: Hello admin, Member of Admin role: True - Hooray !

A Brilliant article - keep up the good work !
Nigel.
On Tuesday, April 03, 2007 5:03:48 PM Darren Brook said:
Fantastic article!  Big help as my hosting company only allows mySQL or Access.

But, I've experienced a problem.

Upon successfully logging in and redirecting, I am having a problem detecting if a user is a member of a role.

The code I am using is in the Page_Load event of the page the user will go to upon correct authentication:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If (Page.User.Identity.IsAuthenticated = True) Then

            If (Page.User.IsInRole("Admin") = True) Then
                welcomeLabel.Text = "Hi, " & Page.User.Identity.Name.Trim.ToString & ", and welcome to the Sky Software Hub."
            Else
                welcomeLabel.Text = "You (" & Page.User.Identity.Name.ToString & " are not a member of the correct security role."
            End If
        Else
            welcomeLabel.Text = "You are not an authenticated visitor."
        End If
    End Sub

The problem I have is that even though I log in with a user that is a member of the Admin role, I still see the message "You are not a member of the correct security role."

Any ideas?

Thanks,
Darren
darrenbrook@btconnect.com

On Tuesday, April 03, 2007 5:30:06 PM Imar Spaanjaars said:
Hi Darren,

Glad you find the article useful.

Did you try the little test at the end of the article? Did it say you're in the Admin role?

Also, does this only happen on your remote server? Maybe you think the user is in the correct role, while in reality it is in the Admin role for a different application?

Take a look at the Applications, Users Roles and UsersInRoles tables in the database. Do you see the things you'd expect?

Imar
On Tuesday, April 03, 2007 7:31:19 PM Darren Brook said:
Hi,

Yes, I've created a role called Admin.  This is in the database.

Yes, this does only happen on the remote hosted server.  It works okay locally.  Could there be something not installed or that needs configuring properly on the hosted server?

I'm not sure what you mean about the user not being in the correct role, or it being in the Admin role for a different application.  Could you explain that a bit more?

Basically, there is only one role - Admin.  There is only one application.

In the database, the IDs all seem to be where I imagine they should be.

There is a  entry in the Applications table - ApplicationName: SampleSite, ApplicationID: 3

There is an entry in the Roles table - RoleID = 17, RoleName = Admin, ApplicationID =3

There is an entry in the Users table - UserID=26, ApplicationID=3,

There is an entry in the UsersInRoles table - UserID=27, RoleID = 17

???

Thanks,
Darren
darrenbrook@btconnect.com

On Tuesday, April 03, 2007 7:38:37 PM Imar Spaanjaars said:
Hi Darren,

Is this a typo in your message, or the actual cause of the problem:

Users table - UserID=26, ApplicationID=3,
UsersInRoles table - UserID=27, RoleID = 17

Notice how the Users table has a user with an ID of 26. However, in the UsersInRoles table, there is a record for a user with an ID of 27....

Hope this helps,

Imar
On Tuesday, April 03, 2007 7:45:32 PM Darren Brook said:
Sorry,

From looking at it, the ID numbers seem to "tie up" okay...

it was a typo and it is 26 in the database, so (sadly) it's not that that's the problem..??

Thanks,
Darren

On Tuesday, April 03, 2007 7:52:58 PM Imar Spaanjaars said:
Hi Darren,

Try renaming the SampleSite application to / or look at the applicationName settings in web.config and change them accordingly.

ASP.NET is able to host multiple applications in a single database, each one separated by its ID. So you can have multiple roles and users, each one tied to a specific application.

These application records are generated automatically, based on the site's name. There's a fair chance you opened your local site as http://localhost/SampleSite, resulting in an app called SampleSite.

Probably you're browsing to the site as http://www.yourdomain.com, resulting in an application called /

If that doesn't fix it, I am out of ideas.....
On Tuesday, April 03, 2007 8:50:44 PM Darren Brook said:
Hi,

Tried that but sadly it didn't fix it.

Quite frustrating.  New to asp.net so it feels like a mountain and a minefield.  Spent all day on this now.... very tired now!

If you do have any ideas, I would very much appreciate them.

The site works fine locally and in debugging.

Could any of the other tables have an impact or effect?

Thanks for your help.

Kind regads
Darren
darrenbrook@btconnect.com

On Tuesday, April 03, 2007 8:54:53 PM Imar Spaanjaars said:
Sorry, no I don't have many other ideas. If you followed all the steps from this article, the site works fine locally and the database looks like it should, I don't know what else to suggest, except for maybe resetting the password on the remote server using a PasswordRecovery control??

Imar
On Tuesday, April 03, 2007 11:24:59 PM Darren Brook said:
Hi,

Could it be anything to do with the hosted server's permissions or IIS settings?

Would it make any difference which version of Access I am using?

Thanks,
Darren
On Wednesday, April 04, 2007 1:54:51 AM Darren Brook said:
I tried some different code in the Page_Load event.

This hasn't fixed the problem but it generates an error.

The code change was to change: -

If (Page.User.IsInRole("SkyAdmin") = True) Then

to: -

If Roles.IsUserInRole(User.Identity.Name.ToString, "SkyAdmin") = True Then

note: I've changed the role Admin to SkyAdmin

Again, locally this works fine.  The error only occurs on the remote hosted server.  I don't know how to debug that from my PC.  Not even sure I can when its a hosting company etc..

Do you know why this might work locally but then generate an error when on the hosted server?

I've also had problems with the Virtual Path being /

arggrgrrhhrghr

:-(

Going to bed now.  Hopefully tomorrow will bring answers.

Thanks,
Darren

On Wednesday, April 04, 2007 4:59:12 AM Imar Spaanjaars said:
Hi Darren,

It could be a security problem, where the account used by the web server doesn't have permissions to read or write to the database. However, I would think you'd get an error. You could try normal queries against the database.

However, like I said earlier: I am out of ideas. It's impossible for me to suggest something useful without seeing your setup, code, machine and so on.....

Cheers,

Imar
On Friday, April 06, 2007 2:40:29 PM Darren Brook said:
Hi,

I have now resolved the above problem.

I am reliably informed that the following line would need to be in the web.config file: -

[roleManager enabled="true" cacheRolesInCookie="true" defaultProvider="AccessRoleProvider"]

I can confirm that this was NOT in the web.config file but it was like this: -

[roleManager cacheRolesInCookie="true" defaultProvider="AccessRoleProvider"]

From what I am told, the enabled=true should be present if membership & roles has been enabled, and written to the web.config automatically if you've user the web admin tool.

I used the web admin tool to create the roles and users but the enabled=true bit was not added.

Anyway, it definitely works now.

Thanks.

Darren
On Tuesday, April 17, 2007 1:34:22 PM Doug said:
Excellent article !

Thanks for taking the time
On Tuesday, April 17, 2007 6:12:47 PM Imar Spaanjaars said:
Hi Doug,

You're welcome. Glad you liked it.

Imar
On Friday, May 11, 2007 2:10:06 PM Gordon Barnett said:
Hi
I searched high and low for the info you provided here and am surprised
it's not more widely available on the net considering all the newbies
would be downloading the free ASP.Net 2.0 and SQL server 2005 express
like me, only to find most hosting companies don't support Express.
The forums are choc-a-block with guys seeking out this info.

This is one of the most appreciated pieces of info I have come across in
a long time. And futhermore it's the most well written I have seen.
The step-by-step detail and structure is better than I have read in
technical books that I have paid good money for. No stone was left
unturned.

Thanks
On Saturday, May 12, 2007 8:14:31 AM Imar Spaanjaars said:
Hi Gordon,

Thank you; glad you like this article....

Imar
On Monday, June 04, 2007 12:17:55 AM Helenmary Cody said:
Hi Imar -

This is the most useful and best written article I have found on the Web in a LONG time!  Thanks very much for making it available.  
On Sunday, June 10, 2007 2:00:11 PM Mike said:
No problems for me, just a comment...I've just spent hours of frustration trying to figure out how to use MS Access to handle login adminstration in ASP.  This article was extremely helpful, very well written, and saved me a TON of frustration.  Now I'm back to getting things done without being hung up on this.

THANK YOU A TON FOR THIS ARTICLE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

http://www.spaanjaars.com/QuickDocId.aspx?quickdoc=404
On Friday, June 22, 2007 6:47:33 AM Li said:
Hi Imar
Tanks for your cool article,it's help me very much.
I'm from China,and do you mind I translate your article to Chinese?
I think this will help more people.
On Friday, June 22, 2007 11:05:45 AM Imar Spaanjaars said:
Hi Li,

Glad you like the article.

Copies / translations of my article are explicitly prohibited. That is, you're not allowed to copy the article as is, or translate it in Chinese.

I'd like to keep control over what I write, and where and how I publish it. I loose this ability when I allow translations. Sorry.

Imar
On Wednesday, July 18, 2007 11:56:33 AM basher said:
Thanks Imar for this article.

I spent days looking for a step-by-step tutorial on this stuff, and posted on forums for help... I got close to a solution, but now I have all the login/role functions working exactly as intended with Access.

;-)
On Wednesday, August 08, 2007 12:30:49 AM dbones said:
Awesome, just Awesome

bones
On Friday, August 10, 2007 7:02:24 PM Ramon Monte Mór said:
Thanks God people like you still exist.
Your article really helped me.

I live in a poor country and we are unable to pay for the expensive
SQL database.

Thank You
On Wednesday, October 10, 2007 12:37:41 AM Hernan said:
Great job... sometimes a SQL Server instance, even a Express version, is to big for small apps, making the implementation expensive and unnecesary complicated.

Thnx!
On Friday, October 12, 2007 12:21:28 PM Jan Herman Veldkamp said:
Thanks for the excellent article, very enlightening.

I was wondering if you could possibly also shed some light on the following: I am running into a problem with the SampleAccesProviders when trying to use the GetAllUsers method. I spent several hours figuring out what I was doing wrong, but then I noticed that the same problem occurs in the sample.

The sample runs fine for me, but when I add a new form with a gridview on it, which is bound to the GetAllUsers method of the membership provider I get an error caused by an unitialized class object.

Am I forgetting something, or doing something illegal, or is this possibly a bug in the provider?
On Sunday, November 04, 2007 8:41:57 AM Kleber said:
Imar, thanks for the article.
I had a problem. I followed all steps of the article but when a open my web project and after WSAT the Access Provider wasn´t there.
I´m using a Web VWD 2005 Express and the WSAT is in portuguese.

Thanks again
On Sunday, November 04, 2007 8:53:41 AM Imar Spaanjaars said:
Hi Kleber,

Sounds like a misconfiguration to me. Are you sure you registered the providers in web.config? Does the sample site work?

Imar
On Sunday, November 04, 2007 8:56:24 AM Imar Spaanjaars said:
Hi Jan Herman,

Completely overlooked your post. Are you still having problems with this? Can you write a more detailed description of the problem (like an error message and location in the code) at p2p.wrox.com and then send me the link?

Imar
On Monday, November 05, 2007 7:46:36 PM Carsten said:
Thanks for the article regarding using access as base!

I have a smalle problem in connection to login:

After login, as described, on a login-page, I can only go back to the default page, not any other page. If I fill out the destinationpageurl for the login-form, it has no effect.

Have you got an idea ?

Thanks again!

Carsten
On Tuesday, November 06, 2007 7:53:59 AM Imar Spaanjaars said:
Hi Carsten,

This is a little too off-topic for me to answer here. Can you post this on a forum like http://p2p.wrox.com?

Imar
On Saturday, November 10, 2007 1:50:00 AM Magnus Andersson said:
First a big thank you to Imar for a very nice article.

Then I want to point out something which might be interesting for some:

If you do not want to first compile the provider code to a dll file (the first step in the article, where Visual C# was used), you can just create an App_Code folder (just like you created the App_Data folder) and put the C# source code files there. They will be automatically compiled the first time the site is run.

Remember though that the folder structure has to go with them, i.e. they have to be inside the AccessProviders folder inside the Samples folder inside the App_Code, just as in the original project.

I tried all this and it worked very nicely. This also makes it easier for you to go inside those files and change things if you feel like.

Regards,
Magnus Andersson
Stockholm, Sweden
On Saturday, November 10, 2007 1:20:48 PM Magnus Andersson said:
I forgot to mention one thing above. It was in the middle of the night so I was tired.

What I forgot to mention was that when you simply add the source code files to the App_Code folder, then, inside the web.config file you need to modify the type attribute of the "providers" element. What you need to do is to simply remove the second optional parameter which is the name of the dll file (since you did not provide a dll file).

Today I also found an article on 4guysfromrolla explaining my approach as an alternative.

See: http://aspnet.4guysfromrolla.com/articles/010307-1.aspx

Regards,
Magnus Andersson
On Saturday, November 10, 2007 6:28:20 PM Imar Spaanjaars said:
Hi Magnus,

Thanks for posting this. I am sure others will benefit from this as well as it's much easier to see the code at work, debug and change it when you are only using the Express Editions as you can have everything in a single project.

Cheers,

Imar
On Tuesday, November 13, 2007 6:23:18 PM thenboo said:
Thank you very much for your article.

I have a problem. When I fire up Visual C# Express, and choose File | New Project, there is no starter kits in the visual web developer for me to
expand to.

Why?
On Tuesday, November 13, 2007 6:25:29 PM Imar Spaanjaars said:
Hi thenboo,

You need to download and install them first as explained in this article.

Imar
On Tuesday, November 13, 2007 7:13:04 PM thenboo said:
Imar,
     I have downloaded it and installed it in C:\Documents and Settings\Username\My Documents\Visual Studio 2005\Templates\ProjectTemplates\Visual Web Developer, and I keep it unzipped.
     I am just wondering why?
On Tuesday, November 13, 2007 7:30:08 PM Imar Spaanjaars said:
Hi thenboo,

What is it that you are asking? And "why do you wonder why and about what"?

Also, why did you unzip the files?

Imar
On Wednesday, November 14, 2007 5:02:44 AM thenboo said:
Hi, Imar
     I am wondering way there is no starter kits node in the visual web developer node when I fire up visual c# express and choose File | New Project.
      I keep the downloaded and installed file unzipped because you told
"You don't have to unpack this file; The format for project templates in Visual Studio 2005 is a zip file, so Visual Studio knows how to handle it."

thenboo
On Wednesday, November 14, 2007 8:48:17 AM Imar Spaanjaars said:
Hi thenboo,

I think we have some verb confusion here. Unzipped means the files are extracted, not that they remain in the zip file. So, you don't need to do that. Leave the zip as is.
So, all you need to do is download the files from the MS site, double-click the .vsi file to install it and then you should be good to go.

If that doesn't help, contact Microsoft support as something is broken.

Imar
On Monday, December 31, 2007 3:46:00 AM Scott said:
I get the error I see discussed above:

Could not load file or assembly 'SampleAccessProviders' or one of its dependencies. The system cannot find the file specified.

Everything works great on my local PC but when I publish to my isp (1&1.com) I get the error above. The app_data folder contains ASPNetDB.mdb and the bin folder contains SampleAccessProviders.dll
I confirmed security on my App_Data folder had write privlidges. I even added extra clear tags as you can see below but I still recieved the error above. How do I trouble shoot this?
On Monday, December 31, 2007 2:05:53 PM Imar Spaanjaars said:
Hi Scott,

Sounds like you need to talk to your host. Maybe they didn't configure your site correctly.

Cheers,

Imar
On Monday, December 31, 2007 7:35:35 PM Scott said:
I could really use some direction here.
What things might I suggest 1&1 check on their side?
On Tuesday, January 01, 2008 3:27:41 PM Imar Spaanjaars said:
Hi Scott,

They should check whether .NET is setup correctly with the right version, whether the Bin folder can be read and so on. Basically, they need to check whether your site is a working ASP.NET 2 web site.

Cheers,

Imar
On Wednesday, January 09, 2008 1:53:26 PM Scott said:
Still get the same error on this page:
http://www.oceanandski.com/admin/

BUT, a little progress with 1and1.com. Here is their reply:

Checking your settings, you did make /admin an application directory.
It was not executable by anyone other then the owner (so not the
webuser, fixed that).  There are not 55 lines in your web.config file
but the line that is causing the problem is the one trying to use oledb
connection to the access database.  oledb connections are not permitted
on shared hosting as referenced here

http://faq.1and1.com/scripting_languages_supported/asp_net/14.html

that does not mean you cannot use access databases.  Here are some
suggestions on alternate methods

http://faq.1and1.com/scripting_languages_supported/access_database/6.htm
l
or you could use your sql database instead of an access database.

Hope that helps.
---------------------------

This link seems to offer workarounds, which do you suggest ?
http://faq.1and1.com/scripting_languages_supported/access_database/6.html
On Friday, January 11, 2008 6:49:49 AM Imar Spaanjaars said:
Hi Scott,

It seems they do not support Access, but offer SQL Server as an alternative instead. I would go for SQL Server, it removes the need for the providers from the article completely, allowing you to work with stuff like Membership out of the box.

Cheers,

Imar
On Friday, January 11, 2008 1:57:53 PM Scott said:
They said they supported Access and to look at this link:
http://faq.1and1.com/scripting_languages_supported/access_database/6.html


If I can't use Access then all the work, database development and security design completed on this project will be lost. Further, I wouldn't be able to use the security features and related controls in Visual Studio on any new projects . I have another Access database working just fine for this web site so when they say they support Access, they do. Does that FAQ give you any idea how to approach the connection using the .dll from this post? I'd rather not give up and loose everything after coming this far.
On Friday, January 11, 2008 5:15:10 PM Imar Spaanjaars said:
Hi Scott,

They say they don't support the OleDb Provider. Since the provider uses that type, you can't use it. It's as simple as that.

The FAQ you linked to deals with classic ASP; not ASP.NET. Maybe you can rewrite the provider, but that kind of defeats its purpose.

My suggestion: change host, or simple use SQL Server for your provider stuff. As I said earlier, all of this is supported out of the box with SQL Server so you can certainly use the security features and related controls in Visual Studio on any new projects.

Additionally, it's pretty easy to upgrade an Access database to SQL Server so database development and security design is NOT lost.

Cheers,

Imar
On Monday, January 14, 2008 6:03:10 PM Mehmet Emin ÇOLAK said:
Hi
In Acessprovider forget password was not working. What can I do?
On Monday, January 14, 2008 6:22:30 PM Imar Spaanjaars said:
Hi Mehmet,

This is quite a vague question so it's impossible for me to supply an answer. The toolkit has support for resetting passwords so it *should* work. Maybe it's just an issue with the mail server?

Otherwise, try posting this on a forum like http://p2p.wrox.com

Cheers,

Imar
On Sunday, March 02, 2008 10:45:17 AM Steve said:
Great article - very clear and well written. However, would you know of similar if using Visual Basic.net. I am a teacher and teach web development to 16-18 year olds. We do VB.net at moment. I have done this with SQL but often come up against firewall issues accessing sql server from inside college. An access/VB.net version would be most appreciated if available.
On Sunday, March 02, 2008 10:54:46 AM Imar Spaanjaars said:
Hi Steve,

I am not sure what you are asking. This article *is* about using Access instead of SQL Server. Once you have compiled the C# code to an assembly, there is no need to use C# anymore, and you can use the DLL from any other .NET language like VB.NET.

Cheers,

Imar
On Saturday, March 08, 2008 1:48:03 AM Matt said:
GREAT ARTICLE!
On Monday, March 24, 2008 10:06:06 AM tina said:
thank you Imar! this article was brilliant!!!!
I spent hours trying to find a solution, being new to dev didn't make it any easier, and then I came across your article. This was clear and easy to follow even for a novice such as myself.
thanks!!!!!
On Thursday, April 10, 2008 5:30:29 AM Chi said:
Great Article.

Have you tried on the new Visual Studio 2008 with this kit?
Would this kit work with the new Visual Studio 2008?

On Thursday, April 10, 2008 6:46:05 AM Imar Spaanjaars said:
Hi Chi,

Yes it would, as VS 2008 uses the same .NET Framework 2.0 run-time under the hood.

Imar
On Thursday, April 10, 2008 6:55:45 PM Chi said:
Hi Imar,

Good to hear from you.
Instead of "unzip" the vsi files, I simply double click the vsi package and see how the start kit would be installed in my box with both Visual Studio 2005 and Visual Studio 2008 installed.
It installed two copies of "ASP.NET Access Providers.zip".  One's in "%My Documents%\Visual Studio 2005\Templates\Project Templates\C#" and the other one in "My Documents%\Visual Studio 2008\Templates\Project Templates\Visual Web Developer".
The template works in both Visual Studio 2005 & Visual Studio 2008.
It is probably because of the Visual Studio content installater that tells the vsi file where to install the templates, but it installs in both versions in a simple double clicks.
Does that mean all .net 2.0 starter kit will work correctly in visual studio 2008?

Chi
On Friday, April 11, 2008 5:48:58 AM Imar Spaanjaars said:
Hi Chi,

I don't think it automatically means all starter kits work. Why would it? And why don't you try it out an see for yourself? And why is it even relevant? You can always upgrade the resulting code...

Imar
On Wednesday, April 30, 2008 1:49:59 PM FatihTR said:
Your code is very very important for me,  you had made a good work. Thanks. I fallowed simple steps and had application working in access  membership...
On Wednesday, May 07, 2008 3:13:16 PM Steven said:
Hi Imar,

The urls to the microsoft provider pages don't work anymore. I didn't search for the correct links to the articles you refer to, but this is the link to the main provider page http://msdn.microsoft.com/en-us/asp.net/aa336558.aspx

And Thx for the article!

Steven
On Wednesday, May 07, 2008 6:51:25 PM Imar Spaanjaars said:
Hi Steven,

Thanks for the update; I have fixed the links..... I wish Microsoft would stop changing them so often; this must have been the third time I changed them....

Cheers,

Imar
On Friday, May 09, 2008 6:23:42 PM Torben said:
I have downloaded the provider sample kit numerous times (and have VS2008) but can get the project to build a solution. All I really need (as I understand it) is a ready-to-go SampleAccessProviders.dll - where can I find one?
On Saturday, May 10, 2008 8:18:25 AM Imar Spaanjaars said:
Hi Torben,

I assume you mean "can't get the project to build"? What exactly is going wrong? It shouldn't be too hard to make it work, usually.

Imar
On Saturday, May 10, 2008 9:33:52 PM Torben said:
Mailing to forums sometimes does wonders! I *did* manage to create the .dll now but at least with my version of VS2008 it ended up in obj/Debug rather than bin. All previous attempts to open the csproj.cs file (and with the forced conversion to VS2008 formats) have failed.

I still got a rootnamespace warning (when I built the solution) but in my test project (after placing the .dll in /bin, changing web.config and adding the .mdb file) everything seems to work well.

Is there anything stopping stopping you, me or someone else from just packaging these three files + a read.me to make life easier? I can't really understand why everyone has to go through the building procedure.

I have been looking for ways to solve this ever since I read about the possibility in your book but it has taken me three weeks to finally get it to work. I am truly grateful (but I still don't understand why it didnt work earlier).
On Sunday, May 11, 2008 7:41:07 AM Imar Spaanjaars said:
Hi Torben,

You normally shouldn't have so many problems; it's a matter of installing the template, creating a new project and compiling it. Hence, no need for the distribution of a DLL.
However, in your case, some things seem pretty wrong. You shouldn't get warnings and you should get the DLL on a different location. What version of Visual Studio are you using? What does it say in Help | About? Also, are you checking for the DLL using a Windows Explorer or through the Solution Explorer of VS? And what happens if you simply start a new project based on this template, make no changes at all and then hit Ctrl+Shift+B? Do you get a DLL then?

I am not sure why Microsoft isn't distributing the DLL. I think the idea for the project was to be a sample / demo project where the source would be more interesting to look at than it was to use the actual DLL.

I can send you a complete DLL (send me an e-mail through my Contact page) but it would be much better if you got it to work on your machine as it also allows you to make changes to the code and debug it at run-time if you want. I can't recompile the code anytime you need a change so you're pretty stuck with the one I can send you.

Cheers,

Imar
On Sunday, May 11, 2008 10:39:23 AM torben said:
Hello again, Imar - let me start by saying how impressed I am by the speed of your replies and your engagement in this forum. I think I can speak for all of us drawing upon your expertise - many, and great thanks.

I am running Windows XP Professional (version 5.1.2600 - Service Pack 2 build 2600) and use Visual Studio 2008 (version 9.0.21022.8 RTM) with the  Microsoft.NET Framework 3.5.

I found the .dll using Windows Explorer.

This is what I do
1) Copy the contents of AccessSampleProviders.zip into the MyWebs folder (I do have an My Web Sites folder as well, but that is the one I use)
2) Fire up VS2008
3) Use Open | Projects and locate the Access.csproj file
4) This starts the VS2008 Conversion Wizard
5) I get message All projects converted successfully
6) I see all the components (as in your Figure 3 further up on this page)
7) When pressing Ctrl+Shift+B I get *ONE* warning:

warning CS1668: Invalid search path 'C:\Program\Microsoft Visual Studio .NET 2003\SDK\v1.1\Lib\' specified in 'LIB environment variable'

and I get TWO SampleAccessProviders.dll files - one in bin/Debug, one in obj/Debug.

Again, using the SampleAccessProviders.dll, the web.config and the mdb file, everything works great locally so my problem is solved for now. Next thing will be to actually deploy something to the ISP's web server and see what happens there...
On Sunday, May 11, 2008 11:11:49 AM Imar Spaanjaars said:
Hi torben,

It's not a forum; it's just my personal web site where I publish articles and blogs, to which you can add comments....

Anyway, I think the problem lies in step 1: you shouldn't copy contents manually, but use Visual Studio's New project dialog. If you download the toolkit you end up with a .VSI file. You need to run that file in order to install a new project template. Afterwards, the project template is available through the File | New Project dialog where the item appears under the My Templates item (I believe that's what it's called).
Once you choose that, VS will create a new project for you; no need to upgrade, and no compile warnings as a result.

The warning you do get may have to do with Reference Paths settings (check your project's Properties dialog for that). Or it may have to with the "project you copy" from step 1, wherever it is that you got that from.

Bottom line: don't extract the ZIP files from the toolkit manually, but install the template and use the File | New Project dialog instead as explained at the beginning of this article.

Cheers,
Imar
On Monday, May 12, 2008 3:36:08 AM Kelly said:
Hi Imar,

Thanks for the effort you put into this blog.  I followed the steps in the article but I get the following error from the WSAT:

Could not load file or assembly 'SampleAccessProviders' or one of its dependencies. The system cannot find the file specified. (C:\Users\Katie\Documents\WebSites\WebSite4\web.config line 13)

Line 13:
type="Samples.AccessProviders.AccessMembershipProvider, SampleAccessProviders"

Thanks for your help
On Monday, May 12, 2008 10:14:12 AM Imar Spaanjaars said:
Hi Kelly,

Please search the existing comments for the error "Could not load file or assembly"; it's been reported a number of times before and it has to do with the name (or existence) of the provider DLL in your Bin folder.

Cheers,

Imar
On Saturday, May 31, 2008 5:47:03 PM An3 said:
Hi Imar,

Excellent article!!

Is it possible to delete users when the site is online. I mean without WSAT. Is there some code (in vb) available.
On Sunday, June 01, 2008 2:20:12 PM Imar Spaanjaars said:
Hi An3,

You certainly can; take a look here: http://msdn.microsoft.com/en-us/library/ms998347.aspx#paght000022_membershipapis

For more examples and background search Google for "membership API".

Cheers,

Imar
On Friday, July 04, 2008 6:42:46 PM afskm said:
Excellent Article .
But I have a problem :
every thing go right until  I clicked the Security tab and got this error :
There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem: Could not load file or assembly 'ASP.NET Access Providers' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

Thanks.
On Friday, July 04, 2008 7:52:20 PM Imar Spaanjaars said:
Hi there,

Check the comments under this article and search for "Could not load file or assembly"; it's been reported a number of times and has to do with the name of the project / assembly.

Cheers,

Imar
On Saturday, July 05, 2008 10:14:42 AM afskm said:
Thanks for the fast reply.

    But he past articls were about finding the file ( which I faced before and solved from these articls ) but this one I think is different because it finds the file but says ((The located assembly's manifest definition does not match the assembly reference))

I repeated the steps three times and no luck.
On Sunday, July 06, 2008 10:23:26 AM Imar Spaanjaars said:
Hi afskm,

In that case, I don't know what else to suggest. By following these steps, things should work as advertised. So, maybe you did miss a step, or forgot to copy a file? Is the web site you created referencing the same .DLL file that has been outputted by the C# Class Library project? And what .DLL files do you have in the Bin folder of your web site?

Imar
On Tuesday, July 15, 2008 1:53:19 PM Frances said:
Since I want to use one database as memebership control and data stored.

I changed the connection string as below in downloaded ASP.NET Access Providers1 downloaded a

add name="AccessFileName" connectionString="Driver={Microsoft Access Driver (*.mdb)};DBQ=\\dbase-svr\shared\Doc_master\RehabRequestTest.mdb"
      

compile to release dll.

I followed the instruction of your document and change the connection string in the web.config of
AccessProviders.

add name="AccessFileName" connectionString="Driver={Microsoft Access Driver (*.mdb)};DBQ=\\dbase-svr\shared\Doc_master\RehabRequestTest.mdb"
    
The following message may help in diagnosing the problem: AccessFile is not valid: C:\WebSites\AccessProviders\Driver={Microsoft Access Driver (*.MDB)}; DBQ=\\dbase-svr\shared\Doc_master\RehabRequestTest.mdb


I can add and edit data in the ms access. that mean my database connection is working fine.

Thx.
On Tuesday, July 15, 2008 1:56:55 PM Imar Spaanjaars said:
Hi there,

Yes, I am sure your connection string looks fine, but not for the provider. Look under the section titled "The connectionStrings Node" in this article. You'll see it expects just a file name.

Imar
On Tuesday, July 15, 2008 1:57:49 PM Frances said:
I understand the reason for SQL . You separate the data security access control storage and data storage. but in MS ACCESS , it seem unnercessary.

On Tuesday, July 15, 2008 2:00:19 PM Imar Spaanjaars said:
Hi Frances,

Heuh?

Imar
On Friday, August 29, 2008 1:35:28 PM Arun Bansal said:
I have found this article very useful. I would be a great thing if an example implementing the same is also added with following basic features: Restricting access to a certain directory, login controls, creating and adding users based on customized data etc. So far what I haven learnt from this article.. thanz for the same..

On Sunday, August 31, 2008 9:18:22 PM Imar Spaanjaars said:
Hi Arun,

There are many articles and books available on this topic, so I don't think it belongs here. This article deals with changing the provider; once you have done that, all the other articles and books on ASP.NET security can be applied to your site with the new provider directly.

If you want a step by step explanation of restricting access, login controls, creating users and so on, then check out my book Beginning ASP.NET 3.5 in C# and VB.NET.

Cheers,

Imar
On Monday, September 01, 2008 5:00:53 AM Arun Bansal said:
i agree. I am quite happy with what you have taught. I would be checking more of your writings.

regards,
On Monday, October 06, 2008 6:54:11 AM Stephen said:
Hi, Imar. I've followed the instructions above. Once the WSAT is open, click the Provider tab and then click the "Select a different provider for each feature (advanced)" link. --------- No Provider Created. Pls help!
On Monday, October 06, 2008 3:10:33 PM Imar Spaanjaars said:
Hi Stephen,

Are you sure you added the right configuration settings to the web.config file?

If that's already the case, can you post this on http://p2p.wrox.com? If you do, be sure to provide more information about your setup, config file and so on.

Cheers,

Imae
On Wednesday, October 29, 2008 7:57:19 PM Michael said:
Absolutely fabulous article. Saved me so much time... which I would ahve had to spent researching this on msdn.
Thanks a packet!
On Thursday, December 11, 2008 8:17:30 AM Dell Krauchi said:
QuickDocId: 404

Thank you for the "great" article/tutorial!! Very much appreciated indeed!

I think that I found a "possible" error in the notes...

In Figure 5, the name of the Project is ASP.NET Access Providers1. After the Project is saved, and checking the location of the saved project, the .DLL file is also ASP.NET Access Providers1. In the tutorial, the .DLL file is shown as SampleAccessProviders. The only way that I have found to do this is by naming the project as SampleAccessProviders (the same as the Assembly name).

In the paragraph that states: "Then, regardless of the version of Visual Studio that you are using, choose Build Solution from the main Build menu. This compiles the project into an assembly called SampleAccessProviders.dll. You'll find the assembly in the folder ASP.NET Access Providers1\bin\Release which you can find at the location where you saved the project."

I found this not to be thee case, as the the complied assembly assumes the name of the saved project. If I use the same name as the .DLL then all is OK!

Hope this helps.

Dell
On Thursday, December 11, 2008 8:28:25 AM Imar Spaanjaars said:
Hi Dell,

Looks like you missed a step. Right before Figure 4 you find this:

==========================================
The next step you need to carry out is to give a new name to the assembly created by this project. If you chose all defaults when creating the new project, the .DLL file that is being created by this project will be called ASP.NET Access Providers1.dll. The configuration file expects the file to be called SampleAccessProviders.dll so you'll need to configure the project accordingly. To do this, right-click the project in the Solution Explorer and choose Properties (alternatively, choose ASP.NET Access Providers1 Properties from the main Project menu). On the Application tab, change the Assembly name to SampleAccessProviders:
==========================================

If you follow these instructions, the DLL will be named correctly.

Cheers,

Imar
On Friday, December 12, 2008 3:21:18 PM Dell Krauchi said:
Hello,

Unless there is something "odd" on my system (which is entirely possible), it would "apprear" that in Figure 5, either the Project Name or the Solution Name is passed on to the .DLL. I have checked and checked this - as long as I name the Project and the Solution as SampleAccessProviders all is OK - else the .DLL takes on the name: ASP.NET Access Providers1.

Thank you agian, Imar.

Dell
On Friday, December 12, 2008 4:30:07 PM Dell Krauchi said:
Imar,

I am not sure if others are having the same problems as I have, but WSAT has been timing out and crashing over and over again. I have scoured the Internet for solutions but to no avail. However, your suggesstion above regarding possible "compilation errors" and adding the following default namespace references to the web.config file - this has rectified the problem.

Thnk you very, very much!

Dell
On Friday, December 12, 2008 7:24:46 PM Imar Spaanjaars said:
Hi Dell,

In Figure 4 you see how to set the Assembly Name. Once you set that name, VS compiles the project to a DLL with that name. You can then save the project or solution under whatever name you want; it should no longer affect the assembly name. The only time that happens is when you create a new project; then the default Assembly Name is named after the project.

Changing the name doesn't *rename* the DLL; it simply creates a new copy so'll you need to delete the old one manually.

Hope this helps.

Imar
On Monday, February 02, 2009 4:52:14 PM Kevin said:
Hi Imar -

Ran across this as I was trolling the web looking for Access providers.

So you know if this can be used to run Access in the 64-bit operating systems?  It sees to me that making software as useful as Windwos X64, then not including Access drivers, is proof that human evolution has come to a complete stop...

Thanks
On Monday, February 02, 2009 6:14:54 PM Imar Spaanjaars said:
Hi Kevin,

I don't know as I never tried it. Why don't you try it out and let us know?

You can always use the built-in SQL Server providers; they run fine on 64 bit versions of Windows.

Imar
On Tuesday, February 03, 2009 4:56:35 AM Kevin said:
Thank you, Imar - As soon as I can shut down for a couple of days to test the four X64 OS's -- Server, XP, Vista, and W7 -- I will try this to see if I can add an Access provider.

I was hoping you might have experience that would save me the time it takes to install/update/update/update an OS.  And I was being lazy...

  :o)



On Tuesday, February 03, 2009 7:06:32 AM Imar Spaanjaars said:
Hi Kevin,

;-) , no problem.

Looking forward to see your repsonse....

Imar
On Wednesday, May 06, 2009 12:39:05 PM sathis said:
WOW it is very useful articles i have ever seen before and talking about the explaination it is very good and really hat off to you  
On Monday, June 22, 2009 2:19:25 AM Kevin said:
This article is great! I searched for about a month now to find out exactly how this is done. I'm sure I can apply this to the Login Control using vb.net. However, I was hoping to use a pre-existing Access database that contains a username field and a password field, but also has user info like phone number and address. Is there a way to use the Login Control On_Click event to validate the username and password credentials (most likely via an SQL query) with a basic pre-existing Access database without all of the schema tables? Thanks for your help Imar!

-Kevin
On Monday, June 22, 2009 7:04:23 PM Imar Spaanjaars said:
Hi Kevin,

Yes, you can. The Login ontrol exposes a LoggingIn event (http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.login.loggingin.aspx) that you can handle to fire a custom SQL query.

Cheers,

Imar
On Friday, August 14, 2009 5:13:48 AM Marie Kerwin said:
Imar,
I cannot say THANK YOU enough for this wonderful article.  It's a miracle and you are the miracle worker.
I will look carefully through the rest of your website and use anything you write.  You are very bright and talented.  Thanks for sharing.
marie
On Wednesday, January 13, 2010 10:34:53 AM Jean-Pierre Huls said:
Imar,

Thank you for this excellent tutorial. As a newbie this is most helpfull to familiarize with providers and role-based authorization.

What would need to be changed to get this working with authentication mode "Windows"? Changing this setting in the web.config did not do the trick.

Thank you in advance.

Jean-Pierre
On Wednesday, January 13, 2010 7:19:41 PM Imar Spaanjaars said:
Hi Jean-Pierre,

In that case, you need none of what's described here. Instead, you need to use the ActiveDirectoryMembershipProvider:

http://msdn.microsoft.com/en-us/library/ms998347.aspx
http://www.google.com/search?hl=en&source=hp&q=ActiveDirectoryMembershipProvider+&aq=f&oq=&aqi=

Cheers,

Imar
On Thursday, January 14, 2010 7:34:02 AM Jean-Pierre said:
Thank you Imar for your reply.

However, using the AD providers turns out to be a no-go option. The Access alternative is a fallback option. So, if you think it possible to combine Access Role Provider and Windows Athentication, instructions for it would be most welcome.

Regs,

Jean-Pierre
On Thursday, January 14, 2010 9:38:17 AM Imar Spaanjaars said:
Hi Jean-Pierre,

Yes, AFAIK, you should be able to use them together.

Cheers,

Imar
On Monday, February 01, 2010 7:46:18 PM David said:
Hi Amar

http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=404

This is so thorough, thank you.  I've searched for several hours now but can't find the files needed to achieve using MDB for membership:

because:

http://msdn.microsoft.com/en-us/asp.net/aa336558.aspx

simply redirects to:

http://msdn.microsoft.com/en-us/aa336522.aspx

MS seem properly not to want you to do this?

Is it no longer a feasible option to use MDB do you think?  My host charges more for SQL and I'm now on .net3.5, is it worth still trying?

regards
David
On Monday, February 01, 2010 9:56:54 PM Imar Spaanjaars said:
Hi David,

Looks like Microsoft changed the links again. Can't find the Provider Toolkit homepage anymore, but I did manage to find a download link:

http://download.microsoft.com/download/5/5/b/55bc291f-4316-4fd7-9269-dbf9edbaada8/SampleAccessProviders.vsi

I also updated the download link at the beginning of this article; the other links are still broken.

Cheers,

Imar
On Thursday, February 11, 2010 9:13:05 AM WUAmin said:
Imar, thanks for the article, Very helpful.  ^_^
On Wednesday, February 24, 2010 10:32:47 PM Elizabeth said:
This was brilliant.  I needed this since I am using a  shared plan and I had already used up my one SQL Server for another project.  I stepped through this and it worked on my first try - amazing!  You have saved me hours of work.  Thank you so much for all of your help.
On Monday, March 08, 2010 1:42:52 PM Joe said:
Hi, thanks for this article.  I'm a newbie at asp.net, and I've worn out my copy of your book Beginning ASP.NET 3.5 already, so thanks for that too!

I've got the Access providers set up but in WSAT I cannot add or manage users.  When I browse to that page I get a message An error has been encountered, please try again.  I can add/manage Roles and Access Rules so I assume my db config is ok.  I'm not sure where to look to debug the problem with user admin.
On Monday, March 08, 2010 5:45:16 PM Imar Spaanjaars said:
Hi Joe,

Can it be a permissions issue? Maybe the web service account cannot write to the database?

Also, try looking in the Windows Event Viewer. Maybe that sheds some light on the actual error.

Cheers,

Imar
On Tuesday, March 09, 2010 3:26:24 AM Joe said:
Event Viewer shows an error:
The connection name 'LocalSqlServer' was not found in the applications configuration or the connection string is empty. (C:\Windows\Microsoft.NET\Framework\v2.0.50727\Config\machine.config line 141)

Each of the provider definitions in my web.config has a connectionStringName attribute that points to AccessFileName.

Seems like the provider definitions in web.config are not overriding machine.config.  I can get rid of the error by editing machine.config and renaming all of the instances of LocalSqlServer to something else, but I shouldn't have to do that.
On Tuesday, March 09, 2010 8:19:44 AM Imar Spaanjaars said:
Hi Joe,

You're right. It shouldn't have to be like that. Are you sure the site is set up correctly and the web.config is located at the root of the site? Did you reconfigure all providers or just a few?

Maybe you can post your web.config code at a forum such as http://p2p.wrox.com/index.php?referrerid=385 so I can take a look? If you do post there, please be specific and post detailed information about your setup (e.g. physical folder structure, how you open and browse to the site in Visual Studio etc

Cheers,

Imar
On Tuesday, March 16, 2010 12:51:19 PM Joe said:
I got rid of the error, although I'm not sure if this is correct either. I had left applicationName="SampleSite" in web.config. Machine.config has applicationName="/". I'm assuming that WSAT was looking for a web.config in an application called SampleSite (which I don't have), and it raised the error about LocalSqlServer not defined because it didn't find the config file? I changed applicationName in web.config="/" and WSAT doesn't raise the error anymore when I try to create users.

What is still puzzling is that even with applicationName="SampleSite", WSAT allowed me to add roles and access rules - it did not give the config error and it found and updated the Access db. It only gave the error on the add users page.
On Tuesday, March 16, 2010 2:36:59 PM Imar Spaanjaars said:
Hi Joe,

The applicationName and database shouldn't be related. The applicationName is used internally by the application services to separate one application from another while still allowing them to use the same database.

Anyway, glad to hear you got it working.

Cheers,

Imar
On Thursday, April 15, 2010 10:36:22 PM NM said:
Thanks...great article! I go working. One question. When looking at code, in AccessConnectionHolder, is the Open method releasing lock correctly ?
If _Opened is true and also after successful opening, shouldn't it release the lock ?

internal void Open(HttpContext context)
        {
            //////////////////////////////////////////
            // Step 1: Get Exclusive lock
            Monitor.Enter(this);
            if (_Opened)
                return; // Already opened

            //////////////////////////////////////////
            // Step 3: Open connection
            try
            {
                Connection.Open();
            }
            catch
            {
                // remove exclusive lock
                Monitor.Exit(this);
                throw; // re-throw the exception
            }

            _Opened = true; // Open worked!
        }

One more question. Could I define a static Application ID and name and use it ?

Thanks in advance and regards
On Friday, April 16, 2010 9:22:31 AM Imar Spaanjaars said:
Hi NM,

I haven't written this code, so I think you'll have to ask Microsoft about it.

Yes, you could use a static ID, although I don't see why you would want to.

Cheers,

Imar
On Wednesday, May 12, 2010 2:09:10 PM Dusan said:
Thanks, great manual - that's all I needed
On Monday, May 17, 2010 8:33:19 AM Otto Svedenblad said:
Hi Imar, and thank you for a very good description on how to use MS Access. I have now implemented it in a small webshop project and it works almost as clockwork. I have a problem that has been bothering me for a long time now. I need, as an administrator, to retreive the order that the customer has placed. To do that I have the customer ID (Integer) in my ordertable. When I was using the SQL-provider I used;

Customer = Membership.GetUser(
new Guid(orderRow["CustomerID"].ToString()));
CustomerProfile =
(HttpContext.Current.Profile as ProfileCommon)
.GetProfile(Customer.UserName);

to get the information, but that doesn't work since Access doesn't use Guid as UserID. All my trials on different ways to "set" the Customer variable based on the CustomerID has returned "null". I would very much appreciate some guidance on how to procced.

Best regards

Otto
On Monday, May 17, 2010 8:58:39 AM Imar Spaanjaars said:
Hi Otto,

Isn't that a matter of converting the user ID to an integer? (Provided that orderRow["CustomerID"] is an integer as well of course):

Customer = Membership.GetUser(Convert.ToInt32(orderRow["CustomerID"]));

Haven't tested it though...

Imar
On Monday, May 17, 2010 10:54:00 AM Otto Svedenblad said:
Hi Imar,

thank you for your quick responce. I tried your proposal but that didn't work either. Even if I set CustomerID to a real value (say 27) I still get "Null" as result.

"Customer = Membership.GetUser(27);"

Does the getuser function work as it is supposed to? If I look at the code in the AccessMembershipProvider it says:

public override MembershipUser GetUser(object userId, bool userIsOnline)
        {
            return null;
        }

To me that looks a little strange but maybe I have missed something here.

Otto
On Monday, May 17, 2010 10:58:06 AM Imar Spaanjaars said:
Ah, that seems to imply that GetUser is not implemented in the Access provider. You could implement it yourself with some custom SQL. Take a look at the source of the SQL provider for an idea about the implementation.

Cheers,

Imar
On Tuesday, May 18, 2010 6:32:28 AM Otto Svedenblad said:
Hi Imar,

you where right. The Getuser function was not implemented. After a little work I have now written my own procedure and it actually works. I think a small warning should be issued though, there could be a security issue here. There is a big difference between a guid and an int in a getuser environment.

Regards

Otto
On Tuesday, May 18, 2010 8:33:17 AM Imar Spaanjaars said:
Hi Otto,

Why would an int versus a Guid be a seurity issue?

Imar
On Tuesday, May 18, 2010 10:21:00 AM Otto Svedenblad said:
Hi again.

Well, it was just a thought. It must be easier to , by misstake or willingly, just type in an interger and try to find userinformation than to "invent" a GUID. But maybe that is not an issue. I must say that I am not an expert on security issues.

Otto
On Tuesday, May 18, 2010 10:26:04 AM Imar Spaanjaars said:
Hi Otto,

While Guids are indeed harder to guess, you should treat them the same as simple integers. In other words, don't do "security by obscurity". Even if a Guid is hard to guess, once it's out in the open (in the address bar, an e-mail etc) you may have security problems.

Cheers,

Imar
On Friday, August 13, 2010 3:08:06 PM Jesper said:


Hello,

I'm building a website in ASP (C#). I'm using the Microsoft Access Membership Provider. I was wondering if the userID will stay unique after removing the user or if it will be used again when creating a new user.

Hope somebody knows the answer.

Greet, Jesper
On Friday, August 13, 2010 3:10:09 PM Imar Spaanjaars said:
Hi Jesper,

Each new user gets a unique ID.

Cheers,

Imar
On Friday, August 13, 2010 3:24:30 PM Jesper said:
Thanks Imar,

I know every user gets an unique ID.
But, when I delete user with ID 23 and after, I create a new user.
The new user will get ID 23(beacaus it doesn't exist anymore) or will it get ID 24 because 23 was already used before?

(maybe you meant the second option, but I wasn't sure, so I hope you can answer ones again)

Greets,
Jesper
On Friday, August 13, 2010 3:28:41 PM Imar Spaanjaars said:
Hi Jesper,

It wouldn't be unique if a new user got the ID from a previously deleted the user..... ;-)

BTW: the best way to find out this stuff is to simply try it. Create a user, delete it, create another one and look at its ID in the database.

Imar
On Friday, August 13, 2010 3:38:58 PM Jesper said:
Thanks a lot again Imar,

Yes, I tried before, but it was a little bit strange, because I couldn't found the number of the last created ID-number anywhere in de database.. Soo how does the provider knows that an ID has been used before, after it removed...

Maybe you this answer too..
anyway..

Tanks, I can go on.
On Friday, August 13, 2010 3:42:16 PM Imar Spaanjaars said:
Hi Jesper,

The User ID is stored in aspnet_Users. The column UserId is an "AutoNumber" in Access, so the database keeps track of and hands out new IDs.

Cheers,

Imar
On Friday, September 03, 2010 2:54:58 AM Fred Maehs said:
Fantastic Imar! I made the mistake of not reading your article in it's entirety because I thought you had to be a C# person to use it ;-), but I now realize that was for the build. I have implemented this in a new site I created and it works beautifully. Not only did you explain how to get it going, but explained the why's which is often missed in subjects of this nature. Thanks again!

FM
On Saturday, September 25, 2010 8:02:04 PM Payam said:
Thanks
On Saturday, October 02, 2010 11:41:24 PM Me said:
Thanks for your hard work on this...it is a great article!
On Sunday, January 09, 2011 1:53:11 AM Jon said:
Hi Imar.  Is there an updated version of this article for ASP.NET 4 using VWD 2010?  I'm following the instructions above (taking into account a different version of the software) but an getting errors during the build:

error CS1070: The type name 'RoleProvider' could not be found. This type has been forwarded to assembly 'System.Web.ApplicationServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'. Consider adding a reference to that assembly.

I get 12 of these errors for each of the type names.

Very much appreciated.
On Sunday, January 09, 2011 11:58:53 AM Imar Spaanjaars said:
Hi Jon,

There wasn't an updated version of this article for ASP.NET 4 but I wrote one for you. You can find the article here:

http://imar.spaanjaars.com/560/using-the-microsoft-access-providers-for-membership-roles-and-profile-under-aspnet-4

Cheers,

Imar
On Sunday, January 09, 2011 6:22:31 PM Jon said:
Thanks for the super-quick response, Imar.
I went through your article, and now am able to use Login controls with an Access database.
:o)
Many thanks.
On Sunday, April 24, 2011 1:06:59 PM Enrico Engelmann said:
Hello Imar,
First of all, congratulations for your very well written article!
Nevertheless I still have a problem. Following your instructions everything works fine and I can create users and roles through the WSAT, modify them and so on. But when I try to login, I am not able to to authenticate! This with the same page I used before, with the credentials stored in the web.config file.
The only program rows in the login page are the following:

        If FormsAuthentication.Authenticate(Login1.UserName.ToString, Login1.Password.ToString) Then
            FormsAuthentication.RedirectFromLoginPage(Login1.UserName, False)
        Else
            Login1.FailureText = "Dati non corretti!"
        End If

The page contains just a Login control.

Do you have an idea where the mistake could be?

Thank you in advance!
On Sunday, April 24, 2011 3:24:18 PM Imar Spaanjaars said:
Hi Enrico,

You should take that code out as you don't need it anymore. Authentication is now handled by the Access membership provider.

Hope this helps,

Imar
On Sunday, April 24, 2011 3:58:18 PM Enrico Engelmann said:
Perfect! Now it works!
Thank you very much, also for  having been so incredibly fast in answering!
I hope to become one day an ASP.net guru like you!

On Sunday, April 24, 2011 4:04:44 PM Imar Spaanjaars said:
You're welcome....

Imar
On Thursday, September 01, 2011 9:25:06 AM shabir hakim said:

Great Explanation and worth reading article..
On Sunday, October 06, 2013 4:38:40 AM Prasad said:
Great..Finally I got it what I am searching for..
I am using Visual Studio 2008 (ASP.NET 3.5) and I am getting error..

Error 102 The type or namespace name 'Linq' does not exist in the namespace 'System' (are you missing an assembly reference?) D:\B.TECH\Project\CAP\WebSite\Default.aspx.cs 4 14 D:\...\WebSite\

Error 103 The type or namespace name 'Linq' does not exist in the namespace 'System.Xml' (are you missing an assembly reference?) D:\B.TECH\Project\CAP\WebSite\Default.aspx.cs 11 18 D:\...\WebSite\


please help..
On Sunday, October 06, 2013 4:53:49 AM Prasad said:
And I am using language C#
On Sunday, October 06, 2013 7:30:00 AM Imar Spaanjaars said:
Hi there,

I don't know where you get that error (System.Xml.Linq is not in my sample source). but try removing the using statement, or make sure your project targets ASP.NET 3.5 or later.

Cheers,

Imar
On Wednesday, February 26, 2014 3:48:36 PM Thomas said:
You have a really great article and I'm amazed with you keeping up with comments over the years!

I've used it and everything seems fine on my end, but being uneasy with all of ASP.Net, I'm not sure on what to do next.

I have an existing Access database that doesn't follow the schema provided with this tutorial and already has many customers on it.  I would like to use it, so that I can expand later when I know more about SQL Server.
On Wednesday, February 26, 2014 9:29:12 PM Imar Spaanjaars said:
Hi Thomas,

I am not sure what your question is exactly, but if it is about whether you can use this solution with your own schema or not, I think the answer is: it depends.

If the schemas are close, you can adapt the source code to match your own database schema. If there's a big difference, you may be better off building your own provider from scratch, or implement security without a provider.

Cheers,

Imar

Talk Back! Comment on Imar.Spaanjaars.Com

I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.

When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a simple calculation exercise. This means that if you want to leave a comment, you'll need to complete the calculation before you hit the Post Comment button.

If you want to object to a comment made by another visitor, be sure to contact me and I'll look into it ASAP. Don't forget to mention the page link, or the QuickDocId of the document.

For more information about the Talk Back feature, check out this news item.