In my environment we use LDAP to resolve connection strings.
My environment is setup so that TNS_ADMIN="C:\oracle\network", and within this folder is both a sqlnet.ora, and a ldap.ora.
I have both 32-bit and 64-bit oracle drivers installed on my box.
To test the drivers, I created a solution in VS2012, and added 3 console application projects.
I added the following script to all 3 programs:
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 1 FROM DUAL";
table.Load(command.ExecuteReader());
}
}
Console.WriteLine("Query Selected {0} Rows.", table.Rows.Count);
}
To the first project I added 32-bit Oracle.DataAccess, and set the compilation target to x86.
To the second I added 64-bit Oracle.DataAccess, and set the compilation target to x64.
To the third I added Oracle.ManagedDataAccess from nuget, and left the compilation target at AnyCpu.
When I run both the 32-bit tester, and the 64-bit tester - they run without an issue - they can resolve the alias, connect to the database and query fine.
When I run the managed tester - I get an ORA-12154 (could not resolve connect identifier) error.
I thought it was an issue with environment settings, but I set <setting name="TraceLevel" value="7" /> in the app.config, and looked at the trace log.
It states that successfully loads sqlnet.ora and ldap.ora, and that it connects to the correct LDAP server, and even prints out a list of all the TNS names it can find.
However it cannot find the alias in the LDAP server.
I tried changing the connection string to use the real name of the database instead of its alias, and all three tests worked correctly.
So it was clear that there was an issue with the managed library and resolving aliases.
I opened up JetBrains dotPeek, and decompiled the managed library, and followed along through the OracleConnection class, and found OracleInternal.Network.LDAP.
I replicated how this code connects to the LDAP server in my own code as follows:
string dbName = "alias";
SearchRequest sr = new SearchRequest(String.Format("cn={0},cn=OracleContext,dc=world", dbName), "(objectClass=orclNetService)", SearchScope.Base, new string[]
{
"orclNetDescString"
});
//[1]
SearchResponse resp;
using (LdapConnection ldapConnection = new LdapConnection(new LdapDirectoryIdentifier("onames.utilities.etsa.net.au", 389, false, false)))
{
ldapConnection.AuthType = AuthType.Anonymous;
LdapSessionOptions sessionOptions = ldapConnection.SessionOptions;
sessionOptions.ProtocolVersion = 3;
ldapConnection.Bind();
resp = (SearchResponse) ldapConnection.SendRequest((DirectoryRequest) sr);
}
Console.WriteLine("{0} is a{1}valid name.", dbName, (resp != null && resp.Entries.Count > 0) ? " " : "n i");
This code block executes and will return no results using the alias.
Again if you switch the alias for the real name, it works fine.
HOWEVER.
If you add the following line at position [1] in the code, the server will correctly resolve the alias and return the correct connection string.
sr.Aliases = DereferenceAlias.Always;
I looked through the decompiled managed library, and could not find this assignment anywhere - which would explain why the alias resolution fails.