Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle ODBC SqlConnect() does not connect from remote machine

3478297Jun 30 2017 — edited Jun 30 2017

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

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 28 2017
Added on Jun 30 2017
3 comments
2,192 views