DTS Package fails when scheduled on server as SQL Server Agent job

DTS Package fails when scheduled on server as SQL Server Agent job

Post by JB » Thu, 17 Apr 2003 17:28:31



I have created a simple DTS package to copy a database
from our production server to a development box on a
nightly basis.
The production server's name is:  
        BLUEBOX

I created the DTS package (and scheduled it) while logged
into the server using Terminal Services. The Windows
account I log on as is:
        BLUEBOX\jmb-sg

The DTS package has just one task: "Copy SQL Server
Objects". The settings for this task are:
        Source database:       (local), using Windows
Authentication
        Destination database:   xx.xxx.xx.xx    (I.P. of
dev box),  using an SQL account on that box

The DTS package properties are:
        Owner/Creator:  BLUEBOX\jmb-sg
        Computer:       BLUEBOX

The "sqlservr.exe" process runs under a dedicated Windows
account:        BLUEBOX\sqlworker
The "sqlagent.exe" process runs under the same Windows
account:        BLUEBOX\sqlworker

The SQL Server Agent job (scheduled to run the above DTS
package once per night) has the owner:
        Owner:          BLUEBOX\sqlworker

Currently, the job has failed each night, producting the
following detailed error message:

Executed as user: BLUEBOX\sqlworker. ...ing...   DTSRun
OnStart:  DTSStep_DTSTransferObjectsTask_1   DTSRun
OnProgress:  DTSStep_DTSTransferObjectsTask_1; Scripting
objects for Transfer; PercentComplete = 0; ProgressCount
= 0   DTSRun OnError:  DTSStep_DTSTransferObjectsTask_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:  5700        
Error:  -2147467259 (800040.  The step failed.

The server is a dual-processor Compaq running a Windows
2000 Server OS and SQL Server 2000. It is behind a
firewall. After creating the job, I ran it manually
(while still logged in via Terminal Services as
BLUEBOX\jmb-sg), and the job completed successfully while
I waited. In the past week, I have read many newsgroup
threads, including in this forum, and have of course read
the KB article 269074 many times over. At this point I
haven't solved the issue, and would greatly appreciate
your expert guidance. I've noticed that a few people have
commented on related issues in the past, especially Allan
Mitchell, and so hope that with the information I've
provided we can figure out what's going on!

Thank you in advance,
JB

 
 
 

DTS Package fails when scheduled on server as SQL Server Agent job

Post by Allan Mitchel » Thu, 17 Apr 2003 17:50:40


The error

-2147024891 (80070005)

means

General access denied error

If you log into the box as the SQL Server Agent account and execute it
interactively as you have just done logged in as you what happens.  This
will highlight the Agent being the problem.  If you have made sure that the
article
http://support.microsoft.com/?kbid=269074 does not apply to you.

In the package enable logging to a text file.

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


Quote:> I have created a simple DTS package to copy a database
> from our production server to a development box on a
> nightly basis.
> The production server's name is:
> BLUEBOX

> I created the DTS package (and scheduled it) while logged
> into the server using Terminal Services. The Windows
> account I log on as is:
> BLUEBOX\jmb-sg

> The DTS package has just one task: "Copy SQL Server
> Objects". The settings for this task are:
> Source database:       (local), using Windows
> Authentication
>    Destination database: xx.xxx.xx.xx (I.P. of
> dev box),  using an SQL account on that box

> The DTS package properties are:
> Owner/Creator: BLUEBOX\jmb-sg
> Computer: BLUEBOX

> The "sqlservr.exe" process runs under a dedicated Windows
> account: BLUEBOX\sqlworker
> The "sqlagent.exe" process runs under the same Windows
> account: BLUEBOX\sqlworker

> The SQL Server Agent job (scheduled to run the above DTS
> package once per night) has the owner:
> Owner: BLUEBOX\sqlworker

> Currently, the job has failed each night, producting the
> following detailed error message:

> Executed as user: BLUEBOX\sqlworker. ...ing...   DTSRun
> OnStart:  DTSStep_DTSTransferObjectsTask_1   DTSRun
> OnProgress:  DTSStep_DTSTransferObjectsTask_1; Scripting
> objects for Transfer; PercentComplete = 0; ProgressCount
> = 0   DTSRun OnError:  DTSStep_DTSTransferObjectsTask_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:  5700
> Error:  -2147467259 (800040.  The step failed.

> The server is a dual-processor Compaq running a Windows
> 2000 Server OS and SQL Server 2000. It is behind a
> firewall. After creating the job, I ran it manually
> (while still logged in via Terminal Services as
> BLUEBOX\jmb-sg), and the job completed successfully while
> I waited. In the past week, I have read many newsgroup
> threads, including in this forum, and have of course read
> the KB article 269074 many times over. At this point I
> haven't solved the issue, and would greatly appreciate
> your expert guidance. I've noticed that a few people have
> commented on related issues in the past, especially Allan
> Mitchell, and so hope that with the information I've
> provided we can figure out what's going on!

> Thank you in advance,
> JB


 
 
 

DTS Package fails when scheduled on server as SQL Server Agent job

Post by JB » Thu, 17 Apr 2003 18:26:07


Thank you for the quick response.

I have setup the job to log errors to a text file, as you
suggest.

Let me clarify the matter regarding KB article 269074: I
do not know, or believe for that matter, that said
article does not apply to this case. After reading it,
and other advice, I changed ownership of the "Job" to be
the same account (BLUEBOX\sqlworker) used to run the SQL
Server Agent process (as detailed below). I hoped this
would fix things, but it hasn't. Unfortunately, the
BLUEBOX\sqlworker account does not permit remote login
for security reasons -- it is to be used solely for
running the SQL instance.

FYI, the BLUEBOX\sqlworker account IS a member of the
SQL "sysadmin" server role (as required per KB article).

Having rechecked the setup just now, I have noticed the
following fact: the BLUEBOX\sqlworker account is a member
of "sysadmin", but under that login's "Database Access"
property, I see that it has NOT been given "permit"
priviliges for ANY of the databases (including the one
used in this matter). Could this, even though the login
is a "sysadmin", result in the error I've been seeing?

Thank you again for your help -- I've tried to ensure
that my setup follows the guidelines set out in the KB
article, but am somewhat confused.

>-----Original Message-----
>The error

>-2147024891 (80070005)

>means

>General access denied error

>If you log into the box as the SQL Server Agent account
and execute it
>interactively as you have just done logged in as you
what happens.  This
>will highlight the Agent being the problem.  If you have
made sure that the
>article
>http://support.microsoft.com/?kbid=269074 does not apply
to you.

>In the package enable logging to a text file.

>--

>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org



>> I have created a simple DTS package to copy a database
>> from our production server to a development box on a
>> nightly basis.
>> The production server's name is:
>> BLUEBOX

>> I created the DTS package (and scheduled it) while
logged
>> into the server using Terminal Services. The Windows
>> account I log on as is:
>> BLUEBOX\jmb-sg

>> The DTS package has just one task: "Copy SQL Server
>> Objects". The settings for this task are:
>> Source database:       (local), using Windows
>> Authentication
>>    Destination database: xx.xxx.xx.xx (I.P. of
>> dev box),  using an SQL account on that box

>> The DTS package properties are:
>> Owner/Creator: BLUEBOX\jmb-sg
>> Computer: BLUEBOX

>> The "sqlservr.exe" process runs under a dedicated
Windows
>> account: BLUEBOX\sqlworker
>> The "sqlagent.exe" process runs under the same Windows
>> account: BLUEBOX\sqlworker

>> The SQL Server Agent job (scheduled to run the above
DTS
>> package once per night) has the owner:
>> Owner: BLUEBOX\sqlworker

>> Currently, the job has failed each night, producting
the
>> following detailed error message:

>> Executed as user: BLUEBOX\sqlworker. ...ing...   DTSRun
>> OnStart:  DTSStep_DTSTransferObjectsTask_1   DTSRun
>> OnProgress:  DTSStep_DTSTransferObjectsTask_1;
Scripting
>> objects for Transfer; PercentComplete = 0;
ProgressCount
>> = 0   DTSRun OnError:  

DTSStep_DTSTransferObjectsTask_1,

- Show quoted text -

Quote:>> 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:  5700
>> Error:  -2147467259 (800040.  The step failed.

>> The server is a dual-processor Compaq running a Windows
>> 2000 Server OS and SQL Server 2000. It is behind a
>> firewall. After creating the job, I ran it manually
>> (while still logged in via Terminal Services as
>> BLUEBOX\jmb-sg), and the job completed successfully
while
>> I waited. In the past week, I have read many newsgroup
>> threads, including in this forum, and have of course
read
>> the KB article 269074 many times over. At this point I
>> haven't solved the issue, and would greatly appreciate
>> your expert guidance. I've noticed that a few people
have
>> commented on related issues in the past, especially
Allan
>> Mitchell, and so hope that with the information I've
>> provided we can figure out what's going on!

>> Thank you in advance,
>> JB

>.

 
 
 

DTS Package fails when scheduled on server as SQL Server Agent job

Post by Allan Mitchel » Thu, 17 Apr 2003 18:57:17


SYSADMIN is God.  There needs to be no permissions explicitly granted on any
databases as they are implictly granted them.

Change the owner of the job to "sa".

The SQLAgent service account needs permissions onto any filesystem locations
local or remote.  it also needs permissions on the Source and destination
SQL Server objects it manipulates.

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


> Thank you for the quick response.

> I have setup the job to log errors to a text file, as you
> suggest.

> Let me clarify the matter regarding KB article 269074: I
> do not know, or believe for that matter, that said
> article does not apply to this case. After reading it,
> and other advice, I changed ownership of the "Job" to be
> the same account (BLUEBOX\sqlworker) used to run the SQL
> Server Agent process (as detailed below). I hoped this
> would fix things, but it hasn't. Unfortunately, the
> BLUEBOX\sqlworker account does not permit remote login
> for security reasons -- it is to be used solely for
> running the SQL instance.

> FYI, the BLUEBOX\sqlworker account IS a member of the
> SQL "sysadmin" server role (as required per KB article).

> Having rechecked the setup just now, I have noticed the
> following fact: the BLUEBOX\sqlworker account is a member
> of "sysadmin", but under that login's "Database Access"
> property, I see that it has NOT been given "permit"
> priviliges for ANY of the databases (including the one
> used in this matter). Could this, even though the login
> is a "sysadmin", result in the error I've been seeing?

> Thank you again for your help -- I've tried to ensure
> that my setup follows the guidelines set out in the KB
> article, but am somewhat confused.

> >-----Original Message-----
> >The error

> >-2147024891 (80070005)

> >means

> >General access denied error

> >If you log into the box as the SQL Server Agent account
> and execute it
> >interactively as you have just done logged in as you
> what happens.  This
> >will highlight the Agent being the problem.  If you have
> made sure that the
> >article
> >http://support.microsoft.com/?kbid=269074 does not apply
> to you.

> >In the package enable logging to a text file.

> >--

> >Allan Mitchell (Microsoft SQL Server MVP)
> >MCSE,MCDBA
> >www.SQLDTS.com
> >I support PASS - the definitive, global community
> >for SQL Server professionals - http://www.sqlpass.org



> >> I have created a simple DTS package to copy a database
> >> from our production server to a development box on a
> >> nightly basis.
> >> The production server's name is:
> >> BLUEBOX

> >> I created the DTS package (and scheduled it) while
> logged
> >> into the server using Terminal Services. The Windows
> >> account I log on as is:
> >> BLUEBOX\jmb-sg

> >> The DTS package has just one task: "Copy SQL Server
> >> Objects". The settings for this task are:
> >> Source database:       (local), using Windows
> >> Authentication
> >>    Destination database: xx.xxx.xx.xx (I.P. of
> >> dev box),  using an SQL account on that box

> >> The DTS package properties are:
> >> Owner/Creator: BLUEBOX\jmb-sg
> >> Computer: BLUEBOX

> >> The "sqlservr.exe" process runs under a dedicated
> Windows
> >> account: BLUEBOX\sqlworker
> >> The "sqlagent.exe" process runs under the same Windows
> >> account: BLUEBOX\sqlworker

> >> The SQL Server Agent job (scheduled to run the above
> DTS
> >> package once per night) has the owner:
> >> Owner: BLUEBOX\sqlworker

> >> Currently, the job has failed each night, producting
> the
> >> following detailed error message:

> >> Executed as user: BLUEBOX\sqlworker. ...ing...   DTSRun
> >> OnStart:  DTSStep_DTSTransferObjectsTask_1   DTSRun
> >> OnProgress:  DTSStep_DTSTransferObjectsTask_1;
> Scripting
> >> objects for Transfer; PercentComplete = 0;
> ProgressCount
> >> = 0   DTSRun OnError:
> DTSStep_DTSTransferObjectsTask_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:  5700
> >> Error:  -2147467259 (800040.  The step failed.

> >> The server is a dual-processor Compaq running a Windows
> >> 2000 Server OS and SQL Server 2000. It is behind a
> >> firewall. After creating the job, I ran it manually
> >> (while still logged in via Terminal Services as
> >> BLUEBOX\jmb-sg), and the job completed successfully
> while
> >> I waited. In the past week, I have read many newsgroup
> >> threads, including in this forum, and have of course
> read
> >> the KB article 269074 many times over. At this point I
> >> haven't solved the issue, and would greatly appreciate
> >> your expert guidance. I've noticed that a few people
> have
> >> commented on related issues in the past, especially
> Allan
> >> Mitchell, and so hope that with the information I've
> >> provided we can figure out what's going on!

> >> Thank you in advance,
> >> JB

> >.

 
 
 

DTS Package fails when scheduled on server as SQL Server Agent job

Post by JB » Fri, 18 Apr 2003 15:43:06


Thank you again Allan.
I made "sa" the owner of the SQL Server Agent job, and
set up logging. The job still fails, but this time I have
more detailed information.

From what I see, the error messages confirm your
suspicions, in that the failure is due to a "CreateFile"
access denied error, when trying to write to a .LOG file.
Below I am attaching the actual error message. It says
that the DTS package was...
Executed On: BLUEBOX
Executed By: sqlworker
"BLUEBOX\sqlworker" is the account under which SQL Server
and SQL Server Agent run. Do you think that this account
needs write access to the DATA directory?

Here are the details from the two errors written to
the "Application" event log. Note that the source
database (located on the BLUEBOX server, where the DTS
package resides in SQL Server) is called "PPS". The
destination database (on a remote server) is
named "PPS_1".

-------------- Source: SQLSERVERAGENT --
SQL Server Scheduled Job 'Backup production DB to
Intercraft (PPS_1)' (0xFFC812264946F744942BBE3AFFB25BD9) -
 Status: Failed - Invoked on: 2003-04-17 05:51:00 -
Message: The job failed.  The Job was invoked by Schedule
2 (Backup production DB to Intercraft (PPS_1)).  The last
step to run was step 1 (Backup production DB to
Intercraft (PPS_1)).

-------------- Source: DataTransformationServices --
The execution of the following DTS Package succeeded:

Package Name: Backup production DB to Intercraft (PPS_1)
Package Description: (null)
Package ID: {661464DF-3BD7-469D-AA97-19927FFFBEE9}
Package Version: {68C193A2-B913-4CC0-B5F7-F9A1B0BFA45C}
Package Execution Lineage: {BB66467B-8B0E-46E9-A10E-
21FF593C17FF}
Executed On: BLUEBOX
Executed By: sqlworker
Execution Started: 17/04/2003 05:51:00
Execution Completed: 17/04/2003 05:51:01
Total Execution Time: 0.063 seconds

Package Steps execution information:

Step 'DTSStep_DTSTransferObjectsTask_1' failed

Step Error Source: Microsoft Data Transformation Services
(DTS) Package
Step Error Description:Access is denied.
 (Microsoft Data Transformation Services (DTS) Package
(80070005): Access is denied.
) (Microsoft SQL-DMO (80004005): [SQL-DMO]CreateFile
error on 'BLUEBOX.PPS.LOG'. Access is denied.
)
Step Error code: 80070005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

Step Execution Started: 17/04/2003 05:51:00
Step Execution Completed: 17/04/2003 05:51:01
Total Step Execution Time: 0.063 seconds
Progress count in Step: 0  
---------------

Thank you for your continued help,
JB

>-----Original Message-----
>SYSADMIN is God.  There needs to be no permissions

explicitly granted on any
>databases as they are implictly granted them.

>Change the owner of the job to "sa".

>The SQLAgent service account needs permissions onto any

filesystem locations
>local or remote.  it also needs permissions on the

Source and destination
>SQL Server objects it manipulates.

>--

>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org

>"JB" <re...@to.newsgroup> wrote in message
>news:064201c303fa$35e89c50$a101280a@phx.gbl...
>> Thank you for the quick response.

>> I have setup the job to log errors to a text file, as
you
>> suggest.

>> Let me clarify the matter regarding KB article 269074:
I
>> do not know, or believe for that matter, that said
>> article does not apply to this case. After reading it,
>> and other advice, I changed ownership of the "Job" to
be
>> the same account (BLUEBOX\sqlworker) used to run the
SQL
>> Server Agent process (as detailed below). I hoped this
>> would fix things, but it hasn't. Unfortunately, the
>> BLUEBOX\sqlworker account does not permit remote login
>> for security reasons -- it is to be used solely for
>> running the SQL instance.

>> FYI, the BLUEBOX\sqlworker account IS a member of the
>> SQL "sysadmin" server role (as required per KB
article).

>> Having rechecked the setup just now, I have noticed the
>> following fact: the BLUEBOX\sqlworker account is a
member
>> of "sysadmin", but under that login's "Database Access"
>> property, I see that it has NOT been given "permit"
>> priviliges for ANY of the databases (including the one
>> used in this matter). Could this, even though the login
>> is a "sysadmin", result in the error I've been seeing?

>> Thank you again for your help -- I've tried to ensure
>> that my setup follows the guidelines set out in the KB
>> article, but am somewhat confused.

>> >-----Original Message-----
>> >The error

>> >-2147024891 (80070005)

>> >means

>> >General access denied error

>> >If you log into the box as the SQL Server Agent
account
>> and execute it
>> >interactively as you have just done logged in as you
>> what happens.  This
>> >will highlight the Agent being the problem.  If you
have
>> made sure that the
>> >article
>> >http://support.microsoft.com/?kbid=269074 does not
apply
>> to you.

>> >In the package enable logging to a text file.

>> >--

>> >Allan Mitchell (Microsoft SQL Server MVP)
>> >MCSE,MCDBA
>> >www.SQLDTS.com
>> >I support PASS - the definitive, global community
>> >for SQL Server professionals - http://www.sqlpass.org

>> >"JB" <re...@to.newsgroup> wrote in message
>> >news:011d01c303f2$2a189950$2f01280a@phx.gbl...
>> >> I have created a simple DTS package to copy a
database
>> >> from our production server to a development box on a
>> >> nightly basis.
>> >> The production server's name is:
>> >> BLUEBOX

>> >> I created the DTS package (and scheduled it) while
>> logged
>> >> into the server using Terminal Services. The Windows
>> >> account I log on as is:
>> >> BLUEBOX\jmb-sg

>> >> The DTS package has just one task: "Copy SQL Server
>> >> Objects". The settings for this task are:
>> >> Source database:       (local), using Windows
>> >> Authentication
>> >>    Destination database: xx.xxx.xx.xx (I.P. of
>> >> dev box),  using an SQL account on that box

>> >> The DTS package properties are:
>> >> Owner/Creator: BLUEBOX\jmb-sg
>> >> Computer: BLUEBOX

>> >> The "sqlservr.exe" process runs under a dedicated
>> Windows
>> >> account: BLUEBOX\sqlworker
>> >> The "sqlagent.exe" process runs under the same
Windows
>> >> account: BLUEBOX\sqlworker

>> >> The SQL Server Agent job (scheduled to run the above
>> DTS
>> >> package once per night) has the owner:
>> >> Owner: BLUEBOX\sqlworker

>> >> Currently, the job has failed each night, producting
>> the
>> >> following detailed error message:

>> >> Executed as user: BLUEBOX\sqlworker. ...ing...  
DTSRun
>> >> OnStart:  DTSStep_DTSTransferObjectsTask_1   DTSRun
>> >> OnProgress:  DTSStep_DTSTransferObjectsTask_1;
>> Scripting
>> >> objects for Transfer; PercentComplete = 0;
>> ProgressCount
>> >> = 0   DTSRun OnError:
>> DTSStep_DTSTransferObjectsTask_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:  5700
>> >> Error:  -2147467259 (800040.  The step failed.

>> >> The server is a dual-processor Compaq running a
Windows
>> >> 2000 Server OS and SQL Server 2000. It is behind a
>> >> firewall. After creating the job, I ran it manually
>> >> (while still logged in via Terminal Services as
>> >> BLUEBOX\jmb-sg), and the job completed successfully
>> while
>> >> I waited. In the past week, I have read many
newsgroup
>> >> threads, including in this forum, and have of course
>> read
>> >> the KB article 269074 many times over. At this
point I
>> >> haven't solved the issue, and would greatly
appreciate
>> >> your expert guidance. I've noticed that a few people
>> have
>> >> commented on related issues in the past, especially
>> Allan
>> >> Mitchell, and so hope that with the information I've
>> >> provided we can figure out what's going on!

>> >> Thank you in advance,
>> >> JB

>> >.

>.

 
 
 

DTS Package fails when scheduled on server as SQL Server Agent job

Post by Allan Mitchel » Fri, 18 Apr 2003 16:03:44


Yes give the BLUEBOX\sqlworker account write perms to the directory you want
it to write to.

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"JB" <re...@to.newsgroup> wrote in message

news:006801c304ac$9a422ce0$3401280a@phx.gbl...
> Thank you again Allan.
> I made "sa" the owner of the SQL Server Agent job, and
> set up logging. The job still fails, but this time I have
> more detailed information.

> From what I see, the error messages confirm your
> suspicions, in that the failure is due to a "CreateFile"
> access denied error, when trying to write to a .LOG file.
> Below I am attaching the actual error message. It says
> that the DTS package was...
> Executed On: BLUEBOX
> Executed By: sqlworker
> "BLUEBOX\sqlworker" is the account under which SQL Server
> and SQL Server Agent run. Do you think that this account
> needs write access to the DATA directory?

> Here are the details from the two errors written to
> the "Application" event log. Note that the source
> database (located on the BLUEBOX server, where the DTS
> package resides in SQL Server) is called "PPS". The
> destination database (on a remote server) is
> named "PPS_1".

> -------------- Source: SQLSERVERAGENT --
> SQL Server Scheduled Job 'Backup production DB to
> Intercraft (PPS_1)' (0xFFC812264946F744942BBE3AFFB25BD9) -
>  Status: Failed - Invoked on: 2003-04-17 05:51:00 -
> Message: The job failed.  The Job was invoked by Schedule
> 2 (Backup production DB to Intercraft (PPS_1)).  The last
> step to run was step 1 (Backup production DB to
> Intercraft (PPS_1)).

> -------------- Source: DataTransformationServices --
> The execution of the following DTS Package succeeded:

> Package Name: Backup production DB to Intercraft (PPS_1)
> Package Description: (null)
> Package ID: {661464DF-3BD7-469D-AA97-19927FFFBEE9}
> Package Version: {68C193A2-B913-4CC0-B5F7-F9A1B0BFA45C}
> Package Execution Lineage: {BB66467B-8B0E-46E9-A10E-
> 21FF593C17FF}
> Executed On: BLUEBOX
> Executed By: sqlworker
> Execution Started: 17/04/2003 05:51:00
> Execution Completed: 17/04/2003 05:51:01
> Total Execution Time: 0.063 seconds

> Package Steps execution information:

> Step 'DTSStep_DTSTransferObjectsTask_1' failed

> Step Error Source: Microsoft Data Transformation Services
> (DTS) Package
> Step Error Description:Access is denied.
>  (Microsoft Data Transformation Services (DTS) Package
> (80070005): Access is denied.
> ) (Microsoft SQL-DMO (80004005): [SQL-DMO]CreateFile
> error on 'BLUEBOX.PPS.LOG'. Access is denied.
> )
> Step Error code: 80070005
> Step Error Help File:sqldts80.hlp
> Step Error Help Context ID:1100

> Step Execution Started: 17/04/2003 05:51:00
> Step Execution Completed: 17/04/2003 05:51:01
> Total Step Execution Time: 0.063 seconds
> Progress count in Step: 0
> ---------------

> Thank you for your continued help,
> JB

> >-----Original Message-----
> >SYSADMIN is God.  There needs to be no permissions
> explicitly granted on any
> >databases as they are implictly granted them.

> >Change the owner of the job to "sa".

> >The SQLAgent service account needs permissions onto any
> filesystem locations
> >local or remote.  it also needs permissions on the
> Source and destination
> >SQL Server objects it manipulates.

> >--

> >Allan Mitchell (Microsoft SQL Server MVP)
> >MCSE,MCDBA
> >www.SQLDTS.com
> >I support PASS - the definitive, global community
> >for SQL Server professionals - http://www.sqlpass.org

> >"JB" <re...@to.newsgroup> wrote in message
> >news:064201c303fa$35e89c50$a101280a@phx.gbl...
> >> Thank you for the quick response.

> >> I have setup the job to log errors to a text file, as
> you
> >> suggest.

> >> Let me clarify the matter regarding KB article 269074:
> I
> >> do not know, or believe for that matter, that said
> >> article does not apply to this case. After reading it,
> >> and other advice, I changed ownership of the "Job" to
> be
> >> the same account (BLUEBOX\sqlworker) used to run the
> SQL
> >> Server Agent process (as detailed below). I hoped this
> >> would fix things, but it hasn't. Unfortunately, the
> >> BLUEBOX\sqlworker account does not permit remote login
> >> for security reasons -- it is to be used solely for
> >> running the SQL instance.

> >> FYI, the BLUEBOX\sqlworker account IS a member of the
> >> SQL "sysadmin" server role (as required per KB
> article).

> >> Having rechecked the setup just now, I have noticed the
> >> following fact: the BLUEBOX\sqlworker account is a
> member
> >> of "sysadmin", but under that login's "Database Access"
> >> property, I see that it has NOT been given "permit"
> >> priviliges for ANY of the databases (including the one
> >> used in this matter). Could this, even though the login
> >> is a "sysadmin", result in the error I've been seeing?

> >> Thank you again for your help -- I've tried to ensure
> >> that my setup follows the guidelines set out in the KB
> >> article, but am somewhat confused.

> >> >-----Original Message-----
> >> >The error

> >> >-2147024891 (80070005)

> >> >means

> >> >General access denied error

> >> >If you log into the box as the SQL Server Agent
> account
> >> and execute it
> >> >interactively as you have just done logged in as you
> >> what happens.  This
> >> >will highlight the Agent being the problem.  If you
> have
> >> made sure that the
> >> >article
> >> >http://support.microsoft.com/?kbid=269074 does not
> apply
> >> to you.

> >> >In the package enable logging to a text file.

> >> >--

> >> >Allan Mitchell (Microsoft SQL Server MVP)
> >> >MCSE,MCDBA
> >> >www.SQLDTS.com
> >> >I support PASS - the definitive, global community
> >> >for SQL Server professionals - http://www.sqlpass.org

> >> >"JB" <re...@to.newsgroup> wrote in message
> >> >news:011d01c303f2$2a189950$2f01280a@phx.gbl...
> >> >> I have created a simple DTS package to copy a
> database
> >> >> from our production server to a development box on a
> >> >> nightly basis.
> >> >> The production server's name is:
> >> >> BLUEBOX

> >> >> I created the DTS package (and scheduled it) while
> >> logged
> >> >> into the server using Terminal Services. The Windows
> >> >> account I log on as is:
> >> >> BLUEBOX\jmb-sg

> >> >> The DTS package has just one task: "Copy SQL Server
> >> >> Objects". The settings for this task are:
> >> >> Source database:       (local), using Windows
> >> >> Authentication
> >> >>    Destination database: xx.xxx.xx.xx (I.P. of
> >> >> dev box),  using an SQL account on that box

> >> >> The DTS package properties are:
> >> >> Owner/Creator: BLUEBOX\jmb-sg
> >> >> Computer: BLUEBOX

> >> >> The "sqlservr.exe" process runs under a dedicated
> >> Windows
> >> >> account: BLUEBOX\sqlworker
> >> >> The "sqlagent.exe" process runs under the same
> Windows
> >> >> account: BLUEBOX\sqlworker

> >> >> The SQL Server Agent job (scheduled to run the above
> >> DTS
> >> >> package once per night) has the owner:
> >> >> Owner: BLUEBOX\sqlworker

> >> >> Currently, the job has failed each night, producting
> >> the
> >> >> following detailed error message:

> >> >> Executed as user: BLUEBOX\sqlworker. ...ing...
> DTSRun
> >> >> OnStart:  DTSStep_DTSTransferObjectsTask_1   DTSRun
> >> >> OnProgress:  DTSStep_DTSTransferObjectsTask_1;
> >> Scripting
> >> >> objects for Transfer; PercentComplete = 0;
> >> ProgressCount
> >> >> = 0   DTSRun OnError:
> >> DTSStep_DTSTransferObjectsTask_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:  5700
> >> >> Error:  -2147467259 (800040.  The step failed.

> >> >> The server is a dual-processor Compaq running a
> Windows
> >> >> 2000 Server OS and SQL Server 2000. It is behind a
> >> >> firewall. After creating the job, I ran it manually
> >> >> (while still logged in via Terminal Services as
> >> >> BLUEBOX\jmb-sg), and the job completed successfully
> >> while
> >> >> I waited. In the past week, I have read many
> newsgroup
> >> >> threads, including in this forum, and have of course
> >> read
> >> >> the KB article 269074 many times over. At this
> point I
> >> >> haven't solved the issue, and would greatly
> appreciate
> >> >> your expert guidance. I've noticed that a few people
> >> have
> >> >> commented on related issues in the past, especially
> >> Allan
> >> >> Mitchell, and so hope that with the information I've
> >> >> provided we can figure out what's going on!

> >> >> Thank you in advance,
> >> >> JB

> >> >.

> >.

 
 
 

DTS Package fails when scheduled on server as SQL Server Agent job

Post by JB » Sat, 19 Apr 2003 09:01:35


Thank you again. It looks like the DTS package wants to
write to the
C:\Program Files\Microsoft SQL Server\80\Tools
directory, so I've added read/write/list permissions.
Will update this thread shortly if it works!

>-----Original Message-----
>Yes give the BLUEBOX\sqlworker account write perms to

the directory you want
>it to write to.

>--

>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org

>"JB" <re...@to.newsgroup> wrote in message
>news:006801c304ac$9a422ce0$3401280a@phx.gbl...
>> Thank you again Allan.
>> I made "sa" the owner of the SQL Server Agent job, and
>> set up logging. The job still fails, but this time I
have
>> more detailed information.

>> From what I see, the error messages confirm your
>> suspicions, in that the failure is due to
a "CreateFile"
>> access denied error, when trying to write to a .LOG
file.
>> Below I am attaching the actual error message. It says
>> that the DTS package was...
>> Executed On: BLUEBOX
>> Executed By: sqlworker
>> "BLUEBOX\sqlworker" is the account under which SQL
Server
>> and SQL Server Agent run. Do you think that this
account
>> needs write access to the DATA directory?

>> Here are the details from the two errors written to
>> the "Application" event log. Note that the source
>> database (located on the BLUEBOX server, where the DTS
>> package resides in SQL Server) is called "PPS". The
>> destination database (on a remote server) is
>> named "PPS_1".

>> -------------- Source: SQLSERVERAGENT --
>> SQL Server Scheduled Job 'Backup production DB to
>> Intercraft (PPS_1)'

(0xFFC812264946F744942BBE3AFFB25BD9) -

- Show quoted text -

>>  Status: Failed - Invoked on: 2003-04-17 05:51:00 -
>> Message: The job failed.  The Job was invoked by
Schedule
>> 2 (Backup production DB to Intercraft (PPS_1)).  The
last
>> step to run was step 1 (Backup production DB to
>> Intercraft (PPS_1)).

>> -------------- Source: DataTransformationServices --
>> The execution of the following DTS Package succeeded:

>> Package Name: Backup production DB to Intercraft
(PPS_1)
>> Package Description: (null)
>> Package ID: {661464DF-3BD7-469D-AA97-19927FFFBEE9}
>> Package Version: {68C193A2-B913-4CC0-B5F7-F9A1B0BFA45C}
>> Package Execution Lineage: {BB66467B-8B0E-46E9-A10E-
>> 21FF593C17FF}
>> Executed On: BLUEBOX
>> Executed By: sqlworker
>> Execution Started: 17/04/2003 05:51:00
>> Execution Completed: 17/04/2003 05:51:01
>> Total Execution Time: 0.063 seconds

>> Package Steps execution information:

>> Step 'DTSStep_DTSTransferObjectsTask_1' failed

>> Step Error Source: Microsoft Data Transformation
Services
>> (DTS) Package
>> Step Error Description:Access is denied.
>>  (Microsoft Data Transformation Services (DTS) Package
>> (80070005): Access is denied.
>> ) (Microsoft SQL-DMO (80004005): [SQL-DMO]CreateFile
>> error on 'BLUEBOX.PPS.LOG'. Access is denied.
>> )
>> Step Error code: 80070005
>> Step Error Help File:sqldts80.hlp
>> Step Error Help Context ID:1100

>> Step Execution Started: 17/04/2003 05:51:00
>> Step Execution Completed: 17/04/2003 05:51:01
>> Total Step Execution Time: 0.063 seconds
>> Progress count in Step: 0
>> ---------------

>> Thank you for your continued help,
>> JB

>> >-----Original Message-----
>> >SYSADMIN is God.  There needs to be no permissions
>> explicitly granted on any
>> >databases as they are implictly granted them.

>> >Change the owner of the job to "sa".

>> >The SQLAgent service account needs permissions onto
any
>> filesystem locations
>> >local or remote.  it also needs permissions on the
>> Source and destination
>> >SQL Server objects it manipulates.

>> >--

>> >Allan Mitchell (Microsoft SQL Server MVP)
>> >MCSE,MCDBA
>> >www.SQLDTS.com
>> >I support PASS - the definitive, global community
>> >for SQL Server professionals - http://www.sqlpass.org

>> >"JB" <re...@to.newsgroup> wrote in message
>> >news:064201c303fa$35e89c50$a101280a@phx.gbl...
>> >> Thank you for the quick response.

>> >> I have setup the job to log errors to a text file,
as
>> you
>> >> suggest.

>> >> Let me clarify the matter regarding KB article
269074:
>> I
>> >> do not know, or believe for that matter, that said
>> >> article does not apply to this case. After reading
it,
>> >> and other advice, I changed ownership of the "Job"
to
>> be
>> >> the same account (BLUEBOX\sqlworker) used to run the
>> SQL
>> >> Server Agent process (as detailed below). I hoped
this
>> >> would fix things, but it hasn't. Unfortunately, the
>> >> BLUEBOX\sqlworker account does not permit remote
login
>> >> for security reasons -- it is to be used solely for
>> >> running the SQL instance.

>> >> FYI, the BLUEBOX\sqlworker account IS a member of
the
>> >> SQL "sysadmin" server role (as required per KB
>> article).

>> >> Having rechecked the setup just now, I have noticed
the
>> >> following fact: the BLUEBOX\sqlworker account is a
>> member
>> >> of "sysadmin", but under that login's "Database
Access"
>> >> property, I see that it has NOT been given "permit"
>> >> priviliges for ANY of the databases (including the
one
>> >> used in this matter). Could this, even though the
login
>> >> is a "sysadmin", result in the error I've been
seeing?

>> >> Thank you again for your help -- I've tried to
ensure
>> >> that my setup follows the guidelines set out in the
KB
>> >> article, but am somewhat confused.

>> >> >-----Original Message-----
>> >> >The error

>> >> >-2147024891 (80070005)

>> >> >means

>> >> >General access denied error

>> >> >If you log into the box as the SQL Server Agent
>> account
>> >> and execute it
>> >> >interactively as you have just done logged in as
you
>> >> what happens.  This
>> >> >will highlight the Agent being the problem.  If you
>> have
>> >> made sure that the
>> >> >article
>> >> >http://support.microsoft.com/?kbid=269074 does not
>> apply
>> >> to you.

>> >> >In the package enable logging to a text file.

>> >> >--

>> >> >Allan Mitchell (Microsoft SQL Server MVP)
>> >> >MCSE,MCDBA
>> >> >www.SQLDTS.com
>> >> >I support PASS - the definitive, global community
>> >> >for SQL Server professionals -

http://www.sqlpass.org

- Show quoted text -

>> >> >"JB" <re...@to.newsgroup> wrote in message
>> >> >news:011d01c303f2$2a189950$2f01280a@phx.gbl...
>> >> >> I have created a simple DTS package to copy a
>> database
>> >> >> from our production server to a development box
on a
>> >> >> nightly basis.
>> >> >> The production server's name is:
>> >> >> BLUEBOX

>> >> >> I created the DTS package (and scheduled it)
while
>> >> logged
>> >> >> into the server using Terminal Services. The
Windows
>> >> >> account I log on as is:
>> >> >> BLUEBOX\jmb-sg

>> >> >> The DTS package has just one task: "Copy SQL
Server
>> >> >> Objects". The settings for this task are:
>> >> >> Source database:       (local), using Windows
>> >> >> Authentication
>> >> >>    Destination database: xx.xxx.xx.xx (I.P. of
>> >> >> dev box),  using an SQL account on that box

>> >> >> The DTS package properties are:
>> >> >> Owner/Creator: BLUEBOX\jmb-sg
>> >> >> Computer: BLUEBOX

>> >> >> The "sqlservr.exe" process runs under a dedicated
>> >> Windows
>> >> >> account: BLUEBOX\sqlworker
>> >> >> The "sqlagent.exe" process runs under the same
>> Windows
>> >> >> account: BLUEBOX\sqlworker

>> >> >> The SQL Server Agent job (scheduled to run the
above
>> >> DTS
>> >> >> package once per night) has the owner:
>> >> >> Owner: BLUEBOX\sqlworker

>> >> >> Currently, the job has failed each night,
producting
>> >> the
>> >> >> following detailed error message:

>> >> >> Executed as user: BLUEBOX\sqlworker. ...ing...
>> DTSRun
>> >> >> OnStart:  DTSStep_DTSTransferObjectsTask_1  
DTSRun
>> >> >> OnProgress:  DTSStep_DTSTransferObjectsTask_1;
>> >> Scripting
>> >> >> objects for Transfer; PercentComplete = 0;
>> >> ProgressCount
>> >> >> = 0   DTSRun OnError:
>> >> DTSStep_DTSTransferObjectsTask_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:  5700
>> >> >> Error:  -2147467259 (800040.  The step failed.

>> >> >> The server is a dual-processor Compaq running a
>> Windows
>> >> >> 2000 Server OS and SQL Server 2000. It is behind
a
>> >> >> firewall. After creating the job, I ran it
manually
>> >> >> (while still logged in via Terminal Services as
>> >> >> BLUEBOX\jmb-sg), and the job completed
successfully
>> >> while
>> >> >> I waited. In the past week, I have read many
>> newsgroup
>> >> >> threads, including in this forum, and have of
course
>> >> read
>> >> >> the KB article 269074 many times over. At this
>> point I
>> >> >> haven't solved the issue, and would greatly
>> appreciate
>> >> >> your expert guidance. I've noticed that a few
people
>> >> have
>> >> >> commented on related issues in the past,
especially
>> >> Allan
>> >> >> Mitchell, and so hope that with the information
I've
>> >> >> provided we can figure out what's going on!

>> >> >> Thank you

...

read more »

 
 
 

DTS Package fails when scheduled on server as SQL Server Agent job

Post by JB » Sun, 20 Apr 2003 13:09:54


It's working! Thanks for all your help along the way. For
anyone who comes across this thread in the future, let me
summarize what I've learned.

1) Set things up so that the DTS package and the Job both
write to the Application event log, for both success and
failure. The resulting detailed error messages are more
helpful than what you'll see in the "Job History".

2) On the server where the DTS package runs, it may well
need write access to certain folders, EVEN if the package
itself does not involve any "write" steps. To run
successfully, DTS will create some log files and some
scripting files. In my case, this is what caused the
failure.

3) To find out what folders will be written to (see 2
above), the best I could do was grab the name of the file
that could not be written (from event log message; see 1)
and then do a search on the whole drive for such a file.

Thanks Allan.

>-----Original Message-----
>Thank you again. It looks like the DTS package wants to
>write to the
>C:\Program Files\Microsoft SQL Server\80\Tools
>directory, so I've added read/write/list permissions.
>Will update this thread shortly if it works!

>>-----Original Message-----
>>Yes give the BLUEBOX\sqlworker account write perms to
>the directory you want
>>it to write to.

>>--

>>Allan Mitchell (Microsoft SQL Server MVP)
>>MCSE,MCDBA
>>www.SQLDTS.com
>>I support PASS - the definitive, global community
>>for SQL Server professionals - http://www.sqlpass.org

>>"JB" <re...@to.newsgroup> wrote in message
>>news:006801c304ac$9a422ce0$3401280a@phx.gbl...
>>> Thank you again Allan.
>>> I made "sa" the owner of the SQL Server Agent job, and
>>> set up logging. The job still fails, but this time I
>have
>>> more detailed information.

>>> From what I see, the error messages confirm your
>>> suspicions, in that the failure is due to
>a "CreateFile"
>>> access denied error, when trying to write to a .LOG
>file.
>>> Below I am attaching the actual error message. It says
>>> that the DTS package was...
>>> Executed On: BLUEBOX
>>> Executed By: sqlworker
>>> "BLUEBOX\sqlworker" is the account under which SQL
>Server
>>> and SQL Server Agent run. Do you think that this
>account
>>> needs write access to the DATA directory?

>>> Here are the details from the two errors written to
>>> the "Application" event log. Note that the source
>>> database (located on the BLUEBOX server, where the DTS
>>> package resides in SQL Server) is called "PPS". The
>>> destination database (on a remote server) is
>>> named "PPS_1".

>>> -------------- Source: SQLSERVERAGENT --
>>> SQL Server Scheduled Job 'Backup production DB to
>>> Intercraft (PPS_1)'
>(0xFFC812264946F744942BBE3AFFB25BD9) -
>>>  Status: Failed - Invoked on: 2003-04-17 05:51:00 -
>>> Message: The job failed.  The Job was invoked by
>Schedule
>>> 2 (Backup production DB to Intercraft (PPS_1)).  The
>last
>>> step to run was step 1 (Backup production DB to
>>> Intercraft (PPS_1)).

>>> -------------- Source: DataTransformationServices --
>>> The execution of the following DTS Package succeeded:

>>> Package Name: Backup production DB to Intercraft
>(PPS_1)
>>> Package Description: (null)
>>> Package ID: {661464DF-3BD7-469D-AA97-19927FFFBEE9}
>>> Package Version: {68C193A2-B913-4CC0-B5F7-
F9A1B0BFA45C}
>>> Package Execution Lineage: {BB66467B-8B0E-46E9-A10E-
>>> 21FF593C17FF}
>>> Executed On: BLUEBOX
>>> Executed By: sqlworker
>>> Execution Started: 17/04/2003 05:51:00
>>> Execution Completed: 17/04/2003 05:51:01
>>> Total Execution Time: 0.063 seconds

>>> Package Steps execution information:

>>> Step 'DTSStep_DTSTransferObjectsTask_1' failed

>>> Step Error Source: Microsoft Data Transformation
>Services
>>> (DTS) Package
>>> Step Error Description:Access is denied.
>>>  (Microsoft Data Transformation Services (DTS) Package
>>> (80070005): Access is denied.
>>> ) (Microsoft SQL-DMO (80004005): [SQL-DMO]CreateFile
>>> error on 'BLUEBOX.PPS.LOG'. Access is denied.
>>> )
>>> Step Error code: 80070005
>>> Step Error Help File:sqldts80.hlp
>>> Step Error Help Context ID:1100

>>> Step Execution Started: 17/04/2003 05:51:00
>>> Step Execution Completed: 17/04/2003 05:51:01
>>> Total Step Execution Time: 0.063 seconds
>>> Progress count in Step: 0
>>> ---------------

>>> Thank you for your continued help,
>>> JB

>>> >-----Original Message-----
>>> >SYSADMIN is God.  There needs to be no permissions
>>> explicitly granted on any
>>> >databases as they are implictly granted them.

>>> >Change the owner of the job to "sa".

>>> >The SQLAgent service account needs permissions onto
>any
>>> filesystem locations
>>> >local or remote.  it also needs permissions on the
>>> Source and destination
>>> >SQL Server objects it manipulates.

>>> >--

>>> >Allan Mitchell (Microsoft SQL Server MVP)
>>> >MCSE,MCDBA
>>> >www.SQLDTS.com
>>> >I support PASS - the definitive, global community
>>> >for SQL Server professionals - http://www.sqlpass.org

>>> >"JB" <re...@to.newsgroup> wrote in message
>>> >news:064201c303fa$35e89c50$a101280a@phx.gbl...
>>> >> Thank you for the quick response.

>>> >> I have setup the job to log errors to a text file,
>as
>>> you
>>> >> suggest.

>>> >> Let me clarify the matter regarding KB article
>269074:
>>> I
>>> >> do not know, or believe for that matter, that said
>>> >> article does not apply to this case. After reading
>it,
>>> >> and other advice, I changed ownership of the "Job"
>to
>>> be
>>> >> the same account (BLUEBOX\sqlworker) used to run
the
>>> SQL
>>> >> Server Agent process (as detailed below). I hoped
>this
>>> >> would fix things, but it hasn't. Unfortunately, the
>>> >> BLUEBOX\sqlworker account does not permit remote
>login
>>> >> for security reasons -- it is to be used solely for
>>> >> running the SQL instance.

>>> >> FYI, the BLUEBOX\sqlworker account IS a member of
>the
>>> >> SQL "sysadmin" server role (as required per KB
>>> article).

>>> >> Having rechecked the setup just now, I have
noticed
>the
>>> >> following fact: the BLUEBOX\sqlworker account is a
>>> member
>>> >> of "sysadmin", but under that login's "Database
>Access"
>>> >> property, I see that it has NOT been given "permit"
>>> >> priviliges for ANY of the databases (including the
>one
>>> >> used in this matter). Could this, even though the
>login
>>> >> is a "sysadmin", result in the error I've been
>seeing?

>>> >> Thank you again for your help -- I've tried to
>ensure
>>> >> that my setup follows the guidelines set out in
the
>KB
>>> >> article, but am somewhat confused.

>>> >> >-----Original Message-----
>>> >> >The error

>>> >> >-2147024891 (80070005)

>>> >> >means

>>> >> >General access denied error

>>> >> >If you log into the box as the SQL Server Agent
>>> account
>>> >> and execute it
>>> >> >interactively as you have just done logged in as
>you
>>> >> what happens.  This
>>> >> >will highlight the Agent being the problem.  If
you
>>> have
>>> >> made sure that the
>>> >> >article
>>> >> >http://support.microsoft.com/?kbid=269074 does not
>>> apply
>>> >> to you.

>>> >> >In the package enable logging to a text file.

>>> >> >--

>>> >> >Allan Mitchell (Microsoft SQL Server MVP)
>>> >> >MCSE,MCDBA
>>> >> >www.SQLDTS.com
>>> >> >I support PASS - the definitive, global community
>>> >> >for SQL Server professionals -
>http://www.sqlpass.org

>>> >> >"JB" <re...@to.newsgroup> wrote in message
>>> >> >news:011d01c303f2$2a189950$2f01280a@phx.gbl...
>>> >> >> I have created a simple DTS package to copy a
>>> database
>>> >> >> from our production server to a development box
>on a
>>> >> >> nightly basis.
>>> >> >> The production server's name is:
>>> >> >> BLUEBOX

>>> >> >> I created the DTS package (and scheduled it)
>while
>>> >> logged
>>> >> >> into the server using Terminal Services. The
>Windows
>>> >> >> account I log on as is:
>>> >> >> BLUEBOX\jmb-sg

>>> >> >> The DTS package has just one task: "Copy SQL
>Server
>>> >> >> Objects". The settings for this task are:
>>> >> >> Source database:       (local), using Windows
>>> >> >> Authentication
>>> >> >>    Destination database: xx.xxx.xx.xx (I.P. of
>>> >> >> dev box),  using an SQL account on that box

>>> >> >> The DTS package properties are:
>>> >> >> Owner/Creator: BLUEBOX\jmb-sg
>>> >> >> Computer: BLUEBOX

>>> >> >> The "sqlservr.exe" process runs under a
dedicated
>>> >> Windows
>>> >> >> account: BLUEBOX\sqlworker
>>> >> >> The "sqlagent.exe" process runs under the same
>>> Windows
>>> >> >> account: BLUEBOX\sqlworker

>>> >> >> The SQL Server Agent job (scheduled to run the
>above
>>> >> DTS
>>> >> >> package once per night) has the owner:
>>> >> >> Owner: BLUEBOX\sqlworker

>>> >> >> Currently, the job has failed each night,
>producting
>>> >> the
>>> >> >> following detailed error message:

>>> >> >> Executed as user: BLUEBOX\sqlworker. ...ing...
>>> DTSRun
>>> >> >> OnStart:  DTSStep_DTSTransferObjectsTask_1  
>DTSRun
>>> >> >> OnProgress:  DTSStep_DTSTransferObjectsTask_1;
>>> >> Scripting
>>> >> >> objects for Transfer; PercentComplete = 0;
>>> >> ProgressCount
>>> >> >> = 0   DTSRun OnError:
>>> >> DTSStep_DTSTransferObjectsTask_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    

...

read more »

 
 
 

DTS Package fails when scheduled on server as SQL Server Agent job

Post by Andrew Oftheson » Sun, 27 Apr 2003 08:12:43


I'm having the same problem....

Where can i activate the "Application event log" for the dts/jobs

"JB" <re...@to.newsgroup> escribi en el mensaje
news:001801c30629$885ba8c0$3301280a@phx.gbl...

> It's working! Thanks for all your help along the way. For
> anyone who comes across this thread in the future, let me
> summarize what I've learned.

> 1) Set things up so that the DTS package and the Job both
> write to the Application event log, for both success and
> failure. The resulting detailed error messages are more
> helpful than what you'll see in the "Job History".

> 2) On the server where the DTS package runs, it may well
> need write access to certain folders, EVEN if the package
> itself does not involve any "write" steps. To run
> successfully, DTS will create some log files and some
> scripting files. In my case, this is what caused the
> failure.

> 3) To find out what folders will be written to (see 2
> above), the best I could do was grab the name of the file
> that could not be written (from event log message; see 1)
> and then do a search on the whole drive for such a file.

> Thanks Allan.

> >-----Original Message-----
> >Thank you again. It looks like the DTS package wants to
> >write to the
> >C:\Program Files\Microsoft SQL Server\80\Tools
> >directory, so I've added read/write/list permissions.
> >Will update this thread shortly if it works!

> >>-----Original Message-----
> >>Yes give the BLUEBOX\sqlworker account write perms to
> >the directory you want
> >>it to write to.

> >>--

> >>Allan Mitchell (Microsoft SQL Server MVP)
> >>MCSE,MCDBA
> >>www.SQLDTS.com
> >>I support PASS - the definitive, global community
> >>for SQL Server professionals - http://www.sqlpass.org

> >>"JB" <re...@to.newsgroup> wrote in message
> >>news:006801c304ac$9a422ce0$3401280a@phx.gbl...
> >>> Thank you again Allan.
> >>> I made "sa" the owner of the SQL Server Agent job, and
> >>> set up logging. The job still fails, but this time I
> >have
> >>> more detailed information.

> >>> From what I see, the error messages confirm your
> >>> suspicions, in that the failure is due to
> >a "CreateFile"
> >>> access denied error, when trying to write to a .LOG
> >file.
> >>> Below I am attaching the actual error message. It says
> >>> that the DTS package was...
> >>> Executed On: BLUEBOX
> >>> Executed By: sqlworker
> >>> "BLUEBOX\sqlworker" is the account under which SQL
> >Server
> >>> and SQL Server Agent run. Do you think that this
> >account
> >>> needs write access to the DATA directory?

> >>> Here are the details from the two errors written to
> >>> the "Application" event log. Note that the source
> >>> database (located on the BLUEBOX server, where the DTS
> >>> package resides in SQL Server) is called "PPS". The
> >>> destination database (on a remote server) is
> >>> named "PPS_1".

> >>> -------------- Source: SQLSERVERAGENT --
> >>> SQL Server Scheduled Job 'Backup production DB to
> >>> Intercraft (PPS_1)'
> >(0xFFC812264946F744942BBE3AFFB25BD9) -
> >>>  Status: Failed - Invoked on: 2003-04-17 05:51:00 -
> >>> Message: The job failed.  The Job was invoked by
> >Schedule
> >>> 2 (Backup production DB to Intercraft (PPS_1)).  The
> >last
> >>> step to run was step 1 (Backup production DB to
> >>> Intercraft (PPS_1)).

> >>> -------------- Source: DataTransformationServices --
> >>> The execution of the following DTS Package succeeded:

> >>> Package Name: Backup production DB to Intercraft
> >(PPS_1)
> >>> Package Description: (null)
> >>> Package ID: {661464DF-3BD7-469D-AA97-19927FFFBEE9}
> >>> Package Version: {68C193A2-B913-4CC0-B5F7-
> F9A1B0BFA45C}
> >>> Package Execution Lineage: {BB66467B-8B0E-46E9-A10E-
> >>> 21FF593C17FF}
> >>> Executed On: BLUEBOX
> >>> Executed By: sqlworker
> >>> Execution Started: 17/04/2003 05:51:00
> >>> Execution Completed: 17/04/2003 05:51:01
> >>> Total Execution Time: 0.063 seconds

> >>> Package Steps execution information:

> >>> Step 'DTSStep_DTSTransferObjectsTask_1' failed

> >>> Step Error Source: Microsoft Data Transformation
> >Services
> >>> (DTS) Package
> >>> Step Error Description:Access is denied.
> >>>  (Microsoft Data Transformation Services (DTS) Package
> >>> (80070005): Access is denied.
> >>> ) (Microsoft SQL-DMO (80004005): [SQL-DMO]CreateFile
> >>> error on 'BLUEBOX.PPS.LOG'. Access is denied.
> >>> )
> >>> Step Error code: 80070005
> >>> Step Error Help File:sqldts80.hlp
> >>> Step Error Help Context ID:1100

> >>> Step Execution Started: 17/04/2003 05:51:00
> >>> Step Execution Completed: 17/04/2003 05:51:01
> >>> Total Step Execution Time: 0.063 seconds
> >>> Progress count in Step: 0
> >>> ---------------

> >>> Thank you for your continued help,
> >>> JB

> >>> >-----Original Message-----
> >>> >SYSADMIN is God.  There needs to be no permissions
> >>> explicitly granted on any
> >>> >databases as they are implictly granted them.

> >>> >Change the owner of the job to "sa".

> >>> >The SQLAgent service account needs permissions onto
> >any
> >>> filesystem locations
> >>> >local or remote.  it also needs permissions on the
> >>> Source and destination
> >>> >SQL Server objects it manipulates.

> >>> >--

> >>> >Allan Mitchell (Microsoft SQL Server MVP)
> >>> >MCSE,MCDBA
> >>> >www.SQLDTS.com
> >>> >I support PASS - the definitive, global community
> >>> >for SQL Server professionals - http://www.sqlpass.org

> >>> >"JB" <re...@to.newsgroup> wrote in message
> >>> >news:064201c303fa$35e89c50$a101280a@phx.gbl...
> >>> >> Thank you for the quick response.

> >>> >> I have setup the job to log errors to a text file,
> >as
> >>> you
> >>> >> suggest.

> >>> >> Let me clarify the matter regarding KB article
> >269074:
> >>> I
> >>> >> do not know, or believe for that matter, that said
> >>> >> article does not apply to this case. After reading
> >it,
> >>> >> and other advice, I changed ownership of the "Job"
> >to
> >>> be
> >>> >> the same account (BLUEBOX\sqlworker) used to run
> the
> >>> SQL
> >>> >> Server Agent process (as detailed below). I hoped
> >this
> >>> >> would fix things, but it hasn't. Unfortunately, the
> >>> >> BLUEBOX\sqlworker account does not permit remote
> >login
> >>> >> for security reasons -- it is to be used solely for
> >>> >> running the SQL instance.

> >>> >> FYI, the BLUEBOX\sqlworker account IS a member of
> >the
> >>> >> SQL "sysadmin" server role (as required per KB
> >>> article).

> >>> >> Having rechecked the setup just now, I have
> noticed
> >the
> >>> >> following fact: the BLUEBOX\sqlworker account is a
> >>> member
> >>> >> of "sysadmin", but under that login's "Database
> >Access"
> >>> >> property, I see that it has NOT been given "permit"
> >>> >> priviliges for ANY of the databases (including the
> >one
> >>> >> used in this matter). Could this, even though the
> >login
> >>> >> is a "sysadmin", result in the error I've been
> >seeing?

> >>> >> Thank you again for your help -- I've tried to
> >ensure
> >>> >> that my setup follows the guidelines set out in
> the
> >KB
> >>> >> article, but am somewhat confused.

> >>> >> >-----Original Message-----
> >>> >> >The error

> >>> >> >-2147024891 (80070005)

> >>> >> >means

> >>> >> >General access denied error

> >>> >> >If you log into the box as the SQL Server Agent
> >>> account
> >>> >> and execute it
> >>> >> >interactively as you have just done logged in as
> >you
> >>> >> what happens.  This
> >>> >> >will highlight the Agent being the problem.  If
> you
> >>> have
> >>> >> made sure that the
> >>> >> >article
> >>> >> >http://support.microsoft.com/?kbid=269074 does not
> >>> apply
> >>> >> to you.

> >>> >> >In the package enable logging to a text file.

> >>> >> >--

> >>> >> >Allan Mitchell (Microsoft SQL Server MVP)
> >>> >> >MCSE,MCDBA
> >>> >> >www.SQLDTS.com
> >>> >> >I support PASS - the definitive, global community
> >>> >> >for SQL Server professionals -
> >http://www.sqlpass.org

> >>> >> >"JB" <re...@to.newsgroup> wrote in message
> >>> >> >news:011d01c303f2$2a189950$2f01280a@phx.gbl...
> >>> >> >> I have created a simple DTS package to copy a
> >>> database
> >>> >> >> from our production server to a development box
> >on a
> >>> >> >> nightly basis.
> >>> >> >> The production server's name is:
> >>> >> >> BLUEBOX

> >>> >> >> I created the DTS package (and scheduled it)
> >while
> >>> >> logged
> >>> >> >> into the server using Terminal Services. The
> >Windows
> >>> >> >> account I log on as is:
> >>> >> >> BLUEBOX\jmb-sg

> >>> >> >> The DTS package has just one task: "Copy SQL
> >Server
> >>> >> >> Objects". The settings for this task are:
> >>> >> >> Source database:       (local), using Windows
> >>> >> >> Authentication
> >>> >> >>    Destination database: xx.xxx.xx.xx (I.P. of
> >>> >> >> dev box),  using an SQL account on that box

> >>> >> >> The DTS package properties are:
> >>> >> >> Owner/Creator: BLUEBOX\jmb-sg
> >>> >> >> Computer: BLUEBOX

> >>> >> >> The "sqlservr.exe" process runs under a
> dedicated
> >>> >> Windows
> >>> >> >> account: BLUEBOX\sqlworker
> >>> >> >> The "sqlagent.exe" process runs under the same
> >>> Windows
> >>> >> >> account: BLUEBOX\sqlworker

> >>> >> >> The SQL Server Agent job (scheduled to run the
> >above
> >>> >> DTS
> >>> >> >> package once per night) has the owner:
> >>> >> >> Owner: BLUEBOX\sqlworker

> >>> >> >> Currently, the job has failed each night,
> >producting
> >>> >> the
> >>> >> >> following detailed error message:

> >>> >> >> Executed as user: BLUEBOX\sqlworker. ...ing...
> >>> DTSRun
> >>> >> >> OnStart:  DTSStep_DTSTransferObjectsTask_1
> >DTSRun

...

read more »

 
 
 

DTS Package fails when scheduled on server as SQL Server Agent job

Post by Old Ja » Sun, 11 May 2003 05:47:43


I am having the same problem. Please let me know how to solve it, if
you have any luck...

OldJast


> I have created a simple DTS package to copy a database
> from our production server to a development box on a
> nightly basis.
> The production server's name is:  
>    BLUEBOX

> I created the DTS package (and scheduled it) while logged
> into the server using Terminal Services. The Windows
> account I log on as is:
>    BLUEBOX\jmb-sg

> The DTS package has just one task: "Copy SQL Server
> Objects". The settings for this task are:
>    Source database:       (local), using Windows
> Authentication
>            Destination database:   xx.xxx.xx.xx    (I.P. of
> dev box),  using an SQL account on that box

> The DTS package properties are:
>    Owner/Creator:  BLUEBOX\jmb-sg
>    Computer:       BLUEBOX

> The "sqlservr.exe" process runs under a dedicated Windows
> account:   BLUEBOX\sqlworker
> The "sqlagent.exe" process runs under the same Windows
> account:   BLUEBOX\sqlworker

> The SQL Server Agent job (scheduled to run the above DTS
> package once per night) has the owner:
>    Owner:          BLUEBOX\sqlworker

> Currently, the job has failed each night, producting the
> following detailed error message:

> Executed as user: BLUEBOX\sqlworker. ...ing...   DTSRun
> OnStart:  DTSStep_DTSTransferObjectsTask_1   DTSRun
> OnProgress:  DTSStep_DTSTransferObjectsTask_1; Scripting
> objects for Transfer; PercentComplete = 0; ProgressCount
> = 0   DTSRun OnError:  DTSStep_DTSTransferObjectsTask_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:  5700        
> Error:  -2147467259 (800040.  The step failed.

> The server is a dual-processor Compaq running a Windows
> 2000 Server OS and SQL Server 2000. It is behind a
> firewall. After creating the job, I ran it manually
> (while still logged in via Terminal Services as
> BLUEBOX\jmb-sg), and the job completed successfully while
> I waited. In the past week, I have read many newsgroup
> threads, including in this forum, and have of course read
> the KB article 269074 many times over. At this point I
> haven't solved the issue, and would greatly appreciate
> your expert guidance. I've noticed that a few people have
> commented on related issues in the past, especially Allan
> Mitchell, and so hope that with the information I've
> provided we can figure out what's going on!

> Thank you in advance,
> JB

 
 
 

DTS Package fails when scheduled on server as SQL Server Agent job

Post by FgtH » Sun, 11 May 2003 18:38:55


A idea and a request :
idea : you say "between a production server and a dev. box" So it's
separates servers ? Are you sure to have the same account (NETWORK account)
on this box (if BLUEBOX is your machine name, so "BLUEBOX/jmb-sg" is local
to this machine, right ? Same for sqlworker). The "Access denied" message
seems to be the problem.
Have you read
http://support.microsoft.com/?kbid=269074 ?
now request : How have you this detailed messages through a shedule job ?
I don't have so detailed messages in my logs.


>> I have created a simple DTS package to copy a database
>> from our production server to a development box on a
>> nightly basis.
>> The production server's name is:  
>>        BLUEBOX

>> I created the DTS package (and scheduled it) while logged
>> into the server using Terminal Services. The Windows
>> account I log on as is:
>>        BLUEBOX\jmb-sg

>> The DTS package has just one task: "Copy SQL Server
>> Objects". The settings for this task are:
>>        Source database:       (local), using Windows
>> Authentication
>>        Destination database:   xx.xxx.xx.xx    (I.P. of
>> dev box),  using an SQL account on that box

>> The DTS package properties are:
>>        Owner/Creator:  BLUEBOX\jmb-sg
>>        Computer:       BLUEBOX

>> The "sqlservr.exe" process runs under a dedicated Windows
>> account:       BLUEBOX\sqlworker
>> The "sqlagent.exe" process runs under the same Windows
>> account:       BLUEBOX\sqlworker

>> The SQL Server Agent job (scheduled to run the above DTS
>> package once per night) has the owner:
>>        Owner:          BLUEBOX\sqlworker

>> Currently, the job has failed each night, producting the
>> following detailed error message:

>> Executed as user: BLUEBOX\sqlworker. ...ing...   DTSRun
>> OnStart:  DTSStep_DTSTransferObjectsTask_1   DTSRun
>> OnProgress:  DTSStep_DTSTransferObjectsTask_1; Scripting
>> objects for Transfer; PercentComplete = 0; ProgressCount
>> = 0   DTSRun OnError:  DTSStep_DTSTransferObjectsTask_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:  5700        
>> Error:  -2147467259 (800040.  The step failed.

>> The server is a dual-processor Compaq running a Windows
>> 2000 Server OS and SQL Server 2000. It is behind a
>> firewall. After creating the job, I ran it manually
>> (while still logged in via Terminal Services as
>> BLUEBOX\jmb-sg), and the job completed successfully while
>> I waited. In the past week, I have read many newsgroup
>> threads, including in this forum, and have of course read
>> the KB article 269074 many times over. At this point I
>> haven't solved the issue, and would greatly appreciate
>> your expert guidance. I've noticed that a few people have
>> commented on related issues in the past, especially Allan
>> Mitchell, and so hope that with the information I've
>> provided we can figure out what's going on!

>> Thank you in advance,
>> JB

--
- FgtH -
Il faut se garder d'hurler avec les loups de peur de n'tre qu'un mouton.
 
 
 

DTS Package fails when scheduled on server as SQL Server Agent job

Post by Andrew Oftheson » Wed, 28 May 2003 05:22:16


I deleted the dts from local packages, and save it to meta data services
packages, and it worked...



> I am having the same problem. Please let me know how to solve it, if
> you have any luck...

> OldJast



Quote:> > I have created a simple DTS package to copy a database
> > from our production server to a development box on a
> > nightly basis.
> > The production server's name is:
> > BLUEBOX

> > I created the DTS package (and scheduled it) while logged
> > into the server using Terminal Services. The Windows
> > account I log on as is:
> > BLUEBOX\jmb-sg

> > The DTS package has just one task: "Copy SQL Server
> > Objects". The settings for this task are:
> > Source database:       (local), using Windows
> > Authentication
> >    Destination database: xx.xxx.xx.xx (I.P. of
> > dev box),  using an SQL account on that box

> > The DTS package properties are:
> > Owner/Creator: BLUEBOX\jmb-sg
> > Computer: BLUEBOX

> > The "sqlservr.exe" process runs under a dedicated Windows
> > account: BLUEBOX\sqlworker
> > The "sqlagent.exe" process runs under the same Windows
> > account: BLUEBOX\sqlworker

> > The SQL Server Agent job (scheduled to run the above DTS
> > package once per night) has the owner:
> > Owner: BLUEBOX\sqlworker

> > Currently, the job has failed each night, producting the
> > following detailed error message:

> > Executed as user: BLUEBOX\sqlworker. ...ing...   DTSRun
> > OnStart:  DTSStep_DTSTransferObjectsTask_1   DTSRun
> > OnProgress:  DTSStep_DTSTransferObjectsTask_1; Scripting
> > objects for Transfer; PercentComplete = 0; ProgressCount
> > = 0   DTSRun OnError:  DTSStep_DTSTransferObjectsTask_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:  5700
> > Error:  -2147467259 (800040.  The step failed.

> > The server is a dual-processor Compaq running a Windows
> > 2000 Server OS and SQL Server 2000. It is behind a
> > firewall. After creating the job, I ran it manually
> > (while still logged in via Terminal Services as
> > BLUEBOX\jmb-sg), and the job completed successfully while
> > I waited. In the past week, I have read many newsgroup
> > threads, including in this forum, and have of course read
> > the KB article 269074 many times over. At this point I
> > haven't solved the issue, and would greatly appreciate
> > your expert guidance. I've noticed that a few people have
> > commented on related issues in the past, especially Allan
> > Mitchell, and so hope that with the information I've
> > provided we can figure out what's going on!

> > Thank you in advance,
> > JB

 
 
 

1. SQL Server Agent Error when running a scheduled DTS package

SQL Server 7.0 SP2
Windows NT Server 4.0

I have a scheduled DTS package that was worked fine for over 2 months until last
week.  With no changes on my part (I have no idea what my NT Admins may have done
and neither do they) the package now fails when scheduled as a job under the SQL
Agent.  The error message I am receiving is as follows:

[000] Password verification for the 'SQLAgentCmdExec' proxy account failed
(reason:  A required privilege is not held by the client)

The account I'm using as SQLAgentCmdExec proxy is a member of the server
administrator group, domain admins, can log on as a service, and has full NT
rights to the SQL Server directories and databases.

The account can also execute xp_cmdshell type jobs through the scheduler with no
problem.

All of my other T-SQL scheduled jobs run as expected.

I've been using the SQL agent since 1998 and have never seen this error message
before.

TIA,

Marc J. Miller, SSgt, USAF
NATO C2 Air Defence Systems Programmer

2. Too many tables involved in query..?

3. DTS does not Run from a scheduled SQL Server Agent Job

4. Help please with SQLNET connections

5. DTS package failed on sql server agent

6. Report Designer Component for Visual Basic 5.0

7. DTS Packages as SQL Server Agent Jobs Won't Run

8. Oracle 8 with RDO problem

9. Unable to run DTS package through Start Job in SQL Server agent

10. DTS package vs. SQL Server agent Job

11. Using a local DTS package in a SQL Server Agent Job

12. SQL Server Agent processed DTS jobs fail..

13. how can I import and export DTS package and SQL Server Agent between SQL Server Groups