[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2005-04-25
9
Medium Priority
?
579 Views
Last Modified: 2008-01-09
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 Server=http://<webserver ip address>;" &_  
        "Remote Provider=Microsoft.Jet.OLEDB.4.0;" &_  
        "Data Source=e:\webserverpath\employment_app.mdb;"  

Any help you can give would be greatly appreciated.
Thanks
0
Comment
Question by:jmuehlbauer
  • 3
  • 3
7 Comments
 
LVL 8

Expert Comment

by:hpdvs2
ID: 13860588
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

by:jmuehlbauer
ID: 13860835
Thanks for your response'

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;]

    Microsoft ADO/RDS error '80072ee7'
    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

by:hpdvs2
ID: 13861018
'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.  

2.  (More likely)  You only have read access to it, if that.

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 8

Expert Comment

by:hpdvs2
ID: 13861127
****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

by:jmuehlbauer
ID: 13861926
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?
Thank you for your help.
0
 

Author Comment

by:jmuehlbauer
ID: 13875555
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.

Any additional help you could give would be appreciated.
0
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 16255286
PAQed with points refunded (500)

CetusMOD
Community Support Moderator
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month18 days, 12 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question