Hi,
I am using PInvoke to call the native ODBC API of the Oracle driver (sqora32.dll). When I run my code on the machine where the database is hosted, the connection works without any problems. But once I want to connect from a remote machine in the same local network, the connection fails. SqlGetDiagRec returns the following message: "ORA-12560: TNS:protocol adapter error" and I am not sure why this happens. When using SQLPlus from command line on the remote machine, I can connect to the database. But only when I use easy connect.
My aim is to connect to a Oracle database without having to configure any database specific data on the client for each connection (for example the tnsnames.ora). In my test environment I have an Oracle 12C Standard Edition running on Windows Server 2012 R2.
Do you have any idea, why my remote connections fail? Am I missing something here? I have already tried to pass the serverName in different ways like
- [hostName]
- [hostName]/[serviceName]
- [hostName]:[port]/[serviceName]
- (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=[hostName])(Port=[port]))(CONNECT_DATA=(serviceName)))
and so on but nothing worked yet. I have also tried to directly pass the IP instead of the FQDN as host name but without any success.
Here is my connection code:
public void Connect(string serverName, string userName, string password)
{
// Allocate environment handle
nSQLResults = OracleNative.SQLAllocHandle(ODBC32.SQL_HANDLE.ENV, ODBC32.SQL_HANDLE_NULL, out this.environmentHandle);
// Set ODBC3 environment handle
nSQLResults = OracleNative.SQLSetEnvAttr(environmentHandle, ODBC32.SQL_ATTR.ODBC_VERSION, ODBC32.SQL_OV_ODBC3, 0);
// Get connection handle
nSQLResults = OracleNative.SQLAllocHandle(ODBC32.SQL_HANDLE.DBC, environmentHandle, out this.connectionHandle);
// Open the connection
nSQLResults = OracleNative.SQLConnect(connectionHandle, serverName, ODBC32.SQL_NTS, userName, ODBC32.SQL_NTS, password, ODBC32.SQL_NTS);
if (nSQLResults == ODBC32.RetCode.ERROR)
{
Console.WriteLine("Could not establish connection.");
}
}
And these are the native methods I use:
[DllImport("sqora32.dll", CharSet = CharSet.Unicode, SetLastError = true)]
public static extern ODBC32.RetCode SQLAllocHandle(
ODBC32.SQL_HANDLE HnadleType,
IntPtr inputHandle,
out IntPtr outputHandle
);
[DllImport("sqora32.dll", SetLastError = true)]
public static extern ODBC32.RetCode SQLSetEnvAttr(
IntPtr EnvironmentHandle,
ODBC32.SQL_ATTR Attribute,
IntPtr ValuePtr,
Int32 StringLength
);
[DllImport("sqora32.dll", CharSet = CharSet.Unicode, SetLastError = true)]
internal static extern ODBC32.RetCode SQLConnect(
IntPtr ConnectionHandle,
string ServerName,
Int16 NameLength1,
string Username,
Int16 NameLength2,
string Authentication,
Int16 NameLength3
);
Thanks in advance for your help!
Best regards
David