Linked Server connecting to Oracle problem

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Linked Server connecting to Oracle problem

Linked Server connecting to Oracle problem

Rate Topic

Display Mode

Topic Options

Author

Message

LOOKUP_BI-756009

Posted Monday, February 08, 2010 2:13 PM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 2:31 PM
Points: 413, Visits: 1,253

I have SQL 2005 on my system and would like to connect to Oracle 10g as a linked server.This is the error mssg Im getting
OLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink".

This is what has been done so far
1) Installed Oracle Client for 10g on my machine
2) The C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN has 2 files [tnsnames] and [sqlnet]
3) Contents on tnsnames is as below
gp =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cs.org)(PORT = 1526))
)
(CONNECT_DATA =
(SERVICE_NAME = gp2)
)
)
4) Executing tnsping gp returns

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 08-FEB-2
010 16:02:10
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\10.2.0\client_1etwork\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = cs.org)(PORT = 1526))) (CONNECT_DATA = (SERVICE_NAME = gp2
01)))
OK (20 msec)

5) Execute sqlplus login/123@gp returns

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 8 16:04:35 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

6) In mngt studio, used following script to create linked server
EXEC sp_addlinkedserver @server = 'TestOraLink', @srvproduct = 'Oracle', @provider = 'OraOLEDB.Oracle', @datasrc = 'gp'

7) Followed by
EXEC sp_addlinkedsrvlogin 'TestOraLink', false, 'login', 'login', '123'

8) To test execute
SELECT * FROM OPENQUERY(TestOraLink, 'select * from test_table2')

Can someone please guide me on what is it that Im missing ? Thanks

Post #862065

LOOKUP_BI-756009

Posted Tuesday, February 09, 2010 9:26 AM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 2:31 PM
Points: 413, Visits: 1,253

All,

Please give me some tips or guidance on what can be done.

Thanks

Post #862530

LOOKUP_BI-756009

Posted Tuesday, February 09, 2010 9:38 AM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 2:31 PM
Points: 413, Visits: 1,253

If I remove the 'Allow In Process' on OraOLEDB.Oracle provider.I get the following error when executing
SELECT * FROM OPENQUERY(TestOraLink, 'select * from test_table2')

Error MSG
OLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink" returned message "ORA-01017: invalid username/password; logon denied".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink".

If 'Allow In Process; is selected,I receive the following message
OLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".

Post #862550

Gift Peddie

Posted Tuesday, February 09, 2010 9:41 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 29, 2013 11:17 AM
Points: 3,432, Visits: 14,332

The TNSNAMES.ORA error relates to the authenticated user permissions to the Oracle home folder. You could try connecting to oracle without linked server in BIDs and then try again. There is fixed way to fix it if I find it I will post it.

Kind regards,
Gift Peddie

Post #862553

LOOKUP_BI-756009

Posted Tuesday, February 09, 2010 9:45 AM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 2:31 PM
Points: 413, Visits: 1,253

Ok I shall try and see if it connects thru BIDS

Post #862556

Gift Peddie

Posted Tuesday, February 09, 2010 9:51 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 29, 2013 11:17 AM
Points: 3,432, Visits: 14,332

LOOKUP_BI-756009 (2/9/2010)

Ok I shall try and see if it connects thru BIDS

In a development box it is generally fixed by adding the TNSNAMES.ORA file in the Oracle home folder which I think you have done based on your original post. You should check the folder permissions to make sure the person in the TNSNAMES file is in the folder users list.

Kind regards,
Gift Peddie

Post #862561

Rudy Panigas

Posted Tuesday, June 07, 2011 10:56 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:38 AM
Points: 306, Visits: 1,012

No sure if this will help but we had a problem with a 64bit SQL server and Oracle support said to only install the 32bit client into the SQL server. It now works. For some reason the 64bit client does not work well with Windows 64 bit.

Hope this helps,

Rudy

Post #1121198

Syed Jahanzaib Bin hassan

Posted Wednesday, June 08, 2011 1:07 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, July 18, 2011 3:54 AM
Points: 492, Visits: 481

Post #1121544

« Prev Topic | Next Topic »

Permissions