Connection to Oracle using Oracle.ManagedDataAccess exception

Question

I'm using the Oracle.ManagedDataAccess Nuget Package Version 12.2.1100 in my C# (>.NET 4.0) project. Everything works ok in my localhost but on the dev server I'm hit with this exception:

Exception Message: ORA-12154: TNS:could not resolve the connect identifier specified Exception Source: Oracle Data Provider for .NET, Managed Driver

Now I thought the ManagedDataAcess contained everything I needed. Am I missing something else? Is something else interfering with the package? Do I need to add something else?

Note: there is no <oracle.manageddataaccess.client> tag in my Web.config

Code:

<connectionStrings>
   <add name="XXX" connectionString="Data Source=XXX;User ID=XXX;Password=XXX" />
</connectionStrings>

EDIT:

I've confirmed that the TNS_ADMIN variable is set within Control Panel but that didn't seem to do the trick.

I then added the tnsnames.ora file to the bin folder and I've got it working but it isn't a long term solution.

If there is no environment variable TNS_ADMIN, does this need to be created? Is the root of the issue, no TNS_ADMIN e.v.? — user94614, Jan 22 '18 at 19:10

score 7 · Accepted Answer · answered Jan 22 '18 at 21:39

7

Your program does not find the tnsnames.ora (resp. sqlnet.ora) file. There are several possibilities to specify the location.

Define it in .NET config file (web.config, machine.config, application.config)
Set environment variable TNS_ADMIN
Copy tnsnames.ora, sqlnet.ora files to directory where your application .exe is located.

Example for .NET config file:

<oracle.manageddataaccess.client>
  <version number="4.122.*">
     <settings>
          <setting name="TNS_ADMIN" value="C:\oracle\network\admin"/>
     </settings>
  </version>
</oracle.manageddataaccess.client>

Note, unlike other drivers/providers the ODP.NET Managed driver does not read the TNS_ADMIN setting from Registry.

answered Jan 22 '18 at 21:39

Wernfried Domscheit

54,457
9
76
110

Although this is an option, I prefer not needing to set up a tnsnames.ora and sqlnet.ora file. I find it far more cumbersome than having a self-contained connection string. Hence my own answer. – mason Jan 22 '18 at 21:40
I asked you whether you have a `tnsnames.ora` file. Hence your answer was "yes" I provided the solution. If you don't like `tnsnames.ora` you can define the alias also in .NET config file or provided by Oracle name server or put the whole TNS hard coded into your connection string. – Wernfried Domscheit Jan 23 '18 at 07:57
No, you didn't ask me, because I'm not the one who asked the question. I'm just commenting on your answer. – mason Jan 23 '18 at 13:15
@mason, Sorry I missed that. – Wernfried Domscheit Jan 23 '18 at 13:22
You could slso creste a symbolic link – Wernfried Domscheit Jan 25 '18 at 17:28
@WernfriedDomscheit the thing i do not understand in my case is `Oracle.ManagedDataAccess.Client` worked without any issue on my local and test environment. But, when i publish my code to prod environment, i got that oracle exception. If this is the case with Managed Data Access, how it could read tnsnames.ora file on test environment without explicitly stating where its directory is – RaZzLe Sep 01 '22 at 11:12
Most common is to use environment variable `TNS_ADMIN`. I think it always takes precedence. – Wernfried Domscheit Sep 01 '22 at 14:56
I find out how it works on my local without explicit definition on `web.config`. After I have downloaded it via NuGet, it also automatically created an `app.config` under my respective lib, which exactly contains this definition – RaZzLe Sep 06 '22 at 07:52

mason · Answer 2 · 2018-01-23T14:29:28.877

5

You probably don't have TNS configured, which is why that form of connection string isn't working. You don't need TNS configured if you use a different form of connection string, ex:

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MyIpOrServerName)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MySID)));User Id=MyUsername;Password=MyPassword;

Replace all the My* placeholders with your values.

edited Jan 23 '18 at 14:29

answered Jan 22 '18 at 17:44

mason

31,774
10
77
121

I tried your suggestion but I'm ending up with `Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-12154: TNS:could not resolve the connect identifier specified ---> OracleInternal.Network.NetworkException (0x00002F7A): ORA-12154: TNS:could not resolve the connect identifier specified` – user94614 Jan 22 '18 at 20:45
@AirJer Did you make sure that it's actually using your connection string? You sure it's not using some default connection string? Can you show the code and how you populate the connection string? – mason Jan 22 '18 at 21:24
Yes, the log show it is using the correct connection string and no default. I'm afraid there is not much else to show. The connection string is the same as you've posted. I'll look again tonight and see if I can find anything new. Thanks for the suggestions so far. – user94614 Jan 22 '18 at 21:34
Can you amend your question to show the code you're using, as an [MCVE]? – mason Jan 22 '18 at 21:41
This connection string would fit for ODBC. It must be `Data Source=...;User Id=myUsername;Password=myPassword;` instead of `SERVER=...;uid=myUsername;pwd=myPassword;`, see https://www.connectionstrings.com/oracle/ – Wernfried Domscheit Jan 23 '18 at 07:52
@WernfriedDomscheit If you look at the link you posted yourself, look under the heading "Omitting tnsnames.ora" where you'll find the form of connection string I used, which is appropriate for Oracle. I have used it in the past. Without ODBC. – mason Jan 23 '18 at 13:17
@mason, no that is for the "Microsoft .NET Framework Data Provider for Oracle" (btw, which is [deprecated](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/oracle-and-adonet)). For Oracle ODP.NET Managed Driver your connection string does not work, at least I get an error. See different variant here: https://stackoverflow.com/questions/34803106/how-to-connect-to-oracle-11-database-from-net/34805999#34805999 – Wernfried Domscheit Jan 23 '18 at 13:36
@WernfriedDomscheit The label might say it's for the deprecated provider, but I can assure you it works because I currently use that form of connection string, with the managed Oracle driver. No errors, had multiple applications using it for years. – mason Jan 23 '18 at 14:02
Strange I get `Exception has been thrown by the target of an invocation. 'SERVER' ist ein ungültiges Verbindungszeichenfolge-Attribut` (Version 4.121.1.0, 4.121.2.0 and 4.122.1.0) - but let's leave it as it is. There is no reason to argue. – Wernfried Domscheit Jan 23 '18 at 14:24
@WernfriedDomscheit Turns out you're right, the connection string did need to be tweaked slightly, I checked my existing application code. I've edited the answer. Thanks! – mason Jan 23 '18 at 14:29
@user94614 I've updated the connection string in the answer. Give it another shot? – mason Jan 23 '18 at 14:30

score 2 · Answer 3 · answered Jun 13 '18 at 23:35

We were seeing similar issues in one of our environments and the following solved our problem;

When Oracle Data Provider for .NET is installed on a web server and depending on what you select during install, it writes entries into the machine.config file (located at C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\ depending on the framework version). It seems different versions of the ODP.NET installer do different things to the machine.config (some of our servers have no version number specified in the machine.config and others have a specific version number specified for the oracle managed client)

Depending on the version installed, it adds a couple of lines like this:

...

<section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />

...

<oracle.manageddataaccess.client>
  <version number="4.121.2.0">
    <settings>
      <setting name="TNS_ADMIN" value="C:\oracle\client\product\12.1.0\client_1\network\admin" />
    </settings>
  </version>
</oracle.manageddataaccess.client>

...

The entries contain a specific version number referring to the ODP.NET oracle client version. We are building our applications with version 4.122.1.0 of the managed client library (Version 12.2.1100 nuget package) which doesn't match 4.121.2.0

We changed the above entries by removing the oracle managed client library version from this tag:

<section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess" />

And specifying a '*' for the version number for this tag:

<oracle.manageddataaccess.client>
    <version number="*">
      <settings>
        <setting name="TNS_ADMIN" value="C:\oracle\client\product\12.1.0\client_1\network\admin" />
      </settings>
    </version>
  </oracle.manageddataaccess.client>

If you need specific version numbers specified then ensure your code is compliled with the same version numbers.

You can also remove all these entries from machine.config if they are there and specify them in the applications web.config depending on your configuration.

score 0 · Answer 4 · answered Jul 03 '20 at 23:38

A simple way in my case.
Set ORACLE_HOME environment variable in Program.cs(Entrypoint class)
without any changes or settings in app.config

[STAThread]
static int Main()
{
    var oracleHome = GetOracleHome(); // Find registry...
    Environment.SetEnvironmentVariable("ORACLE_HOME", oracleHome);

    Application.EnableVisualStyles();
    Application.SetCompatibleTextRenderingDefault(false);
    ...

Connection to Oracle using Oracle.ManagedDataAccess exception

4 Answers4