Oracle error: TNS: Listener was not given the SERVICE_NAME in CONNECT_DATA 1

Question

I found that error Visual stutio 2010 when I try to connect with Oracle database

http://i.stack.imgur.com/BtIKu.jpg

http://i.stack.imgur.com/q6ffE.jpg

Here is TNSNAMES.ORA:

TNS_ALIAS=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =188.11.32.22)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Here is sqlnet.ora

# sqlnet.ora Network Configuration File: F:\app\user\product\11.2.0\client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

What should I do now??

Answer 1

It is old post here but as I was in same situation and this forum comes up pretty top of google search then I decided to post my solution.

I tried to send XML request to Oracle server and got from one instance: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

The problem was in FQDN service_name. It tried to solve it over EZCONNECT but in Oracle 11g EZCONNECT does not send Service name at all.

Solution: 1. In "$ORACLE_HOME\database\network\admin\sqlnet.ora" use only TNSNAMES in NAMES.DIRECTORY_PATH like:

NAMES.DIRECTORY_PATH= (TNSNAMES)

1. In "$ORACLE_HOME\database\network\admin\tnsnames.ora" create a additional section with FQDN. Like:

   EXAMPLE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = example.domain.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = x99.domain.com) (SID=X) ) )

   EXAMPLE.DOMAIN.COM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = example.domain.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = x99.domain.com) (SID=X) ) )

2. Use tnsping utilite to ping both names: 1) tnsping example; 2) tnsping example.domain.com - both names must answer.

NB! Use your own HOST, SERVICE_NAME AND SID of cource ;)

I hope that it helps someone.

BR

Raul

Answer 2

You need to use the shorthand version when setting the DataSource property of the Connection string. The entries in your TNSNames file will translate to this

var conBuiler = new OracleConnectionStringBuilder();
//DataSource = "HOST:PORT/SERVICE_NAME"
conBuilder.DataSource = "example.domain.com:1521/x99.domain.com"
conBuilder.UserId = "SomeUser";
conBuilder.Password = "Password123";

var orCon = new OracleConnection(conBuilder.ConnectionString);