SQL Server Performance Forum – Threads Archive

Execute Package but not Job

I have a package that uses a data driven query to export data from an extended stored procedure to an Access database that resides on a different server. When I execute the package it works. I then scheduled the package, and when run as a job it fails saying: "Error string: The Microsoft Jet database engine cannot open the file ‘\serverfolderMyAccess.mdb’. It is already opened exclusively by another user, or you need permission to view its data." Any idea why I can execute the package, but not the job?

---

When you execute the package, you are running as ‘you’ with your permissions. When executed from a job, it is the agent account that its running from. What the error message tells me is that the agent account does not have permission to that directory/file. Chris

---

[quoteWhat the error message tells me is that the agent account does not have permission to that directory/file. Thanks Chris. I see there is a service called SQLServerAgent. Is there a way to tell the name of the account being used to run this failed job?

---

For the easiest way:
look at the job history, then expand for details, then select step 1 – the first line in the messages box should start with Executed as user: This will let you know

---

It says it was executed by [server name]System. I cannot see where that is a user defined user, so how do I grant permissions to "System"?

---

In addition to what Chris referred, any jobs which require access to network resources will run under the context of the user account specified for SQLSERVERAGENT. This is a significant point when trying to diagnose why a xp_cmdshell command or DTS package runs when manually executed and fails when run through a job. If this is the case, the usual culprit is the user account which SQLSERVERAGENT runs under does not have sufficient permissions to access the resources required. So use a domain account with required privileges to start the SQLAgent service and then schedule the DTS package. KBAhttp://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 about scheduling the DTS package as a job and other information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Thanks Satya. Great article. The one thing I am still not clear is the user account or local account running the SQLSERVERAGENT. When I open the SQLSERVERAGENT service it says "login as local system account". What is the local system account or what user account is used when the local system account is the one logging the service in?

---

-The local system account does not require a password, does not have network access rights in Windows NT 4.0 and Windows 2000, and restricts your SQL Server installation from interacting with other servers. A domain user account uses Windows Authentication to set up and connect to SQL Server. By default, account information appears for the domain user account currently logged on to the computer. The local system and local user accounts do not have network access rights. Using a local account restricts SQL Server from interacting with other servers. Some server-to-server activities can be performed only with a domain user account. For example: -Remote procedure calls (RPCs)
-Replication
-Backing up to network drives
-Heterogeneous joins that involve remote data sources
-SQL Server Agent mail features and SQL Mail Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>