Re: Linked Server (Oracle 9i)
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)
Excuse me for my bad english.
"David Gray" <police_at_spamcop.net> ha scritto nel messaggio
news:0n5fo0pldddgt35repsc11d2s5jdp1rfe0_at_4ax.com...
try to use
sp_addlinkedsrvlogin 'TURLIVE', false, null, 'sonica','******'
>
> 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