Hi there,

I wonder if anyone could point me to a resource where DTS autorization &
security is covered in-depth. I have this situation and I can't reason
why things are working and why NOT:

I have a DTSpackage on SQL1, which copies a table from SQL2 to SQL3.
When I run this this package from my terminal as SA, it works fine. When
I run the package from the server SQL1 as SA it works fine. Now beware!

When I schedule the package to a job on SQL1 and I start the job, the
job fails with an 'server does not exists or access denied' message.
:crying: This message is generated in the copy_task of the package. This
could mean, that the MSSQLServer on SQL2 has no autorization to connect
to SQL3, or that SQLServerAgent on SQL1 is not autorized for SQL3.
However, when I schedule a job on SQL1 with the statement 'select * from
SQL2.northwind.dbo.categories' the job runs fine; dito for SQL3. And
when I execute the package directly, it works ok.

I have not tried this yet for SQL2, but by now my head is aching:blink:,
have to take a break.

I think I have a ghood understanding on SQL Server security, but now I
am doubting myself. Do I miss something here? Or is autorization in a
mix of DTS packages and jobs really this nauseating?

Please, any hints apreciated.

Tnx,

Hans Brouwer

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

If the Job is owned by a "sysadmin" then it will execute in the context of
the SQL Server Agent Account. If not then it executes under the Proxy
account.

http://support.microsoft.com/?kbid=269074

So for your Job either the Agent account or the Proxy account on SQL1 need
the necessary permissions onto SQL2 and SQL3

Remember the system account cannot see off it's own box.

--
--

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


"hansje" <hansjes (AT) anonymous (DOT) com> wrote