How do I setup tnsnames.ora in asp.net core web application?

Question

I have a small asp.net core 2.2 app that should access an oracle db; I'm using NuGet Oracle.ManagedDataAccess.Core (2.18.6), and it just works on my machine.

When I deploy it to the windows server with IIS, I place a tnsnames.ora file in app's directory and again it just works.

Now I want to use a shared tnsnames.ora file. I have tried web.config like following (recipy from StackOverflow/Google answers to similar questions).

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
  </configSections>

  <!-- skipping app stuff -->

  <oracle.manageddataaccess.client>
    <version number="*">
      <settings>
        <setting name="tns_admin" value="F:\path\to\tnsadmin\folder" />
      </settings>
    </version>
  </oracle.manageddataaccess.client>
</configuration>

Unfortunately, this doesn't work (and since the Core version of Oracle.ManagedDataAccess.dll doesn't have an ODPMSectionHandler class it isn't a big surprise).

So, is there a way to have a shared tnsnames.ora file with odp.net core?

(PS imo we need the odp.net-core tag)

UPDATE

One need to ensure the w3wp.exe can actually access the files.
Setting system environment variable TNS_ADMIN is a possible solution. It'll suffice in this particular case, but I'm still curious about how to configure it via web.config.

Define an Environment variable `TNS_ADMIN`. The environment variable should take precedence over all other settings, e.g. Registry (which does not apply for the managed driver) or .net config file. — Wernfried Domscheit, Apr 24 '19 at 09:52
@WernfriedDomscheit didn't work. Do you know if I need to restart the IIS for it to become aware of a new environment variable? — Tymur Gubayev, Apr 24 '19 at 10:04
@WernfriedDomscheit I actually restarted the IIS (`iisreset /noforce`) -- still doesn't work. — Tymur Gubayev, Apr 24 '19 at 10:22
Yes, most likely you have to restart the process. Did you define it in system environment or in user environment? — Wernfried Domscheit, Apr 24 '19 at 10:50
@WernfriedDomscheit it is in the "System variables"-section (also, just checked it is actually visible in a helper app via Environment.GetVariable) — Tymur Gubayev, Apr 24 '19 at 11:09
Then I don't know why ODP does not find the file. What is the error you get? — Wernfried Domscheit, Apr 24 '19 at 12:45
It's the standard `OracleException: ORA-12154: TNS: could not resolve the connect identifier specified` — Tymur Gubayev, Apr 24 '19 at 13:11
Have a look with [Process Monitor](https://learn.microsoft.com/en-us/sysinternals/downloads/procmon) if the `tnsnames.ora` file is found and taken. — Wernfried Domscheit, Apr 24 '19 at 13:16
ty, the monitor shows "ACCESS DENIED" for the TNS_ADMIN-path. I.e. the environment variable actually does work -- which is sufficient for my needs. But I'm still curious about how to set it in the `web.config`-file. — Tymur Gubayev, Apr 24 '19 at 14:05

score 0 · Answer 1 · answered May 25 '19 at 13:21

0

ODP.NET Core does not support config files like standard ODP.NET does. You will need to use the Configuration APIs:

https://docs.oracle.com/en/database/oracle/oracle-data-access-components/18.3/odpnt/InstallCoreConfiguration.html

answered May 25 '19 at 13:21

Christian Shay

2,570
14
24

score 0 · Answer 2 · answered Aug 09 '19 at 02:03

With Oracle.ManagedDataAccess you don't need to use a tnsnames.ora file. In fact, you don't need an Oracle client installed on the web server. Use startup.cs.

public void ConfigureServices(IServiceCollection services)
{                        
        services.AddEntityFrameworkOracle()
            .AddDbContext<OracleDbContext>(builder => builder.UseOracle(Configuration["Data:OracleDbContext"]),ServiceLifetime.Scoped)
            .AddDbContext<AppsDbContext>(option => option.UseOracle(Configuration["Data:AppsDbConnection:ConnectionString"]), ServiceLifetime.Scoped);
}

apsettings.json

{
 "Logging": {
"LogLevel": {
  "Default": "Warning"
}
},
"AllowedHosts": "*",
"Data": {
"OracleDbContext": "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=zzz)(PORT=1521))(CONNECT_DATA=(zzzz)));User Id=zzz;Password=zzz;" },
"AppsDbContext": "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=yyyy)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=yyyy)));User Id=yyyy;Password=yyyy;" }
}
}

But what if you are migrating from oracle client, and you already have the infrastructure in place for tns names? — Ryan Leach, Jul 29 '21 at 00:31

score 0 · Answer 3 · answered Apr 19 '22 at 11:17

I just ran into the same issue, strangely enough setting env variables system wide didn't seem to work. I had to configure it in the web.config itself for it to find the TNS_ADMIN, like so:

<aspNetCore ...>
  <environmentVariables>
    <environmentVariable name="TNS_ADMIN" value="C:\Oracle\product\11.2.0\client_1\network\admin" />
  </environmentVariables>
</aspNetCore>

How do I setup tnsnames.ora in asp.net core web application?

3 Answers3