ADO.NET 3.5 Cookbook, 2nd Edition

1.12. Connecting to a Microsoft Access Database from ASP.NET

Problem

You know your connection string is correct, but still can't connect to your Microsoft Access database from your ASP.NET application. What are the differences between connecting from a Windows Forms .NET application and an ASP.NET application?

Solution

You must grant the necessary file permissions for accessing a Microsoft Access database engine to the default user account used by ASP.NET.

1. Begin by creating a new ASP.NET Web Application project named ConnectMSAccessDataAspNet.

2. Add the following elements to the Web.config file within the <system.web> element:

   	<authentication mode="Windows" />
   	<identity impersonate="true" />

3. Add a connection string to Northwind 2007.accdbs on the local machine to the configuration file Web.config by adding the following <connectionStrings> element within the <configuration> element:

   	<connectionStrings>
   	    <add name="Northwind2007" providerName="System.Data.OleDb"
   	       connectionString="Provider=Microsoft.ACE.OLEDB.12.0;
   	           Data Source=C:\Documents and Settings\bill\My Documents\
   	           Northwind 2007.accdb;"/>
   	</connectionStrings>

The C# code Default.aspx.cs in the project ConnectMSAccessDataAspNet is shown in Example 1-13.

Example 1-13. File: Default.aspx.cs for ConnectMSAccessDataAspNet solution

using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;

namespace ConnectMSAccessDataAspNet
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string sqlText = "SELECT TOP 10 * FROM Customers";
            string connectString =
                ConfigurationManager.ConnectionStrings[
                "Northwind2007"].ConnectionString;
            DataTable dt = new DataTable(  );
            OleDbDataAdapter da =
                new OleDbDataAdapter(sqlText, connectString);
            da.Fill(dt);

            foreach (DataRow row in dt.Rows)
                Response.Write(row["ID"] + " - " + row["Last Name"] +
                    ", " + row["First Name"] + "<br/>");
        }
    }
}

The output is shown in Figure 1-12.

Figure 1-12. Output for ConnectMSAccessAspNet solution

Discussion

When a user retrieves a page from an ASP.NET web site, code runs on the server to generate and deliver the page. By default, IIS (Internet Information Server) uses the system account to provide the security context for all processes. This account can access the IIS computer, but is not allowed to access network shares on other computers.

To allow an ASP.NET application to connect to a Microsoft Access database, IIS must be configured to use an account other than the system account. The new account must be configured to have permission to access all files and folders needed to use the Access database. If the Access database is on a remote computer, the account also requires access to that computer.

The following sections describe how to configure the IIS Server and the Access computer to allow ASP.NET to connect to an Access database.

Configure IIS

The system account cannot authenticate across a network. Enable impersonation in the web.config file for a given ASP.NET application so that ASP.NET impersonates an account on the Microsoft Access computer with the required access permissions to the Access database. For example:

	<identity impersonate="true" userName="domain\username"
	    password="myPassword" />

This method stores the username and password in clear text on the server. Ensure that IIS is configured to prevent users of the web site from viewing the contents of the web.config file—this is the default configuration.

The Microsoft Access database engine uses the TEMP folder on the IIS computer that is accessing the Access database. The user identity requires NTFS (Windows NT File System) full-control permissions on the TEMP folder. Ensure that the TEMP and TMP environment variables are properly configured.

Configure the Access computer

On the Access computer, the user account that is used to access the database requires Read, Write, Execute, and Change permissions on the database file. The user identity needs Read, Write, Execute, Delete, and Change permissions on the folder containing the database files. The user account requires permissions to access the share that contains the database file and folders.

The user account must be recognized by the Access computer. For a domain user account, add it to the permissions list on both computers. For a user account local to the IIS computer, create a duplicate account on the Access computer with the same name and password.

Grant the user account "Log on Locally and Access this Computer from the Network" permission to access the computer in the local security policy. These permissions are assigned within the Security Settings\Local Policies\User Rights Assignment node in the Local Security Policy tool.