1

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?

cander
  • 113
  • 1
  • 3

1 Answers1

1

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

DCookie
  • 2,098
  • 17
  • 18
  • 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? – cander Apr 27 '11 at 16:12
  • 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: http://forums.oracle.com/forums/thread.jspa?threadID=910386 – DCookie Apr 27 '11 at 18:06
  • Since you can get logged in via EZCONNECT using your tnsnames settings, I would suspect a corrupted client installation. – DCookie Apr 27 '11 at 18:08
  • One other possibility is another entry in the tnsnames.ora file being invalid. – DCookie Apr 27 '11 at 19:44
  • 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 Apr 27 '11 at 21:56
  • 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 :-/ – cander Apr 27 '11 at 22:37