accessing an Access db on another server, using sql agent job

Question

accessing an Access db on another server, using sql agent job

oldgoat

SSCrazy

Points: 2344
More actions
September 14, 2006 at 6:53 am

#67639

This has come up since we moved a database to a new server.
The Access 2k db is a linked server, via a URI path. The sql agent job has to read the db and copy its content to the sql 2k db on 'localhost'.
Using the login that the sql service runs under, I get
"Server: Msg 7399, Level 16, State 1, Procedure cn_updateitd, Line 9
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\\COUNCILNET\G$\ITD\ITD.MDB'. It is already opened exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ]."
Running the same under my own domain admin login, it works perfectly.
The MSKB makes mention of the temp folder used by the service account for running such jobs, but altering the permissions on this has made no difference.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Oh yes, sure, it will run for sysadmins....

I have a similar problem with one of my apps where I coded something that opens 3 connections to MS ACCESS database from the website. It works nice on my development server. even better on my test server, it just gives this exclusive connection error on the production server. I did everything: the database, the ODBC sourc and my code set everything to multi-user access, not exclusive.

The problem here according to the Microsoft that the user who opens the database first should have WRITE access to the parent folder of the database file to create a lock file with extension ldb. If this file can not be created the database opens in Exclusive mode.

So I would check who is accessing the MS ACCESS database file and give him/her/it WRITE permissions to the folder of this file.

OR I would make sure that ONLY one connection opens at the time.

Let me know if you need references, I will look them up.

Regards,Yelena Varsha

oldgoat SSCrazy Points: 2344 More actions · Answer 2

oldgoat

SSCrazy

Points: 2344

September 15, 2006 at 4:35 am

#660645

would be handy, yes. Thanks

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

http://support.microsoft.com/kb/174943/EN-US/

PRB: 80004005 "Couldn't Use '(unknown)'; File Already in Use"

Oledb and ODBC could return differently worded messages, also message may vary based on the access type: from the website or from other sources like you have from SQL Server.

The second message in this article is like yours:

"Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [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. "

So this article says the following. See the STATUS part too 🙂

CAUSE

This is a Windows NT permissions problem. The account that is accessing the page doesn't have sufficient permissions to lock the database. The account would be either the anonymous account (by default the IUSR_<machinename&gt

or a specific user account if the page has been secured for authenticated access.

RESOLUTION

If the page is meant to be accessed anonymously, give the IUSR_<machinename> account full control to the ASP page, and the folder and files where the database is located. Additionally, if the path to the database is being referenced using UNC (\\Server\Share), ensure that the Share Permissions allow the IUSR_<machinename> account full access. This step applies even if the share is on the local Web server.

If the page has been secured such that the IUSR_<machinename> can't access the file or database, then ensure that the user's account that is being authenticated has full control to the appropriate folders and files.

At a minimum, the account being used needs Create, Destroy, Read, Write to work with the .ldb file. Specific permission can be applied to the database to limit access.

STATUS

This behavior is by design.

Regards,Yelena Varsha

oldgoat SSCrazy Points: 2344 More actions · Answer 4

Thanks. Whilst the issue wasn't around web-based access, that gave me the right pointer, as I discovered that the 'network access' perms was the issue - a subtle difference from the routine security stuff, but I'm getting used to these irritations with w2k3.