Linking Oracle with SQL Server

Question

I'm trying to link SQL Server 2005 to an Oracle 10g database. I've installed the Oracle client on the SQL server and validated that I can connect to the Oracle database using both tnsping and sqlplus. When I try to run a query in SQL Server I get the following:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORA_CSSA2APD" 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 "ORA_CSSA2APD".

Any ideas? I've tried both of the following queries with no luck:

select * from openquery(ORA_CSSA2APD, 'select count(rowid) from eservice_op.agent')

select count(rowid) from ORA_CSSA2APD..eservice_op.agent

Answer 1

This issue happened to me, as well, but only with certain Windows user accounts. A combination of enabling the "Allow inprocess" provider option for the OraOLEDB.Oracle provide (SSMS > Server Objects > Linked Servers > Provides > OraOLEDB.Oracle), restarting the SQL Server Windows service and lastly adjusting the permissions on the TNSNAMES.ora file directly.

Answer 2

I suspect an environment setting. That is, your session is picking up the TNSNAMES.ORA file but the session underlying SQL Server is not. I'd check were ORACLE_HOME and, possibly, TNS_ADMIN are being set and pointing to.

Are you able to use the easy connect syntax for the database with the SQL Server connection .

IE replace ORA_CSSA2APD with hostname:1521/service_name