MS SQL 2008 - Setup Oracle Linked Server [SERVICE_NAME not given error]

Question

I'm trying to setup a linked server on MS SQL 2008 R2 to an Oracle 11g server. I've got the Oracle client installed and sqlnet.ora, tnsnames.ora & listener.ora copied from the server and in place with the following values:

sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tnsnames.ora

ORACLE =

 (DESCRIPTION = 

   (ADDRESS = (PROTOCOL = TCP)(HOST = slug.blue.local)(PORT = 1521)) 
   (CONNECT_DATA = 
       (SERVER = DEDICATED) 
       (SERVICE_NAME = oracle.blue.local)
   )
 )

listener.ora

LISTENER =

  (DESCRIPTION_LIST =

      (DESCRIPTION =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
          (ADDRESS = (PROTOCOL = TCP)(HOST = slug.blue.local)(PORT = 1521))
       )
   )

but I keep getting this error when creating a new linked server

The linked server has been created but failed a connecton test. Do you want to keep the linked server?

Aditional information:

• An exception occurred while executng a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

• Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE".

  OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE" returned message

  "ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA." (Microsoft SQL Server, Error: 7303)

Can anyone spot any problems with my config?

Are there other ways to specify the SERVICE_NAME or does everything come from the .ora files in the oracle client?

Solution

I see nothing wrong with your TNSNAMES.ora file, syntactically. I was able to copy/paste into mine and change hostname and service name and successfully connect.

Try connecting with SQL*Plus. Does this give you the same error? Can you connect with the EZCONNECT syntax: sqlplus user@'slug.blue.local:1521/oracle.blue.local'?

Do you have more than one Oracle client installed on your local system?

Also, try a bare-bones TNSNAMES.ORA file, with just the entry you posted.

Also, did you try connecting with SQL*Plus using the tnsnames.ora file? I.e, sqlplus user@oracle

Comments

[cander]

Thanks fo the reply. I can connect with with EZCONNECT using sqlplus, just cut n' pasted your connstr in, added the user/password and it connects no problem. I have have been doing a bit of uninstall/reinstalling of the oracle client. Could some left over stuff (dll, reg keys...) be causing issues with MS SQL?

[DCookie]

A similar issue I came across was solved by a complete wipe of the client install, including deleting all Oracle registry keys, and reinstalling the client. Last post in this thread: forums.oracle.com/forums/thread.jspa?threadID=910386

[DCookie]

Since you can get logged in via EZCONNECT using your tnsnames settings, I would suspect a corrupted client installation.

[DCookie]

One other possibility is another entry in the tnsnames.ora file being invalid.

[cander]

Solved! I de-installed everything oracle and deleted a few left over files/dirs, then re-installed the oracle client, with a few reboots in between just to be sure, and am now very happy to be getting a whole new error [server returned message: invalid username/password]. Just need to figure out how to configure the linked server username/password (the method suggested on MS site doesn't work) and i'm all sorted. Thanks a lot @DCookie

[cander]

EDIT: Disregard comment about MS username/password method not working. It does work, I just lost focus after all the oracle client hassle and left CAPS LOCK on :-/