Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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

share|improve this answer
    
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: 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.