SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MS Access as linked server : job fails


MS Access as linked server : job fails

Author
Message
liuc
liuc
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 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
SQL ORACLE
SQL ORACLE
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6799 Visits: 1314
How about to change the execution account to others, such as local account?
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29343 Visits: 19002
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?
Kurt W. Zimmerman
Kurt W. Zimmerman
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2608 Visits: 1400
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
liuc
liuc
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 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.
VA123
VA123
SSC Eights!
SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)

Group: General Forum Members
Points: 910 Visits: 2390
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.
liuc
liuc
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 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
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29343 Visits: 19002
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?
Key DBA
Key DBA
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1699 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
Timothy Ford-473880
Timothy Ford-473880
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1667 Visits: 446
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search