I try to set a job to run a DTS package, failed.
My environment is SQL Server 2000 sp4 on windows Server 2003.
There is a local window group called Dev_accnt including member SQLTesting
who is a global account of domain Jackson. local group Dev_accnt assigned in
SQL Server with Login name: LocalMachine\Dev_accnt, type: windows group.
There is no individual Login name assigned to SQLTesting in SQL server.
I used a global account Jackson\SQL_Oper for SQL Server Agent startup
account once, then I change it to global account Jackson\SQLAgentStart and
give SQLAgentStart account with fixed servre role sysadmin, withdraw
sysadmin from Jackson\SQL_Oper. Here Jackson\SQL_Oper or
Jackson\SQLAgentStart joined in different local window group SQLOperation
with no sysadmin rol or SQLAdministrator with sysadmin role separately and
no individual Login IDs in SQL Server, but when use Jackson\SQL_Oper or
Jackson\SQLAgentStart for SQL Server Agent startup account they will be
created a individual Login IDs in SQL Server but under "Server Access"
display "via group memeber". I did not remove these 2 individual IDs from
SQL Server.
Then I use account SQLTesting log in and create package copying a table from
one database to another and create a schedule job for it.
I directly run package work fine, but I run package from its schedule job,
the job failed, no matter it is run by manual or by SQL Agent. The error as
following:
"Executed as user: Jackson\SQL_Oper. ... OnStart:
DTSStep_DTSCreateProcessTask_1 DTSRun OnError:
DTSStep_DTSCreateProcessTask_1, Error = -2147024891 (80070005) Error
string: Access is denied. Error source: Microsoft Data
Transformation Services (DTS) Package Help file: sqldts80.hlp
Help context: 1100 Error Detail Records: Error: -2147024891
(80070005); Provider Error: 0 (0) Error string: Access is denied.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp Help context: 1100
Error: -2147024891 (80070005); Provider Error: 0 (0) Error
string: Access is denied. Error source: Microsoft Data
Transformation Services (DTS) Package Help file: sqldts80.hlp
Help context: 4900 DTSRun OnFinish: DTSStep_DTSCreateProcessTask_1
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish:
DTSStep_DTSActiveScriptTask_1 DTSRun: ... Process Exit Code 1. The step
failed."
Then I remove Jackson\SQL_Oper from SQL Server and from local windows group
of window server 2003 which SQL Server installed. I run job again get error
message as following:
Executed as user: Jackson\SQL_Oper. DTSRun: Loading...
Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error
string: Login failed for user 'Jackson\SQL_Oper'. Error source:
Microsoft OLE DB Provider for SQL Server Help file: Help
context: 0. Process Exit Code 1. The step failed.
Some one has some idea about this problem?