DTS Oracle->SQL2000, who connects to ora?

[Werner]

- Using the wizard from SQL Enterprise Manager on a client PC I set up and
run a DTS package to import tables from a Oracle DB to SQL Server 2000. It
worked fine.

- Some investigation showed, that the connection to the Oracle Server was
made from the client PC (I think by SQL*Net), not from the SQL Server. Surprise!

- In case I want to schedule this package, the connection to Oracle definitely
has be made from the SQL Server. Right?

How can I influence in DTS, who connects to Oracle (in this example), as
long as I start the job in EntManager from my WS?

I assume: if I am sure, that the connection from the SQLServer to OraServer
works, it also should be easy to setup Oracle as a linked server in SQLServer?

[Rob Vieira]

It is actually SQL Server connecting to Oracle - the exact security context
depends on who is running the package - the scheduler or an individual user.
The SQL*Net name is just a generic name that I presume is being provided by
the ODBC or OLEDB driver (depending on which you're using).

Setting up Oracle as a linked server is not a big deal, and I believe there
is an example of it on BOL.

Rob


"Werner" <wzoller@nospam.remove.mbbm.de> wrote in message
news:3a6ff61e$1@news.devx.com...
>
> - Using the wizard from SQL Enterprise Manager on a client PC I set up
and
> run a DTS package to import tables from a Oracle DB to SQL Server 2000. It
> worked fine.
>
> - Some investigation showed, that the connection to the Oracle Server was
> made from the client PC (I think by SQL*Net), not from the SQL Server.
Surprise!
>
> - In case I want to schedule this package, the connection to Oracle
definitely
> has be made from the SQL Server. Right?
>
> How can I influence in DTS, who connects to Oracle (in this example), as
> long as I start the job in EntManager from my WS?
>
> I assume: if I am sure, that the connection from the SQLServer to
OraServer
> works, it also should be easy to setup Oracle as a linked server in
SQLServer?
>