oracle as linked server

Hi,

i want to set Oracle as a linked server to my MS SQL.
When I execute

EXEC sp_addlinkedserver
@.server = 'ORATEST',
@.provider='MSDAORA',
@.srvproduct='any',
@.datasrc='ORADB_F9.SAPIENS.COM'

and then trying to make sample select, I get the following:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error. Authentication failed.
[OLE/DB provider returned message: ORA-01017: invalid username/password; logon denied]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Please advice, where can I set up the username and password for my Oracle, so the things will work.Refer to books online for "Establishing Security for Linked Servers" topic for information. Make sure to use the same user where it does have privilege to access Oracle tables.|||Satya,


Thank you very much - I've forgotten about it.

Now the next question:
I did

sp_addlinkedsrvlogin
@.rmtsrvname = 'ORATEST',
@.useself = 'false',
@.rmtuser = 'hana',
@.rmtpassword = 'hana'

Now, when I run the following query:

select * from ORATEST...deptsales

, it says me the following:

Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'ORATEST' does not contain table 'deptsales'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='ORATEST', TableName='deptsales'].

I am sure that the table does exist, I could see it via Excel.|||Error is self-explanatory, when setting up the linked server map your local login to appropirate remote login.|||Originally posted by Satya
Error is self-explanatory, when setting up the linked server map your local login to appropirate remote login.

I am having this same error.. I am not sure what you mean by mapping local login to remote login. Where do we access the name of our remote login??|||Originally posted by Satya
Error is self-explanatory, when setting up the linked server map your local login to appropirate remote login.

I did it, as you can see from my previous post - did I do something wrong ?

I've created an antry for Oracle's login "hana". I am sure that the table "hana.deptsales" exists. What's wrong here ?

TIA|||How about privileges for the user Hana on that table?|||Hana is the table owner|||You forgot to mentioned the Database Name. The Oracle's query syntax should be like:

select * from SERVERNAME.DatabaseName.Owner.TableName

SVT|||Let me explain further the steps that you needed to do before query the Oracle database.

1) You'll need to install the Oracle client software on the SQL Server,
and reboot the server for the Oracle's DLL to register.
2) Run the
sp_addlinkserver 'TestOracle', 'OLEOracle', 'MSOracle', 'TSTORA'
(where:
'TestOracle' is the linkserver name
'OLEOracle' is the OLE DB Name
'MSOracle' is the Microsoft's OLE DB
'TSTORA' is the Net Service Name (create with Oracle NetConfig
Assistant)

3)Mapping Logins
EXEC sp_addlinkedsrvlogin 'TestOracle', FALSE*, Null, 'Scott', 'Tiger'

(Add "scott' with pwd as "tiger' on 'TestOracle', if * is set to TRUE means SQL Server will use the CURRENT login to connect to the link server, use "TRUE' only if the Logins of the 2 DB servers matched).

4) Test the connection. From the QA:

Select * from TESTOracle..Scott.EMP

Good luck,
SVT