Solved

# Using ASP, access an ACCESS DB on a remote server.

Posted on 2005-04-25
571 Views
My web server (sitting on my DMZ) is running IIS 6.0 with a homegrown ASP for employment applications.  All data is being stored is within an ACCESS mdb.  All works well within this app including the ability to access this mdb from inside my network using MS ACCESS.

I am having a problem, however, trying to access this mdb through my Intranet server which resides inside my firewall.  I've tried the following commands which have not worked:

mydsn="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=g:\employment_app.mdb" ' G is a mapped drive on the Intranet server

mydsn="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=\\webserver\enterprisedata\employment_app.mdb" ' enterprisedata is a share on the webserver

mydsn="DRIVER={Microsoft Access Driver (*.mdb)}; Data Source=ApplicationDB" ' applicationdb is a dsn created on the intranet server

mydsn="DSN=ApplicationDB"  ' applicationdb is a dsn created on the intranet server

mydsn = "Provider=MS Remote;" &_
"Remote Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=e:\webserverpath\employment_app.mdb;"

Thanks
0
Question by:jmuehlbauer

LVL 8

Expert Comment

I see a couple quick possibilities.

1.   Security issues.   By Default, IIS sets itself up to use a security account, IUSR_<Machine Name>.

-   If IUSR_<> does not have write priviledges to the Access DB, then your connection with it will fail.

-   When using MS Access Databases,  Access will use the windows Temp directory, for scratch disk space.   You IUSR Account must have read/write access to that as well.

2.  A Share is user level.   Which means it's not there if your logged off.  I'm pretty sure that IUSR would not have access to it.

******************************
here is a little insight on how the servers work.  I have a feeling that your setup might be based on mis information.

I presume you have the database located elsewhere because you don't want the resources taken up on the web server.
*** The server with the file on it is only acting as a file server.   You could have your access database sitting on a Linux Samba Share, and your web server would still get to it fine.  Your web server has to load the Access DB into memory and do all the processing from its side.    - If it is possible to have the Access DB on the web server, that would be the best for your resources.

******************************
If it is nessacary that multiple machines be able to access this database at once, then I would recommend not going with an access database.  Those are really only effective on small systems with 1 connection.  Multiple connections to a file based DB can be harsh on the integrity of your DB.   I would recommend upgrading to Either MS SQL 2K (because it has wizards, and a fairly seemless conversion) if the costs are acceptable, or an MySQL DB, because it is free and pretty much as powerful as MS SQL 2K
0

Author Comment

The ACCESS DB is on my web server (IIS 6.0) and I'm trying to access it from my Intranet server (IIS 5.0).  If I try to access the DB using a DSN on the requesting IIS server, I get the following response:

MYDSN = [DSN=ApplicationDB]

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access 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.

/web/Employment_App/employmentapplist.asp, line 56

If I try to access the DB using the exact path on the requesting IIS server, I get the following response:

MYDSN = [DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=\\webserver\enterprisedata\employment_app.mdb]

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x46c Thread 0x834 DBC 0x2215dec Jet'.

/web/Employment_App/employmentapplist.asp, line 56

And, if I try to access the DB using the remote path statement I get the following:

MYDSN = [Provider=MS Remote;Remote Server=http://<69.7.213.35>;Remote Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\accweb\fpdb\employment_app.mdb;]

Internet Client Error: Cannot Connect to Server.

/web/Employment_App/employmentapplist.asp, line 56

I've given the IUSR_<Machine Name> user write access to the folder which the MDB resides and the C:\TEMP folder as well.

What am I missing?
Thanks
0

LVL 8

Expert Comment

'Temporary (volatile) Jet DSN for process 0x46c Thread 0x834 DBC 0x2215dec Jet'

This is most likely caused because the Windows temp directory is not read/write friendly to the IUSR Account.  You must find the folder in explorer, and add the IUSR Account to be able to read and write to it.

The first error is caused by 1 of two things.

1.   Some one has the access db open to a table editing page, or query editing page.

0

LVL 8

Expert Comment

****Since you are trying to read this file from the file share, you whould know this...

1.  You have two different machines.  I can see from your code that one is called WebServer.  I'll make up a name for the one your site is on (dev1)

There are multiple IUSR Accounts.

The first is IUSR_WebServer  This is on the machine that has the Database.  This IUSR Account Does not matter.

The Second is IUSR_dev1.  This is on the machine with the errored site.  You have to make sure that this user has access to the Database/Directory, and the windows temp directory.

If you don't have these machines linked together on a domain then I suggest this.   Make a new user on your computer (from the Computer Manager (administrative panel)) called SiteUser.  Do this on Both machines.   Give them the same passwords.

On WebServer, Make sure that SiteUser has Read-write access on the folder itself, and the Shared permissions of EnterPriseData, (where the MDB is located).

On the local server, (dev1?) make sure that SiteUser Has read/Write Access to your Website folder, possible c:\inetpub\wwwroot.  Also make sure that SiteUser has read/write permissions to the Windows Temp directory.   Then from your iis control panel, (found in Computer management from administrative tools)  Go to the properties of your website, and go to the directory security tab.   The first thing it will show you is Allow anonymous usage.   There is a button to select the user.  Select the SiteUser, and give it the password.

Once this is done, All should work without issue, particularly using

MYDSN = [DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=\\webserver\enterprisedata\employment_app.mdb]
0

Author Comment

OK; I've tried all the above and nothing seems to work.  I've given account IUSR_DEV1 r/w access to WEBSERVER db and temp directories.  The account pw and the pw inside the IIS anonymous tab are the same.  I even changed the share to give r/w access to the account.  I still get the error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access 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.

This makes no sence to me; I've gone through all possible steps and still, i can't talk to the DB.

As I said earlier, my webserver is sitting on the DMZ and has a different IP address range than my internal network.  It is, however, on my windows (AD) network and is listed in my DNS as WEBSERVER.  AD authentication works fine between both networks.

Could this be causing a problem?
0

Author Comment

It sure seems like I have a protection/privledge issue.  I can access the ACCESS db that resides within my Intranet site by using the following code:

mydsn="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath(".") & "..\..\fpdb\employment_app_rem.mdb"

However, as I said before, I can't access the ACCESS db on a remote server using the following code:

mydsn="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=\\webserver\enterprisedata$\employment_app.mdb" I've tried one additional thing to solve this problem. Inside the db "employment_app_rem.mdb", I created a link to the tables in "\\webserver\enterprisedata\employment_app.mdb". I can open this db and view the contents of the linked tables using ACCESS but when I try to access the db through my ASP I get the following error: MYDSN = [DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=C:\<dev1>\wwwroot\web\Employment_App..\..\fpdb\employment_app_rem.mdb] Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '\\<webserver>\enterprisedata$\Employment_App.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

I've attempted adjusting privledges on <webserver> with no success.  I'm not sure what's missing or what privledge should be granted.

0

Accepted Solution

PAQed with points refunded (500)

CetusMOD
Community Support Moderator
0

## Featured Post

### Suggested Solutions

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…