ODP.NET Managed library does resolve alias, but 32-bit library does

Question

I have 32-bit drivers installed on my box (they were installed and configured by some DBAs)

I wrote a simple script to test the drivers which pretty much is as follows

using (DataTable table = new DataTable())
{
    using (OracleConnection connection = new OracleConnection())
    {
        connection.ConnectionString = "Data Source=alias;User id=user;Password=password";
        connection.Open();
        using (OracleCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.Text;
            command.CommandText = "SELECT * FROM DUAL";
            table.Load(command.ExecuteReader());
        }
    }
}

When I compile this application as 32-bit with the 32-bit Oracle.DataAccess.dll, it executes without a hitch.

However if I compile the application as AnyCPU with the Oracle.ManagedDataAccess.dll, i get an ORA-12154 (could not resolve the connect identifier specified) error.

If I tnsping alias, it works correctly and tells me the connect identifier with the real database name.

If I then change the connection string to use the real database name instead of the alias, and then try with the managed library again, it executes without a hitch.

I've been reading around and found this answer which says the managed driver relies on the tnsnames.ora file to resolve the aliases, however I rely on LDAP servers defined in sqlnet.ora and ldap.ora.

When I tnsping, it says it uses sqlnet.ora to resolve the name.

So how come the managed drivers do not work?

Answer 1

May this workaround is suitable for you. You can query the LDAP server by your own and put the full connection string to your code.

You can resolve the connection string from LDAP with this code:

using (OracleConnection connection = new OracleConnection())
{
    connection.ConnectionString = "Data Source=" + ResolveServiceNameLdap("alias") + ";User id=user;Password=password";
    connection.Open();
}

...

  private static string ResolveServiceNameLdap(string serviceName)
  {
     string tnsAdminPath = Path.Combine(@"C:\oracle\network", "ldap.ora");
     // or something more advanced...

     // ldap.ora can contain many LDAP servers
     IEnumerable<string> directoryServers = null;

     if ( File.Exists(tnsAdminPath) ) {
        string defaultAdminContext = string.Empty;

        using ( var sr = File.OpenText(tnsAdminPath) ) {
           string line;

           while ( ( line = sr.ReadLine() ) != null ) {
              // Ignore comments or empty lines
              if ( line.StartsWith("#") || line == string.Empty )
                 continue;

              // If line starts with DEFAULT_ADMIN_CONTEXT then get its value
              if ( line.StartsWith("DEFAULT_ADMIN_CONTEXT") )
                 defaultAdminContext = line.Substring(line.IndexOf('=') + 1).Trim(new[] { '\"', ' ' });

              // If line starts with DIRECTORY_SERVERS then get its value
              if ( line.StartsWith("DIRECTORY_SERVERS") ) {
                 string[] serversPorts = line.Substring(line.IndexOf('=') + 1).Trim(new[] { '(', ')', ' ' }).Split(',');
                 directoryServers = serversPorts.SelectMany(x => {
                    // If the server includes multiple port numbers, this needs to be handled
                    string[] serverPorts = x.Split(':');
                    if ( serverPorts.Count() > 1 )
                       return serverPorts.Skip(1).Select(y => string.Format("{0}:{1}", serverPorts.First(), y));

                    return new[] { x };
                 });
              }
           }
        }

        // Iterate through each LDAP server, and try to connect
        foreach ( string directoryServer in directoryServers ) {
           // Try to connect to LDAP server with using default admin contact
           try {
              var directoryEntry = new DirectoryEntry("LDAP://" + directoryServer + "/" + defaultAdminContext, null, null, AuthenticationTypes.Anonymous);
              var directorySearcher = new DirectorySearcher(directoryEntry, "(&(objectclass=orclNetService)(cn=" + serviceName + "))", new[] { "orclnetdescstring" }, SearchScope.Subtree);
              SearchResult searchResult = directorySearcher.FindOne();
              var value = searchResult.Properties["orclnetdescstring"][0] as byte[];
              if ( value != null )
                 connectionString = Encoding.Default.GetString(value);

              // If the connection was successful, then not necessary to try other LDAP servers
              break;
           } catch {
              // If the connection to LDAP server not successful, try to connect to the next LDAP server
              continue;
           }
        }

        // If casting was not successful, or not found any TNS value, then result is an error 
        if ( string.IsNullOrEmpty(connectionString) ) 
           throw new Exception("TNS value not found in LDAP");

     } else {
        // If ldap.ora doesn't exist, then throw error 
        throw new FileNotFoundException("ldap.ora not found");
     }

     return connectionString;
  }

Answer 2

ODP.NET Managed driver relies on TNS_ADMIN value being set in the config file to find the TNSNAMES.ORA file. If you don't want to set that value, you can include the TNSNAMES.ORA file in the working directory of the executable or create a TNS alias in the config file directly.

Edit: If you are relying on SQLNET.ORA and LDAP.ORA you can also copy those into the working directory as well, or set the LDAP parameters in your config file. See the ODP.NET doc for the config file parameters available for LDAP.