ORA-12154 error trying to connect using ODP .NET

Question

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

Do you have more than one Oracle Home configured on your machine? — sstan, Dec 10 '15 at 16:16
As far as I know there is only one. Unless installing odp .net via nuget created another one. — Derek, Dec 10 '15 at 16:17
I notice that your connection via `sqlplus` is done using `@localhost/db6`. Are you able to connect with `sqlplus` using only `@db6`? Have you also tried running `tnsping db6` from the command line? — sstan, Dec 10 '15 at 16:19
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 http://stackoverflow.com/questions/28280883/determining-locatation-of-relevant-tnsnames-ora-file/28283924#28283924 — Wernfried Domscheit, Dec 10 '15 at 16:24
The TNS_ADMIN answer fixed it for me - I don't think I would have ever figured that out. Are you sure you sent the correct link? I didn't see how it was related. — Derek, Dec 10 '15 at 16:30
Wernfried - feel free to add an answer and I will accept and upvote. — Derek, Dec 10 '15 at 16:30

score 9 · Accepted Answer · answered Jun 03 '16 at 11:19

9

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

answered Jun 03 '16 at 11:19

Derek

7,615
5
33
58

Or your TNS_ADMIN environment variable is in place but pointing to the wrong location as it did in my case: C:\ORACLE\ORA12C\NETWORK\ADMIN Instead of C:\oracle\product\12.2.0\client_1\Network\Admin – Klaus Nji Jun 04 '18 at 18:07

score 2 · Answer 2 · edited May 01 '18 at 01:37

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.

In modern versions of ODP.NET we started relying on config file entries and so what likely happened to you is there is a TNS_ADMIN entry in the devenv.exe.config or in machine.config. — Christian Shay, May 01 '18 at 19:51

score 1 · Answer 3 · answered Jun 22 '16 at 00:23

1

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.

answered Jun 22 '16 at 00:23

Jeff

8,020
34
99
157

2

In modern versions of ODP.NET we started relying on config file entries and so what happened to you is there is a TNS_ADMIN entry in the devenv.exe.config or in machine.config. – Christian Shay May 01 '18 at 19:50
This happened again and this time it was an incorrect setting in the machine.config file. Updated it (requires that the editor be opened in administration mode) and that fixed the problem! Thanks Christian! – Jeff Nov 02 '18 at 21:01

ORA-12154 error trying to connect using ODP .NET

3 Answers3