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:







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




          (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?

1 Answer 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

  • 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, 2011 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, 2011 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, 2011 at 18:08
  • One other possibility is another entry in the tnsnames.ora file being invalid.
    – DCookie
    Apr 27, 2011 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, 2011 at 21:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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