Problems with DTS package. Access denied

[Jim Andersen]

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 ?

/jim

[mountain man]

In 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" <jimVÆÆ[e-mail]> wrote in message
news:bt**********@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]

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" <jimVÆÆ[e-mail]> wrote in message
news:bt**********@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]

"Jim Andersen" <jimVÆÆ[e-mail]> wrote in message
news:bt**********@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

[Jim Andersen]

mountain man wrote:

"Jim Andersen" <jimVÆÆ[e-mail]> wrote in message
news:bt**********@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

[Simon Hayes]

"Jim Andersen" <jimVÆÆ[e-mail]> wrote in message news:<bu**********@sunsite.dk>...

mountain man wrote:

"Jim Andersen" <jimVÆÆ[e-mail]> wrote in message
news:bt**********@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/default...&Product=sql2k

Simon