打开Oracle OleDb连接成功，而托管驱动程序连接失败

问题描述

从C#中，我使用OleDb连接到Oracle，并执行以下操作:

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

那很好.

通过相同的过程，我尝试使用托管驱动程序执行以下操作进行连接:

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

失败并显示以下错误:

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

过去，当tnsnames存在问题时，我曾收到过类似的错误，但是我从来没有遇到过在托管驱动程序不起作用时OleDb可以工作的情况.

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.

推荐答案

ODP.NET受管驱动程序使用另一种方式查找tnsnames.ora. sqlnet.ora比OraOLEDB好.

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

例如，OraOLEDB读取注册表的TNS_ADMIN值，而ODP.NET托管驱动程序则不读取.请参阅确定相关tnsnames.ora文件的位置更多细节.

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.

一种解决方案是使用文件夹名称设置环境变量TNS_ADMIN，其中tnsnames.ora和sqlnet.ora文件位于该文件夹中.据我所知，环境变量优先于(几乎)ODP.NET托管驱动程序和OraOLEDB中的所有其他设置.

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.

这篇关于打开Oracle OleDb连接成功，而托管驱动程序连接失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！