1

With Oracle.ManagedDataAccess.dll 12 and 18, if you want to use tnsname.ora aliases, then you have to define the Environment Variable TNS_ADMIN with the corresponding path.

I just discovered that the version 19 is able to get the tnsname.ora configuration without this Environment Variable but I'm not sure how?

Neither the web.cong nor the machine.config contains that Environment Variable.

There seems to be a path in Registry Editor but my project still works when I modify the path with erroneous one.

enter image description here

From what I have tested, here is a list of what works and what doesn't :

  • Local client 19.3 with Oracle.ManagedDataAccess 19.3 (and superior) : Works without TNS_ADMIN Env
  • Local client 19.3 with Oracle.ManagedDataAccess 18.6 (and prior) : Doesn't work without TNS_ADMIN Env
  • Local client 12.1 with Oracle.ManagedDataAccess (all versions) : Doesn't work without TNS_ADMIN Env

So i'm wondering why does this particular case work?

Also is there a method inside Oracle.ManagedDataAccess that return the used tnsname.ora path?

Thanks,

Ldoppea
  • 347
  • 3
  • 15
  • 1
    I know earlier versions of ODP.NET Managed driver did not read the registry at all. Run [Process Monitor](https://learn.microsoft.com/de-de/sysinternals/downloads/procmon) and check what is actually read by the application. – Wernfried Domscheit Dec 24 '19 at 14:53
  • 1
    Value of `TNS_ADMIN` might be taken from cache. Ensure you reboot the computer between the tests. – Wernfried Domscheit Dec 24 '19 at 14:56
  • 1
    Have a look at https://stackoverflow.com/questions/28280883/determining-location-of-relevant-tnsnames-ora-file/28283924#28283924 - resolution for `tnsnames.ora` file seems to be volatile in ODP.NET Managed driver. – Wernfried Domscheit Dec 24 '19 at 14:58
  • Thx for your answers, the Process Monitor option really helped :) – Ldoppea Dec 26 '19 at 09:29

2 Answers2

1

I don't have any experience specifically with Oracle.ManagedDataAccess.dll, but I do know that in general, no client requires TNS_ADMIN to be set. The TNS layer itself has an order of precedence where it looks to find tnsnames.ora, and this is outside of the calling client. First it looks for a TNS_ADMIN environment variable. If that is not set, or tnsnames.ora is not found where TNS_ADMIN points, it next checks the process's current directory. If not found there, it checks $ORACLE_HOME/network/admin. I'd say your statement "if you want to use tnsname.ora aliases, then you have to define the Environment Variable TNS_ADMIN with the corresponding path" is a conclusion you came to based on incorrect interpretation of either reading or observation.

EdStevens
  • 3,708
  • 2
  • 10
  • 18
  • Hi, Thanks for your answer. You are right, my statement is a bit excessive. But what I meant was that if nothing is found by the client, then the easiest solution is to set the TNS_ADMIN env variable. By default the oracle install does not set anything for he ManagedDataAccess library, you should run configuraiton scripts by yourself, so in general the proposed solution is to set the TNS_ADMIN at the project level so you cannot have side effects on your install. (here again, like you said, it is a personal conclusion, maybe I'm wrong). – Ldoppea Dec 24 '19 at 15:43
  • 1
    OK, so, as I said, the process of locating tnsnames.ora (as well as sqlnet.ora) occurs after the client has passed the request to the TNS layer, and there it has at least two places it will look if TNS_ADMIN is not set at the OS process level. So that is why "version 19 is able to get the tnsname.ora configuration without this Environment Variable" – EdStevens Dec 24 '19 at 15:55
  • Ok. I realize my mistake after finding the solution. I was pretty sure none of those variables you mentioned were set, but I was not looking on the correct registry key. Thx again. – Ldoppea Dec 26 '19 at 09:28
1

Thanks all for your answers.

I finally found the solution using Process Monitor (thx @WernfriedDomscheit) and filtering the Path column with TNS_ADMIN.

There are two paths in the Registry Editor that contain the TNS_ADMINvariable:

  • HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET.Managed\4.122.19.1
  • HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ORACLE\ODP.NET.Managed\4.122.19.1

I was only trying to remove the non-WOW6432Node path. If I also remove the WOW6432Node one, then Oracle.ManagedDataAccess.dll successfully fail.

Ldoppea
  • 347
  • 3
  • 15
  • 1
    It depends on your compile settings. `HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE` is used when your application runs in 64-bit mode, whereas `HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ORACLE` is used by applications running in 32-bit mode. See [Registry Redirector](https://learn.microsoft.com/de-de/windows/win32/winprog64/registry-redirector?redirectedfrom=MSDN) from more details. – Wernfried Domscheit Dec 26 '19 at 13:40
  • 1
    Optionally you could also have `HKEY_CURRENT_USER\SOFTWARE\ORACLE\ODP.NET.Managed\4.122.19.1` key. – Wernfried Domscheit Dec 26 '19 at 13:42