CODEWITHSUNDEEP
.netoracle-databasevisual-studio-2015entity-framework-6oracle-manageddataaccess
Spontifixus
Spontifixus

Reputation: 6660

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:

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:

Upvotes: 2

Views: 7009

Answers (2)

atokpas
atokpas

Reputation: 3351

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

Upvotes: 2

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59602

I assume ODP.NET Managed Driverdoes not find your tnsnames.ora file. ODP.NET Managed Driver uses this order to resolve the alias:

  1. data source alias in the dataSources section under <oracle.manageddataaccess.client> section in the .NET config file.
  2. data source alias in the tnsnames.ora file at the location specified by TNS_ADMIN in the .NET config file. Locations can consist of either absolute or relative directory paths.
  3. data source alias in the tnsnames.ora file present in the same directory as the .exe.

See Data Provider for .NET Developer's Guide

As you see ODP.NET Managed Driver does not read environment variable to determine value of TNS_ADMIN. Check your .NET config files (i.e. machine.config, web.config, user.config)

btw, you can run set NLS_LANG=AMERICAN_GERMANY to get english error messages. You can set this also in your Registry at HKLM\SOFTWARE\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG

Upvotes: 2

Related Questions