Problems with DTS package. Access denied

Hi,

I made a DTS-package and it works when I execute it manually, but when it is
run by the SQL Server Agent, it fails.

I have use the guide to create a maintenance plan. That doesn't work so good
either. It runs Optimizations, but not integrity checks or backups (probably
because integrity checks failed).

The following from the log file:
Executed as user: HT-DOMAIN\INTRAB-SQL. ...:

DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnError:
DTSStep_DTSDataPumpTask_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: Cannot open a log file of specified name. Access is denied.
Error source: Micr... Process Exit Code 1. The step failed.

Log-file endeth here....

My "data-sources"/Connections is set up to use sql-server
authentication where I enter superuser name+password, not windows
authentication.

If I browse around using SQL-EM:
Under Security - Logins HT-DOMAIN\intrab-sql:
Tab General: Grant Access
Tab Server Roles: System Administrators
Tab Database Access: checkmark at Mbestil, user=dbo; database roles
for mbestil = public + db_owner

I also have som problems identifying where the package fails. I have given
the individual "steps"/"transformations" some pretty good names, but in the
log-file it still shows the "old" names.

The DTS package empties a table, then fills it by copying data from another
table in another database on another server.

Anyone with some useful tips ?

/jimIn the services management you will find SQL Agent is
configured with a user account on the operating system.
What OS are you on?

This OS account needs the appropriate DB access that is
equivalent to yours when running interactively.

Once this is fixed, because SQL Agent handles the automated
tasks, your backups, reorganisations, maintenance plans,
DTS tasks and anything else you need to automate in
the environment should have no problems in running
unattended.

Hope this helps.

Pete Brown
Falls Creek
Oz

"Jim Andersen" <jimVK@.officeconsult.dk> wrote in message
news:btdvco$1ad$1@.sunsite.dk...
> Hi,
> I made a DTS-package and it works when I execute it manually, but when it
is
> run by the SQL Server Agent, it fails.
> I have use the guide to create a maintenance plan. That doesn't work so
good
> either. It runs Optimizations, but not integrity checks or backups
(probably
> because integrity checks failed).
> The following from the log file:

...[trim]...

> Log-file endeth here....
> My "data-sources"/Connections is set up to use sql-server
> authentication where I enter superuser name+password, not windows
> authentication.
> If I browse around using SQL-EM:
> Under Security - Logins HT-DOMAIN\intrab-sql:
> Tab General: Grant Access
> Tab Server Roles: System Administrators
> Tab Database Access: checkmark at Mbestil, user=dbo; database roles
> for mbestil = public + db_owner
> I also have som problems identifying where the package fails. I have given
> the individual "steps"/"transformations" some pretty good names, but in
the
> log-file it still shows the "old" names.
> The DTS package empties a table, then fills it by copying data from
another
> table in another database on another server.
> Anyone with some useful tips ?
> /jim|||mountain man wrote:
> In the services management you will find SQL Agent is
> configured with a user account on the operating system.

Found it.

> What OS are you on?

Will check (not at problem site right now)

> This OS account needs the appropriate DB access that is
> equivalent to yours when running interactively.

Why ? I can understand it, when it comes to the backup, etc. But I have
supplied the name and password in the Connection properties in the DTS
package. And I _think_ (will have to check) that it actually empties the
table (first step in the package).

> Once this is fixed, because SQL Agent handles the automated
> tasks, your backups, reorganisations, maintenance plans,
> DTS tasks and anything else you need to automate in
> the environment should have no problems in running
> unattended.

I sure hope it has some kind of domino-effect :-)

> Hope this helps.

Me too. Will get back.
thx
Jim

> "Jim Andersen" <jimVK@.officeconsult.dk> wrote in message
> news:btdvco$1ad$1@.sunsite.dk...
>> Hi,
>>
>> I made a DTS-package and it works when I execute it manually, but
>> when it is run by the SQL Server Agent, it fails.
>>
>> I have use the guide to create a maintenance plan. That doesn't work
>> so good either. It runs Optimizations, but not integrity checks or
>> backups (probably because integrity checks failed).
>>
>> The following from the log file:
> ...[trim]...
>> Log-file endeth here....
>>
>> My "data-sources"/Connections is set up to use sql-server
>> authentication where I enter superuser name+password, not windows
>> authentication.
>>
>> If I browse around using SQL-EM:
>> Under Security - Logins HT-DOMAIN\intrab-sql:
>> Tab General: Grant Access
>> Tab Server Roles: System Administrators
>> Tab Database Access: checkmark at Mbestil, user=dbo; database roles
>> for mbestil = public + db_owner
>>
>> I also have som problems identifying where the package fails. I have
>> given the individual "steps"/"transformations" some pretty good
>> names, but in the log-file it still shows the "old" names.
>>
>> The DTS package empties a table, then fills it by copying data from
>> another table in another database on another server.
>>
>> Anyone with some useful tips ?
>>
>> /jim|||"Jim Andersen" <jimVK@.officeconsult.dk> wrote in message
news:btgjk3$r7u$1@.sunsite.dk...
> mountain man wrote:

> > This OS account needs the appropriate DB access that is
> > equivalent to yours when running interactively.
> Why ? I can understand it, when it comes to the backup, etc.

I believe all tasks run via SQL Agent will try to use the SQL Agent
user account nominated in the services detail.

> But I have
> supplied the name and password in the Connection properties in the DTS
> package. And I _think_ (will have to check) that it actually empties the
> table (first step in the package).

Also check the name of the owner (you, admin?) of the automated package.
The SQL Agent account needs to have equivalent access on the database end.

> > Hope this helps.
> Me too. Will get back.

Good luck with it Jim. Sometimes it is a little fiddly getting SQL Agent
up and running for the first time. Also, there have been a multitude of
threads in here in the past concerning this very issue.

So if the MS doco is not conducive to the solution, try an advance google
through the mssql newsgroups only, for the term "SQL Agent". If all else
fails write back.

Pete Brown
Falls Creek
Oz|||mountain man wrote:
> "Jim Andersen" <jimVK@.officeconsult.dk> wrote in message
> news:btgjk3$r7u$1@.sunsite.dk...
>> mountain man wrote:
>>> This OS account needs the appropriate DB access that is
>>> equivalent to yours when running interactively.
>>
>> Why ? I can understand it, when it comes to the backup, etc.
> I believe all tasks run via SQL Agent will try to use the SQL Agent
> user account nominated in the services detail.
>> But I have
>> supplied the name and password in the Connection properties in the
>> DTS package. And I _think_ (will have to check) that it actually
>> empties the table (first step in the package).
> Also check the name of the owner (you, admin?) of the automated
> package. The SQL Agent account needs to have equivalent access on the
> database end.

User JIM is now system administrator equivalent on the server. I split the
package in 3 parts to avoid any misunderstandings about workflow. All
packages are owned by JIM

Pack1 copies data from a remote server table to local table X.
Pack2 massages data from local table X and copies it to another local table
Y.
Pack3 copies some other data from remote server to another local table.

And then 3 jobs in sql server agent, 1 minute apart (each task takes a
second or 2).
It is Pack2 that fails. "Error string: Cannot open a log file of specified
name. Access is denied."
What does that mean ?

All packages run fine when I execute them manually.

I would have expected it to be either pack1 or 3. But now I think I'm gonna
change pack3 so it reads AND massages data from the remote server table to
local table Y.

It just puzzles me...

/jim|||"Jim Andersen" <jimVK@.officeconsult.dk> wrote in message news:<bu5svi$s7t$1@.sunsite.dk>...
> mountain man wrote:
> > "Jim Andersen" <jimVK@.officeconsult.dk> wrote in message
> > news:btgjk3$r7u$1@.sunsite.dk...
> >> mountain man wrote:
> >>> This OS account needs the appropriate DB access that is
> >>> equivalent to yours when running interactively.
> >>
> >> Why ? I can understand it, when it comes to the backup, etc.
> > I believe all tasks run via SQL Agent will try to use the SQL Agent
> > user account nominated in the services detail.
> >> But I have
> >> supplied the name and password in the Connection properties in the
> >> DTS package. And I _think_ (will have to check) that it actually
> >> empties the table (first step in the package).
> > Also check the name of the owner (you, admin?) of the automated
> > package. The SQL Agent account needs to have equivalent access on the
> > database end.
> User JIM is now system administrator equivalent on the server. I split the
> package in 3 parts to avoid any misunderstandings about workflow. All
> packages are owned by JIM
> Pack1 copies data from a remote server table to local table X.
> Pack2 massages data from local table X and copies it to another local table
> Y.
> Pack3 copies some other data from remote server to another local table.
> And then 3 jobs in sql server agent, 1 minute apart (each task takes a
> second or 2).
> It is Pack2 that fails. "Error string: Cannot open a log file of specified
> name. Access is denied."
> What does that mean ?
> All packages run fine when I execute them manually.
> I would have expected it to be either pack1 or 3. But now I think I'm gonna
> change pack3 so it reads AND massages data from the remote server table to
> local table Y.
> It just puzzles me...
> /jim

You might find this KB article useful - it gives quite a lot of detail
on how scheduled DTS packages are executed, and which security
contexts are relevant:

http://support.microsoft.com/defaul...4&Product=sql2k

Simon