Re: Linked Server (Oracle 9i)
From: David Gray (police_at_spamcop.net)
Date: 11/03/04
- Previous message: Cirrosi: "Re: Linked Server (Oracle 9i)"
- In reply to: Cirrosi: "Re: Linked Server (Oracle 9i)"
- Next in thread: Raju: "Re: Linked Server (Oracle 9i)"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 03 Nov 2004 16:40:36 +0000
Thats the one, all works fine now. Thanks very much
Dave.
On Wed, 3 Nov 2004 00:45:23 +0100, "Cirrosi"
<CirrosiN_O-S_P_A-M@fastwebnet.it> wrote:
>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@spamcop.net> ha scritto nel messaggio
>news:0n5fo0pldddgt35repsc11d2s5jdp1rfe0@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.
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
- Previous message: Cirrosi: "Re: Linked Server (Oracle 9i)"
- In reply to: Cirrosi: "Re: Linked Server (Oracle 9i)"
- Next in thread: Raju: "Re: Linked Server (Oracle 9i)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
