How to get IIS 6.0 and ASP.NET modify an Access database

Posted: 29.5.2005 8.13.39 (EET/GMT+2)

Configuring an ASP.NET application on IIS 6.0 (Windows Server 2003) can be tricky, especially if your application needs to use a database, say an Access database. Reading the database works by default, but modifying it does not. So, what to do?

By default on Windows Server 2003, ASP.NET uses the IIS 6.0’s working process settings, i.e. it is running in the "Worker Process Isolation Mode." This means that you don't need to specify security settings in the XML based Machine.config file, instead you just change the settings for the application pool visually in the IIS Manager console. See the topic "Access Permissions for Web Applications" in the .NET SDK documentation for details.

Okay, now you know the very basics. Next assume you have an ASP.NET application that needs to access an Access database. By default, all is fine, since you can read the contents of the database. But when modifying it, you might run into errors such as "Operation must use an updateable query".

To get things working, you need to add some more NTFS permissions to your database file, or more specifically the directory where the .MDB file exists. This is because your ASP.NET application will also need to be able to generate (write) the lock file (.LDB).

By default, an application pool in IIS 6.0 runs under the NETWORK SERVICE low-privileged system account. You can verify this from the Identity tab of the application pool, or through the Windows Task Manager by looking at the user name of the W3WP.EXE file, the process name for an application pool.

So, to get rid of the security problems you have, open Windows Explorer, and open the properties for the directory where your database exists, and add the NETWORK SERVICE account to the list. Be sure to give it the Modify permissions, that is Read and Write rights.

This should fix the error. You can also use the following piece of ASP.NET C# code to verify the user account under which your web application is running:

<HTML>
<HEAD>
<TITLE>Test Page</TITLE>
</HEAD>
<BODY>
<H1>Test Page</H1>
<P>User is: <%= System.Security.Principal.WindowsIdentity.GetCurrent().Name %>.</P>
</BODY>
</HTML>

By default, this should display: NT AUTHORITY\NETWORK SERVICE.

Finally, you might wish to download the tool named "Internet Information Services Authentication and Access Control Diagnostics Version 1.0" (Authdiag) from Microsoft's web site. This tool might help you get your access permissions right.