Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

MS Access as linked server : job fails Expand / Collapse
Author
Message
Posted Tuesday, March 4, 2008 9:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 13, 2012 4:07 AM
Points: 19, Visits: 100
Hi, i created a job to execute a stored on a sqlserver2005 db retrieving data from a remote msaccesss db.
When i run the job i get this error (in italian):
03/04/2008 16:55:23,provaImportPatenti,Error,1,PREFNO,provaImportPatenti,importPatenti,,Executed as user: NT AUTHORITY\SYSTEM. Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ProvaPatenti". [SQLSTATE 42000] (Error 7303) OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ProvaPatenti" returned message ""Z:\patenti 2006.mdb" non è un percorso valido. Assicurarsi che il nome del percorso sia corretto e di essere collegati al server in cui si trova il file.". [SQLSTATE 01000] (Error 7412). The step failed.,00:00:00,16,7412,,,,0

I think is a matter of permission, but i don't know how to solve it.
The remote db has no password
On the linked server i set "sa" as local login and "impersonate".
Do i have to change somthing?
thnks
Post #463860
Posted Tuesday, March 4, 2008 10:15 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 5, 2013 4:43 PM
Points: 1,473, Visits: 1,314
How about to change the execution account to others, such as local account?
Post #463905
Posted Tuesday, March 4, 2008 10:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 7,064, Visits: 15,277
If my italian isn't entirely shot - it's stating that the path isn't correct. Meaning - it doesn't understand what "Z:" is under the credentials executing the query.

Try changing the Z: to the UNC path that it maps to instead.

in other words, something looking like: "\\servername\sharename\patenti 2006.MDB"

If you still get an error, then it will be a permissions error. that means that the SQL Server service account wouldn't have access to that share (the account used to start up the SQL service), so you'd need to give it access to that remote share.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #463928
Posted Tuesday, March 4, 2008 11:35 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:57 AM
Points: 986, Visits: 1,327
Matt Miller (3/4/2008)
If my italian isn't entirely shot - it's stating that the path isn't correct. Meaning - it doesn't understand what "Z:" is under the credentials executing the query.

Try changing the Z: to the UNC path that it maps to instead.

in other words, something looking like: "\\servername\sharename\patenti 2006.MDB"

If you still get an error, then it will be a permissions error. that means that the SQL Server service account wouldn't have access to that share (the account used to start up the SQL service), so you'd need to give it access to that remote share.


I would agree. Keep in mind that if you run a job from the job queue you will not have access to any mapped drives you have when you log in. This is a common problem. Another common problem is authentication. I found that I get myself into trouble if I try to rely on integrated security. If you use standard security (always supplying a userid/password) a bulk of your problems go away.

Good luck.

Report back your findings.

Kurt
DBA
RHWI, Inc
Poughkeepsie, NY


Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #463961
Posted Wednesday, March 5, 2008 1:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 13, 2012 4:07 AM
Points: 19, Visits: 100
Hi, thanks for your posts.
First of all i deleted my linked server and executed these lines of code to add another one :

EXEC sp_addlinkedserver
@server = N'provaPatenti',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'OLE DB Provider for Jet',
@datasrc = N'\\server2\patenti\patenti 2006.mdb'
GO

-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N'provaPatenti',
@useself = N'false',
@rmtuser = N'Admin',
@rmtpassword = ''
GO

When i run a query on it it works well.
When i execute the job (that simply call the same query tested before) i get a different error (can't open file):
03/05/2008 08:43:15,provaImport,Error,1,PREFNO,provaImport,importPatenti,,Executed as user: NT AUTHORITY\SYSTEM. Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ProvaPatenti". [SQLSTATE 42000] (Error 7303) OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ProvaPatenti" returned message "Il modulo di gestione di database Microsoft Jet non è in grado di aprire il file '\\server2\patenti\Patenti 2003.mdb'. Il file è già aperto con accesso esclusivo da un altro utente o è necessario disporre dell'autorizzazione per visualizzare i dati.". [SQLSTATE 01000] (Error 7412). The step failed.,00:00:01,16,7412,,,,0

At this point it must be a permission problem.
What i didn't say before, and probably is important, the "patenti" folder on the server requires a username and a password to access it on the intranet. If the connection is not established before, even the call to the query out of the job fails (the job fails anyway either the connection is established or not).
Thank you very much.
Post #464255
Posted Wednesday, March 5, 2008 5:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 216, Visits: 2,083
SQL ORACLE (3/4/2008)
How about to change the execution account to others, such as local account?


Try this.

1. Open the SQL Server Configuration Manager.
2. For the SQL Server Agent process, change the 'Log On As' from Local System to a local account.
Post #464334
Posted Wednesday, March 5, 2008 7:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 13, 2012 4:07 AM
Points: 19, Visits: 100
i changed the sql server agent log on "this account". but nothing changed.
Which account do i have to specify in this context? any of the local machine (server) ?
I created an account with the same username and password of the account nedded to access the remote folder where the access db is located. But i get the same error.
Thanks
Post #464419
Posted Wednesday, March 5, 2008 7:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 7,064, Visits: 15,277
Liuc -

you're correct - you now need to get permissions to that network folder for the account that is being used to start SQL Server. You may unfortunately need to change to a domain account (from a local account) in order to do so.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #464420
Posted Thursday, March 6, 2008 7:43 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 26, 2009 3:02 PM
Points: 515, Visits: 655
I agree with Matt. A Domain Account that executes (and owns) the Job, is configured to use the Linked Server and has access to the MDB file as well as the Folder where the MDB file is stored should make everything work. Keep us posted.



"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
Post #465155
Posted Thursday, March 6, 2008 8:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 11, 2014 10:59 AM
Points: 1,019, Visits: 442
Another thing to think about is this: Access has a default login of 'Admin' with no pwd. Try that combination for the security in your linked server and try again.

- Tim Ford, SQL Server MVP
http://www.sqlcruise.com
http://www.thesqlagentman.com
http://www.linkedin.com/in/timothyford
Post #465181
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse