I wonder if anyone can assist with this one.

I have a simple DTS package that reads data from an Access
database and inserts it into a table on the local SQL
Server. The job runs fine when I run the job from the DTS
designer, or if I use DTSRUN. But when scheduled from SQL
Server Agent, it fails at the DataPump task. Here are some
more details:

- The SQLServer service starts as a domain account (NOT a
member of local admins or domain admins) called SQLAdmin

- SQLAgent service starts as the same account as above.

- When I log onto the server as the DOMAIN\SQLAdmin, I can
run the package successfully from both DTS Designer and
the commmand line via DTSRUN

- The Owner of the SQL Agent job is DOMAIN\SQLAdmin

- The Data Transformation Task has an MS Access
connection that points to \\REMOTE\Share
Name\DataDir\sourcefile.mdb The \\REMOTE computer is a
different physical server in the same domain.

- When I log onto the SQL server as the DOMAIN\SQLAdmin, I
can perform a TYPE "\\REMOTE\Share
Name\DataDir\sourcefile.mdb" and it opens and displays the
contents of the file, so this proves I have the neccessary
permissions to open the file under this account.

- When I remove the DataPump task from the DTS package,
the job runs and succeeds from SQL Server Agent.

- The error from the DTS package that is reported in the
SQL Server Agent Job History is:
DTSStep_DTSDataPumpTask_11, Error = -2147467259
(80004005) Error string: Unspecified error
Error source: Microsoft JET Database Engine Help
file: Help context: 5000000

This is driving me potty! Any assistance appreciated.

Steve

You indicated SQLAdmin is not a member of the sysadmin role, so the job is
executing under the proxy account permissions.

269074 INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?id=269074

"The security context in which the job is run is determined by the owner of
the job. If the job is owned by a login that is not a member of the
Sysadmin server role, then the package is run under the context of the
account setup as the SQL Agent Proxy Account, and has the rights and
permissions of that account.

For SQL Agent Proxy to be able to run jobs that connect to SQL Server, the
SQL Agent Proxy account must have proper Windows/NT permissions and be
granted login access to SQL Server with appropriate database permissions.
For the jobs that execute a DTS package, the SQL Agent Proxy Account must
have read and write permissions to the temp directory of the Account the
SQL Server Agent is running under. For example,
c:\Documents and Settings\<Account>\Local Settings\Temp

If the job is owned by an account (either a SQL Server login or a Windows
NT authenticated login) that is a member of the Sysadmin role, the SQL
Agent job runs under the context of the account used to start the SQL Agent
service.

Also, if the job is owned by a Windows NT domain account and if the package
is stored in the SQL Server or SQL Server repository (not as a file), you
must start the SQL Server service by using an account from the same domain
or an account from a trusted domain. For example, if the SQL Agent job is
owned by an account from the USA domain, then the account used to start the
SQL Server service must be either from the USA domain or a domain trusted
by the USA domain. If the SQL Server is started using a local account, the
package fails to run."

Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Cindy,

The NT Domain account that the SQL Server and SQL Agent
services run under does indeed have the System
Administrators Server Role (this is looking at the
properties in Enterprise Manager for the DOMAIN\SQLAdmin
login).

Steve