I'm doing this student project which involves extracting data from a couple of Access DBs and a IBM AS400 into a SQL Server 7.0 database. I have created some DTS packages in DTS designer which will update the SQL Server 7.0; they work alright when executed manually. But when I schedule them using SQL ServerAgent Service, they fail. I have read somewhere that the ServerAgent must have an account which must have permissions and privileges to access the external databases and within the SQL Server database. I have this, but they still fail. Any suggestions?

-- Anonymous, October 15, 1999

Answers

Nigel,

Microsoft documentation offers this warning:

Note

The scheduled package will be executed by SQL Server Agent using the permissions specified during server registration. If Windows NT Authentication was used, then SQL Server Agent will attempt to load the package using its own security, which may not be sufficient.

David Rhodes (drhodes@ixl.com) offers this comment in microsoft.public.sqlserver.server:

I just spent a week with MS support on this one.. If the job owner (which may be inherited from the package account) is not an sa equiv, get ready for some strange results. Theoretically if the job owner is an sa equiv., the job runs under the same account as the SQL Agent service account. Non-sa's run under SQLAgentCmdExec (for DTS scripts). Your results will vary.. d.

Hope this helps,

Eric

-- Anonymous, October 18, 1999