Search code examples
.netoracle-databasevisual-studio-2015entity-framework-6oracle-manageddataaccess

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

In my setup I have a .NET application built with Visual Studio 2015 trying to access data on an Oracle 12c database using Entity Framework 6.

Here's what I did to acchieve that:

  • I followed a tutorial at oracle.com on how to use nuget to install the Official Oracle ODP.NET, Managed Entity Framework Driver and its dependencies.
  • I ensured that the tnsnames.ora and sqlnet.ora can be found. (The environment variable TNS_ADMIN is configured correctly)
  • I enabled trace logging for the oracle driver to see what is actually happening here.
  • I created an EDMX file that maps some entities (This already required the workaround described below)

Here is what my App.config looks like (linebreaks introduced to improve readability):

  <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" />

When trying to access the database this fails with an exception:

"ORA-12533: Netzwerksession: Syntaxfehler bei Verbindungstransportadresse"

which according to the oracle docs translates to

"ORA-12533: TNS: illegal ADDRESS parameters"

The trace log shows that the tnsnames.ora gets resolved correctly.

When using the IP-address and the port instead of the TNS name the connection works fine. But as the tnsnames.ora gets managed by the database administrators in our company, addressing the servers using their IP-address is not an option.

I should also note that older drivers (e.g. the Oracle.DataAccess.dll) have no problems accessing the database using this setup.

EDIT: Here is tnsnames.ora file as I use it now:

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))
  )

What am I doing wrong here?

NB: There are questions similar to this. However, the solutions that helped in that cases did not help here, and the exception encountered there are different from the one encountered here:

Solution

  • ORA-12533: TNS:illegal ADDRESS parameters

    Cause: An illegal set of protocol adapter parameters was specified. In some cases, this error is returned when a connection cannot be made to the protocol transport.

    Action: Verify that the destination can be reached using the specified protocol. Check the parameters within the ADDRESS section of TNSNAMES.ORA. Legal ADDRESS parameter formats may be found in the Oracle operating system specific documentation for your platform. Protocols that resolve names at the transport layer (such as DECnet object names) are vulnerable to this error if not properly configured or names are misspelled.

    COMMUNITY is unsupported network parameter in tnsnames.ora after Oracle version 10g. So remove this parameter and keep only the (PROTOCOL=tcp), this should fix. Also remove the first ADDRESS parameter which is indetended for Oracle Listener.ora file.

    Unsupported Parameters