DevOps, CI/CD and Automation

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