Oracle Managed Data Access Seems to Ignore Port When Using Alias

Question

I am getting the following error

ExceptionMessage: "ORA-12541: TNS: No listener"
ExceptionType: "Oracle.ManagedDataAccess.Client.OracleException"
InnerException: {Message: "An error has occurred.", ExceptionMessage: "ORA-12541: TNS: No listener",…}
ExceptionMessage: "ORA-12541: TNS: No listener"
ExceptionType: "OracleInternal.Network.NetworkException"
InnerException: {Message: "An error has occurred.",…}
ExceptionMessage: "No connection could be made because the target machine actively refused it {internal_ip}:1521"
ExceptionType: "System.Net.Sockets.SocketException"
Message: "An error has occurred."
StackTrace: "   at System.Net.Sockets.Socket.InternalEndConnect(IAsyncResult asyncResult)
↵   at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult)
↵   at OracleInternal.Network.TcpTransportAdapter.ConnectIterate()"
Message: "An error has occurred."
StackTrace: "   at OracleInternal.Network.OracleCommunication.DoConnect(String tnsDescriptor)
↵   at OracleInternal.ServiceObjects.OracleConnectionImpl.Connect(ConnectionString cs, Boolean bOpenEndUserSession, OracleConnection connRefForCriteria, String instanceName)"
Message: "An error has occurred."
StackTrace: "   at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)
↵   at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)
↵   at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword, OracleConnection connRefForCriteria)
↵   at Oracle.ManagedDataAccess.Client.OracleConnection.Open()

with the following alias in my config file. Notice that the port in the config file is 1522 but the error references port 1521.

<oracle.manageddataaccess.client>
  <version number="*">
    <dataSources>
      <dataSource alias="ConnectionOne" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST={host})(PORT=1522))(CONNECT_DATA=(SERVICE_NAME={service_name}))) " />
    </dataSources>
  </version>
</oracle.manageddataaccess.client>

Changing the connection string from

<connectionStrings>
    <add name="ConnectionOne"
       connectionString="user id = ; password = ; data source = ConnectionOne"/>
</connectionStrings>

to

<connectionStrings>
    <add name="ConnectionOne"
       connectionString="user id = ; password = ; data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST={host})(PORT=1522))(CONNECT_DATA=(SERVICE_NAME={service_name})))"/>
</connectionStrings>

fixes the problem.

From the Oracle documentation (https://docs.oracle.com/cd/E63277_01/win.121/e63268/InstallManagedConfig.htm#ODPNT8161)

The following precedence order is followed to resolve the data source alias specified in the Data Source attribute in the connection string.

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.

So, I don't think it is being read from another location.

Does anybody know what might cause this?

Answer 1

I found TNS_ADMIN system variable caused all my headaches similar to yours. Added or removed depending on the situation fixes the problem.