ORA-12154 error trying to connect using ODP .NET

Question

ORA-12154 error trying to connect using ODP .NET

UPDATE: Wernfried's answer fixed it for me.

Create an environment variable TNS_ADMIN=D:\oracle\product\12.1.0\dbhome_1\network\admin

Sqlplus reads TNS_ADMIN from Registry but ODP.NET Managed Driver does not read the registry. See also: OdbcConnection returning Chinese Characters as "?"

You can check that the environment variable is set by:

string tns_admin = Environment.GetEnvironmentVariable("TNS_ADMIN")

I didn't quite follow how the link he suggested was relevant.

Original question:

ORA-12154 error trying to connect using ODP .NET

The code:

  OracleConnection oracleConnection = new OracleConnection();
  string connectionString = "User Id=redacted;Password=redacted;Data Source=db6";
  oracleConnection.ConnectionString = connectionString;
  oracleConnection.Open();

The error:

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Oracle.ManagedDataAccess.Client.OracleException: ORA-12154: TNS:could not resolve the connect identifier specified ---> OracleInternal.Network.NetworkException: ORA-12154: TNS:could not resolve the connect identifier specified
   at OracleInternal.Network.AddressResolution..ctor(String TNSAlias, String instanceName)
   at OracleInternal.Network.OracleCommunication.DoConnect(String tnsDescriptor)
   at OracleInternal.Network.OracleCommunication.Connect(String 

My tnsnames.ora:

# tnsnames.ora Network Configuration File: D:\oracle\product\12.1.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

DB6 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = redacted.redacted.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db6)
    )
  )

LISTENER_DB6 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = redacted.redacted.com)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

Connecting with sql-plus:

C:\Users\Derek.Morin\Documents\Visual Studio 2010\Projects\ScriptCode\Oracle>sqlplus redacted/redacted@localhost/db6

SQL*Plus: Release 12.1.0.1.0 Production on Thu Dec 10 09:10:14 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Tue Sep 22 2015 09:41:19 -05:00

Connected to:
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

Answer 1

Wernfried's comment fixed it for me. Since he didn't come back to post it as an answer - I will close this with his comment.

Create an environment variable

TNS_ADMIN=D:\oracle\product\12.1.0\dbhome_1\network\admin

sqlplus reads TNS_ADMIN from Registry but ODP.NET Managed Driver does not read the registry. See also stackoverflow.com/questions/28280883/… – Wernfried Domscheit Dec 10 '15 at 16:24

Answer 2

I second Jeff's observation. I've been trying to figure this out for a couple of days, and that was the key. I did a search on all extant tnsnames.ora files on the server in question, renamed every one that wasn't in %ORACLE_HOME/network/admin, and everything just started to work!

I recently uninstalled 32 bit Oracle drivers and re-installed 64 bit drivers... for a short period of time, the only tnsnames.ora file was the sample file (in the sample subdirectory)... perhaps the bad link (which HAD to be in the registry, I've had that TNS_ADMIN environment variable the whole time) was forged at that point? Not sure, but even though the variable appears to help some people, the water can be very muddy in regards to this issue.

Answer 3

Note: I had this problem in an SSRS project using ODP.Net and in my case the ODP.Net defaulted my TNS to "C:\Program Files (x86)\Oracle Developer Tools for VS2015\network\admin" (in spite of the fact I had a TNS_ADMIN environmental variable that was for something else. I went into that directory and renamed the sample tnsnames.ora and sqlnet.ora files to something different (i.e. junk-tnsnames.ora) it started working. Crazy! Hopefully this helps someone else.