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\De v_accnt, type: windows group.
There is no individual Login name assigned to SQLTesting in SQL server.
I used a global account Jackson\SQL_Ope r for SQL Server Agent startup
account once, then I change it to global account Jackson\SQLAgen tStart and
give SQLAgentStart account with fixed servre role sysadmin, withdraw
sysadmin from Jackson\SQL_Ope r. Here Jackson\SQL_Ope r or
Jackson\SQLAgen tStart joined in different local window group SQLOperation
with no sysadmin rol or SQLAdministrato r with sysadmin role separately and
no individual Login IDs in SQL Server, but when use Jackson\SQL_Ope r or
Jackson\SQLAgen tStart 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_Ope r. ... OnStart:
DTSStep_DTSCrea teProcessTask_1 DTSRun OnError:
DTSStep_DTSCrea teProcessTask_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_DTSCrea teProcessTask_1
DTSRun OnStart: DTSStep_DTSActi veScriptTask_1 DTSRun OnFinish:
DTSStep_DTSActi veScriptTask_1 DTSRun: ... Process Exit Code 1. The step
failed."
Then I remove Jackson\SQL_Ope r 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_Ope r. DTSRun: Loading...
Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error
string: Login failed for user 'Jackson\SQL_Op er'. 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?
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\De v_accnt, type: windows group.
There is no individual Login name assigned to SQLTesting in SQL server.
I used a global account Jackson\SQL_Ope r for SQL Server Agent startup
account once, then I change it to global account Jackson\SQLAgen tStart and
give SQLAgentStart account with fixed servre role sysadmin, withdraw
sysadmin from Jackson\SQL_Ope r. Here Jackson\SQL_Ope r or
Jackson\SQLAgen tStart joined in different local window group SQLOperation
with no sysadmin rol or SQLAdministrato r with sysadmin role separately and
no individual Login IDs in SQL Server, but when use Jackson\SQL_Ope r or
Jackson\SQLAgen tStart 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_Ope r. ... OnStart:
DTSStep_DTSCrea teProcessTask_1 DTSRun OnError:
DTSStep_DTSCrea teProcessTask_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_DTSCrea teProcessTask_1
DTSRun OnStart: DTSStep_DTSActi veScriptTask_1 DTSRun OnFinish:
DTSStep_DTSActi veScriptTask_1 DTSRun: ... Process Exit Code 1. The step
failed."
Then I remove Jackson\SQL_Ope r 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_Ope r. DTSRun: Loading...
Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error
string: Login failed for user 'Jackson\SQL_Op er'. 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?