How To Configure Visual InterDev to Work with an Authenticated Web Project (178215)

SUMMARY

The default setup of a Visual InterDev project will not allow both the Web server to access a file database (for example, Access or FoxPro database) and authors on remote machines to use Visual InterDev to work on the project when Active Server Pages (ASP) pages that perform database access are secured.

This article describes how to setup a Visual InterDev project so that data access works correctly from the secured pages, and individuals on other machines can use Visual InterDev to author those pages.

While this article uses an Access .mdb file as the example, the discussion applies to any file based resource.

Symptoms for this problem can appear on either the Web server or the Visual InterDev clients. The most common symptoms are as follows:

Data access fails on the ASP pages returning the error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC Microsoft Access 97 Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.
Data connection icon in a Visual InterDev project shows with a red "X."

MORE INFORMATION

The root of this problem is that the Web server and Visual InterDev clients have conflicting needs with respect to the kind of path used to reference the database.

Web Server Needs

To avoid "delegation" errors, the database must reside on the Web server, and the Web server must reference the database using a local drive letter. A UNC path cannot be used.

NOTE: The preceding comments apply when you use Microsoft Internet Information Server (IIS) 4.0 and Microsoft Access 97. If your Web server is IIS 5.0 and the database is Access 2000, and if the database file is on the Web server, you can use a UNC path to connect. You can also use a UNC path to connect to a database on a remote computer (not on the Web server), but in this case you will still have the delegation problem mentioned below and will have to use one of the workarounds for that problem.

(Delegation refers to Windows NT's inability to access resources on other machines running Windows NT when NT Challenge/Response has been used as an authentication method. For a full discussion of delegation, see the Web site noted in the "References" section.)

Client Needs

Since the database must reside on the Web server, Visual InterDev clients must reference the .mdb file using a network path (either a mapped drive letter or UNC)

The Global.asa created by Visual InterDev includes path information (DBQ=) that will use either a local or network path to the database. This will necessarily cause problems for either the server or the Visual InterDev clients.

SOLUTION

The Global.asa connection string variable must be changed so that it references only the name of a DSN. For example, change the variable Session("Conn_ConnectionString") from something like this:

   DSN=MyDSN;DBQ=C:\data\orders.mdb;DriverId=25;FIL=MS Access...

to simply this:

   DSN=MyDSN

Notice that there is no longer a DBQ in the connection string that points to the file. When the Web server reads the Global.asa, it will look on its own local system for the "MyDSN" data source and use the information it contains to access the database. Likewise, when the Visual InterDev clients read the Global.asa, they will look on their local systems for a DSN of the same name and use that information. The key is to manually create these DSN's with the appropriate information for each platform.

To create the appropriate DSN use the following steps:

Create a DSN on the Web server that uses a local path to point to the database. Create DSNs on Visual InterDev client machines that use network paths to point to the database on the Web server.
Use Visual InterDev to create a data connection. Choose the DSN on the local machine.
Manually change the ConnectionString variable in the Global.asa as shown in the example above.