How to connect to Oracle DB using TNS alias with Entity Framework 6?

.net entity-framework-6 oracle oracle-manageddataaccess visual-studio-2015

Question

In my configuration, an Entity Framework 6-based.NET application created with Visual Studio 2015 is attempting to retrieve data from an Oracle 12c database.

I performed the following to do that:

  • I followed a tutorial at oracle.com on how to use nuget to install the ODP.NET, Managed Entity Framework Driver for Oracle and its dependencies.
  • I checked to see whether tnsnames.ora and sqlnet.ora were accessible. (The environment variable TNS ADMIN has the appropriate settings.)
  • For the Oracle driver, I turned on trace logging so I could see what is really going on.
  • I produced a file called EDMX that maps certain entities (This already required the workaround described below)

Here is how my App.config looks (line breaks have been added to make it easier to read):

  <oracle.manageddataaccess.client>
    <version number="*">
      <settings>
        <setting name="TraceLevel" value="7" />
        <setting name="TraceOption" value="0" />
        <setting name="TraceFileLocation" value="C:\Temp" />
        <setting name="TNS_ADMIN" value="c:\Temp\tns" />
      </settings>
    </version>
  </oracle.manageddataaccess.client>
  <connectionStrings>
    <add name="MyModel"  
         connectionString="metadata=res://*/UDBModel.csdl|res://*/UDBModel.ssdl|res://*/UDBModel.msl;
                           provider=Oracle.ManagedDataAccess.Client;
                           provider connection string=&quot;User Id=*****;Password=*****;Data Source=MYDATASOURCE.WORLD;&quot;" 
         providerName="System.Data.EntityClient" />

This fails while attempting to access the database, with the following exception:

"ORA-12533: Netzwerksession: Syntaxfehler bei Verbindungstransportadresse"

which the oracle docs says translated to

"ORA-12533: TNS: illegal ADDRESS parameters"

The trace log demonstrates that the tnsnames.ora is appropriately resolved.

The connection is successful when the IP address and port are used in place of the TNS name. However, because our company's database administrators maintain tnsnames.ora, contacting the servers by their IP addresses is not an option.

I should also point out that with this configuration, older drivers (such the Oracle.DataAccess.dll) have no issues accessing the database.

EDIT: This is the tnsnames.ora file as I currently use it:

MYDATASOURCE.WORLD = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
    (ADDRESS = (PROTOCOL = IPC)(KEY = MYDATASOURCE))
    (ADDRESS = (COMMUNITY = TCP.world)(PROTOCOL = TCP)(Host = myds.example.com)(Port = 1530)))
    (CONNECT_DATA = (SID = MYDATASOURCE))
  )

Why am I doing this wrong?

NB: There are more questions like this. The remedies that worked in those situations did not work here, and the exception there was different from the one here:

1
2
5/23/2017 12:06:51 PM

Accepted Answer

ZZZ_tmp
2
10/5/2016 11:40:37 AM

Popular Answer

ODP.NET Managed Driver, I presume, cannot locate yourtnsnames.ora ODP.NET Managed Driver resolves the alias in the following order:

  1. alias for the data sourcedataSources section below<oracle.manageddataaccess.client> part of the.NET configuration file.
  2. alias for the data sourcetnsnames.ora file at the address provided byTNS_ADMIN the.NET configuration file. Both absolute and relative directory paths may be included in locations.
  3. alias for the data sourcetnsnames.ora a file that is located in the same directory as the.exe .

look at Developer's Guide for Data Provider for.NET

You can see that the ODP.NET Managed Driver doesn't examine environment variables to determine the value ofTNS_ADMIN . Examine the.NET configuration files (i.e.machine.config , web.config , user.config )

Oh, and you can run.set NLS_LANG=AMERICAN_GERMANY to get errors in English. Additionally, you may set this in your Registry atHKLM\SOFTWARE\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow