Solved

How to authenticate ASPNET against an Access database?

Posted on 2010-11-18
15
547 Views
Last Modified: 2012-05-10
I’ve created a website using Visual Studio 2005 on my XP-PC and using a local Access 2003 database, it works fine. I moved the database to a server and changed the database reference to: \\server_name\database_name  and I’m getting the infamous error:

The Microsoft Jet database engine cannot open the file '\\server\databasename.  It is already opened exclusively by another user, or you need permission to view its data.

Having just created it and without Access open against it (no .ldb file present), I’m convinced it isn’t an exclusive issue.  I believe it to be a simple permission issue.  My event log records an error (eventid: 1309):  

Process information:
    Process ID: 4980
    Process name: aspnet_wp.exe
    Account name: CANADA\ASPNET

(where Canada is my machine name)

I thought it would be necessary to change the anonymous access account (in IIS 5.1) but playing around with that hasn’t helped at all; the errors in the event log continue to show the account name listed above even after restarting the website.  I’m trying to find out where the connection string is stored so that perhaps, I can make it use a “valid” account.  I’m using IE 8 from my XP-PC and looking for a solution.  Can anyone help?  Thank you!

BTW, this tiny application will be used by 1 or 2 PCs internally and it merely displays information from a table (no updates, inserts, deletes, etc.).

0
Comment
Question by:ejefferson213
  • 8
  • 6
15 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34168549
Move the DB to the website folder and try again.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 50 total points
ID: 34171917
The ASP user account will need permissions on whatever folder is hosting that Access file. It must have at least Modify permissions on that folder. The folder suggested by CodeCruiser may work, but if not then just try different folders until you discover one that has the correct permissions (or grant the folder permissions, if you have admin access to the web server).
0
 

Author Comment

by:ejefferson213
ID: 34172635
Thank you both.  First, let me explain that I'm not a developer by trade, just an administrator. Having developed this tiny code on my XP machine, I was planning to migrate it to my web server in stages (since I'm not familiar with the process).  First step was to simply test my ability to run the code against a database on a DB server (thus I'd rather not move my DB to the web server). This is the stage I'm at now that's not working. The final step was to figure out how to move my application to the web server.  

For now, I need to know how to authorize a local account (on my PC) of ASPNET to access an Access 2003 file from a DB server. I've found two different postings about how to change the userid/password of ASP in a Config file.  Another posting states that this is not the way it's done but instead you need to do it via Application Pool Identity in IIS (but I'm not at that point yet).  Still looking .....
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34173031
The ASP.NET code is run by the ASP.NET worker threads which execute under and ASPNET account credentials. You need to grant access to the shared folder for the ASPNET user on the webserver.
0
 

Author Comment

by:ejefferson213
ID: 34175497
I created a local account on both the web server and DB server and placed in the web.config file the statement:  <identity impersonate="true" userName="userid" password="password"/>  where userid and password are based on the newly created local account but I still get the same error.  Perhaps I need to restart a service or something?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34179657
Does this new user have access to the resources you are trying to access?
0
 

Author Comment

by:ejefferson213
ID: 34196333
For the moment, I moved my database to the web server and I'm still getting an error.  My asp grid has a setting pointing to the file as \\servername\c$\database\communityservices.mdb.  The event in the application log on the web server states:

Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 11/23/2010 8:20:01 AM
Event time (UTC): 11/23/2010 1:20:01 PM
Event ID: 4b8b0e4d246347a1978720edde059490
Event sequence: 104
Event occurrence: 5
Event detail code: 0
 
Application information:
    Application domain: /LM/W3SVC/1/Root/CommunityServices-2-129349908311922564
    Trust level: Full
    Application Virtual Path: /CommunityServices
    Application Path: c:\inetpub\wwwroot\CommunityServices\
    Machine name: FENDER
 
Process information:
    Process ID: 4176
    Process name: w3wp.exe
    Account name: NT AUTHORITY\NETWORK SERVICE
 
Exception information:
    Exception type: OleDbException
    Exception message: The Microsoft Jet database engine cannot open the file '\\servername\c$\database\communityservices.mdb'.  It is already opened exclusively by another user, or you need permission to view its data.
I gave Network Service full authority to the C drive without success and no other user has the database open.  Any thoughts???  Thank you!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34196525
Can you not test it by keeping the mdb in c:\inetpub\wwwroot\CommunityServices\ ?
0
 

Author Comment

by:ejefferson213
ID: 34196615
I moved it there but got the same error. The aspx code for my menu has (in part):

        <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="\\servername\c$\inetpub\wwwroot\communityservices\communityservices.mdb"
            SelectCommand="SELECT Organizations.[Organization Name], Organizations.[Organization Address], Organizations.[Organization Hours], Organizations.[Organization Phone Number1], Organizations.[Organization Requirements], Organizations.[Organization Website], Events.[Event Hot Link or Article], [Organization Types].[Organization Type]&#13;&#10;FROM Events RIGHT JOIN (Organizations LEFT JOIN [Organization Types] ON Organizations.[Organization Type] = [Organization Types].OrganizationTypeID) ON Events.[Event Sponsoring Organization ID] = Organizations.OrganizationID&#13;&#10;ORDER BY Organizations.[Organization Name];&#13;&#10;&#13;&#10;" EnableCaching="True" FilterExpression="[Organization Type]='Housing'">
        </asp:AccessDataSource>

Naturally, I need to reference the server otherwise the system would try to look for the file locally on the client. (I did this by mistake and found that out the hard way.)
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34196653
Use relative path instead of full path.

DataFile="communityservices.mdb

or

DataFile="/communityservices.mdb
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34196658
>otherwise the system would try to look for the file locally on the client

This is SERVER side code so it looks for the file on the server not the client.
0
 

Author Comment

by:ejefferson213
ID: 34196918
Thank you very much; that absolutely fixed that!!

Getting back to my original desire, how can I place this on my database/file server? I really don't want to put this database on my web server.  My file/db servers are backed up nightly, etc.  

I know I can use an absolute path to point it to the db server but how do I authorize a domain account to use it? (in what config file do I make that statement and what does it look like?)
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 450 total points
ID: 34197096
We have already tried impersonation which did not work for you. Try using a network share instead of C$ and give everyone full access to the network share.
0
 

Author Closing Comment

by:ejefferson213
ID: 34199882
That did it.  I created a network share, authorized Everyone to have access and it worked.  Thanks Again!!
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34199929
Glad to help :-)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now