Opening Oracle OleDb connection succeeds, while managed driver connection fails

0

From C# I'm connecting to Oracle using OleDb doing the following:

String connectionString = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=x;Password=y;Data Source=z";

var connection = new System.Data.OleDbConnection(connectionString);
connection.Open();

That works fine.

From the same process, I'm trying to connect using the managed driver doing the following:

String connectionString = "USER ID=x;PASSWORD=y;DATA SOURCE=z";

var connection = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString);
connection.Open();

That fails with the following error:

Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-12154: TNS:could not resolve the connect identifier specified ---> OracleInternal.Network.NetworkException (0x00002F7A): ORA-12154: TNS:could not resolve the connect identifier specified
   at OracleInternal.Network.AddressResolution..ctor(String TNSAlias, String instanceName)
   at OracleInternal.Network.OracleCommunication.DoConnect(String tnsDescriptor)
   at OracleInternal.ServiceObjects.OracleConnectionImpl.Connect(ConnectionString cs, Boolean bOpenEndUserSession, String instanceName)

Server stack trace: 
   at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)
   at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)
   at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword)
   at Oracle.ManagedDataAccess.Client.OracleConnection.Open()

I've received errors like this in the past when there was a problem with tnsnames, but I've never had a situation where OleDb works when the managed driver doesn't.

I've double checked and the user, password, and datasource are identical in the two connection strings.

c#
.net
oracle
oracle-manageddataaccess
asked on Stack Overflow Apr 13, 2016 by Mike W. • edited Apr 13, 2016 by Mike W.

2 Answers

0

Sounds like your TNSNames isnt setup correctly. You could also do a connection string like this to get around using tnsnames

static string constr = @"Data Source=(DESCRIPTION=
         (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=yourhostname )(PORT=1521)))
         (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));
        User Id=system ;Password=yourpasswrd"; 

this is error ORA-12154: TNS:could not resolve the connect identifier specified?

answered on Stack Overflow Apr 13, 2016 by Wjdavis5 • edited May 23, 2017 by Community
0

ODP.NET Managed driver uses a different way of looking for tnsnames.ora, resp. sqlnet.ora than OraOLEDB does.

For example, OraOLEDB reads the Registry for TNS_ADMIN value, whereas ODP.NET Managed driver does not. See Determining locatation of relevant tnsnames.ora file for more details.

One solution is to set an Environment variable TNS_ADMIN with folder name where tnsnames.ora and sqlnet.ora files are located. As far as I know the Environment variables takes precedence over (almost) all other setting in both, ODP.NET Managed driver and OraOLEDB.

answered on Stack Overflow Apr 14, 2016 by Wernfried Domscheit • edited May 23, 2017 by Community

User contributions licensed under CC BY-SA 3.0