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

linked-serveroracleoracle-11gsql serversql-server-2008

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?

Best Answer

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

Related Topic