Re: Linked Server (Oracle 9i)

From: Cirrosi <CirrosiN_O-S_P_A-M_at_fastwebnet.it>
Date: Wed, 3 Nov 2004 00:45:23 +0100
Message-ID: <q_Uhd.88$gO1.75_at_tornado.fastwebnet.it>


Are you sure you are connecting to sql server with sa username? (check in enterprise manager registration properties if you are using sa or windows autentication)
try to use
sp_addlinkedsrvlogin 'TURLIVE', false, null, 'sonica','******'

Excuse me for my bad english.

"David Gray" <police_at_spamcop.net> ha scritto nel messaggio news:0n5fo0pldddgt35repsc11d2s5jdp1rfe0_at_4ax.com...
>
> Hello all,
>
> Having problems connecting to an Oracle 9i database from within
> SQL/Server 2000 using the Security/Linked Servers feature.
>
> Server1 (SQL/Server)
> -----------
> Windows Server 2003, Standard edition
> MS SQL/Server 2000
> Oracle 9i Client kit (OLEDB & ODBC) & Enterprise management tools
> Microsoft ODBC for Oracle
> Oracle OLEDB
> MDAC 2.8 RTM
>
>
>
> Server2 (Oracle)
> -----------
> Windows 2000 - Advanced Server
> Oracle 9i database (v9.2.0.1.0)
> Two nodes clustered using Microsoft cluster manager. (Nodes are
> DATABASE01 & DATABASE02 - Cluster is WMCLUSTER)
>
>
>
> When I try to connect to the linked server in Enterprise Manager I get
> the following error messages.
>
> Error 7399 OLE DB provider 'MSDAORA' reported an error. Authentication
> failed.
>
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> IDBInitialize::Initialize returned 0x80040e4d: Authentication
> failed.].
>
>
> From within Query analyzer I get a slightly different message
> reporting that the username/password are incorrect.
>
> dbcc traceon(7399)
> select * from TURLIVE..SONICA.INV_LOC
>
>
> Server: Msg 7399, Level 16, State 1, Line 3
> 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.].
>
> I know the username/password combination is correct and I can use
> these from with Oracle enterprise Manager with sucess.
>
> TURLIVE is the name I've given the linked server, SONICA is the name
> of the schema on the Oracle database and INV_LOC is a valid table.
> TURLIVE is also the name of the database instance on Server2.
>
>
> Steps taken so far
>
> Install Oracle client tools (Enterprise Manager, Net manager etc) on
> Server1.
>
> Setup an entry in TNSNAMES.ORA to the cluster that has the Oracle
> database. e.g.
>
> TURLIVE =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = WMCLUSTER)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = TURLIVE)
> )
> )
>
> This works fine, I can connect via Oracle Enterprise manager and I can
> TNSPING WMCLUSTER, DATABASE01 & DATABASE02.
>
> Configured an ODBC source to TURLIVE.
>
> On Server1 I've configured the linked server using the following SQL.
>
> sp_addlinkedserver 'TURLIVE', 'Oracle', 'MSDAORA', 'TURLIVE'
> sp_addlinkedsrvlogin 'TURLIVE', false, 'sa', 'sonica','******'
>
> (password blanked)
>
> I then rebooted Server1
>
> The properties of the new linked server are:
>
> Product name = Oracle
> Data Source = TURLIVE
> Provider String = blank
>
> I've modifed the registry on Server1 as instructed by a Microsoft KB
> article.
>
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI\
>
> OracleXaLib = "oracleclient8.dll"
> OracleSqlLib = "orasql8.dll"
> OracleOciLib = "oci.dll"
>
>
> Still no luck. Can anyone please point out he bleeding obvious? :-)
> Thanks in advance
>
>
> As an aside, has anyone ever configured a linked server to an Oracle
> Rdb (Previously DEC Rdb running on OpenVMS and DEC UNIX) database?
> Are there any HOWTO guides for this type of connectivity?
>
>
>
>
>
> Cheers
> Dave.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
Received on Wed Nov 03 2004 - 00:45:23 CET

Original text of this message