SO I figured I’ll post a quick solution to this problem that had been troubling me today.

I have a .Net application connecting to an Oracle Database. I have installed the ODP.Net data connection libraries. I created the Oracle connection successfully within Entity Framework, and used LINQ to retrieve the data. All is well… until this solution is deployed this onto a server…

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

20131018_1700_1

The error message seems to imply a Listener configuration is missing on the Oracle server. However, this doesn’t explain why the solution works fine on my local machine. It’s got to be something different with the server right ?

Here’s a copy of the Connecting String used by the Entity Framework. Take note of the Data Source value:

<add
name="MyOracleEntities"
connectionString="metadata=res://*/MyOracleModel.csdl|res://*/MyOracleModel.ssdl|res://*/MyOracleModel.msl;
                  provider=Oracle.ManagedDataAccess.Client;provider connection string=&quot;
                  data source=MyOracleDB;
                  user id=testuser;
                  password=testpass;
                  persist security info=True&quot;"
providerName="System.Data.EntityClient"/>

Steps

1. Installed the ODP.Net data access libraries — no luck.

2. Installed the Oracle Client — no luck.

3. Googled’ the error message.. and it got even more confusing. Search results goes on to talk about the Oracle Server itself, Listeners, TNSNames, updating Oracle configurations etc etc..  — Wrong path

3. Dived into the TNSNames.ORA file.. Made sure the contents on the server were the same contents as my local machine — no luck

4. Just about to call it a day, since by that time I had already worked up a headache.. then.. I found it!

So here is a sample if the TNSNames.ORA file.


MyOracleDB =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MyOracleDB)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MyOracleSID
    )
  )

The Solution

Rather than just specifying “MyOracleDB” as the Data Source in the connection string.. even though it worked fine on the local machine, changing the data source property to have a value similar to the contents of the TNSNames.ORA file provided success.

So, here’s the updated connection string for the .NET web.config or app.config file:

<add
name="MyOracleEntities"
connectionString="metadata=res://*/MyOracleModel.csdl|res://*/MyOracleModel.ssdl|res://*/MyOracleModel.msl;
                  provider=Oracle.ManagedDataAccess.Client;
                  provider connection string=&quot;
                  data source=(DESCRIPTION =
                                      (ADDRESS = (PROTOCOL = TCP)(HOST = MyOracleDB)(PORT = 1521))
                                      (CONNECT_DATA =
                                               (SERVER = DEDICATED)
                                               (SERVICE_NAME = MyOracleSID)
                                      )
                  )
                  user id=testuser;
                  password=testpass;
                  persist security info=True&quot;"
providerName="System.Data.EntityClient"/>

Success!! 🙂
Hope this helped!