title: Oracle 18 XE HS MSSQL LINUX Connecting problem
body: <p>I have a problem with connecting to the MSSQL database via ODBC.
  The problem occurs with version 12c or 18xe, with the 11g version I connect with no problem.</p>
  
  <p>my configuration:</p>
  
  <p><strong>First server:</strong></p>
  
  <ul>
  <li>Linux Centos 7 64 bit </li>
  <li>Oracle 18xe unixODBC 2.3.1</li>
  <li>ODBC driver: MSSQL ODBC V17 (libmsodbcsql-17.2.so.0.1)</li>
  </ul>
  
  <p><strong>Second server:</strong>
  Windows 2012R2
  MS SQL 11.0.6020.0</p>
  
  <p><strong>Configuration:</strong></p>
  
  <p>/etc/odbcinst.ini:</p>
  
  <pre><code>[ODBC Driver 17 for SQL Server]
  Description=Microsoft ODBC Driver 17 for SQL Server
  Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
  </code></pre>
  
  <p>/etc/odbc.ini:</p>
  
  <pre><code>[MSSQL]
  Driver = ODBC Driver 17 for SQL Server
  Server = 94.72.71.346
  Database = DEMO
  Port = 1433
  Trace = yes
  TraceFile /tmp/odbctrace.out
  </code></pre>
  
  <p>/opt/oracle/product/18c/dbhomeXE/hs/admin/initdg4odbc.ora:</p>
  
  <pre><code>HS_FDS_CONNECT_INFO = MSSQL
  HS_FDS_TRACE_LEVEL = DEBUG
  HS_DB_NAME = DEMO
  HS_AUTOREGISTER = TRUE
  #HS_LANGUAGE = AL32UTF8
  HS_FDS_SHAREABLE_NAME = /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
  set ODBCINI=/etc/odbc.ini
  </code></pre>
  
  <p>/opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora:</p>
  
  <pre><code>DEFAULT_SERVICE_LISTENER = XE
  
  LISTENER =
    (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = vpsXXXXX.ovh.net)(PORT = 1521))
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
    )
  
  SID_LIST_LISTENER=
      (SID_LIST=
      (SID_DESC=
        (SID_NAME=dg4odbc)
        (ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE)
        (PROGRAM=dg4odbc)
          )
      )
  </code></pre>
  
  <p>/opt/oracle/product/18c/dbhomeXE/network/admin/tnsnames.ora:</p>
  
  <pre><code>XE =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vpsXXXXX.ovh.net)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = XE)
      )
    )
  
  dg4odbc =
    (DESCRIPTION=
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost) (PORT=1521))
      (CONNECT_DATA = (SID = dg4odbc))
      (HS=OK)
    )
  
  LISTENER_XE =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vpsXXXXX.ovh.net)(PORT = 1521))
  </code></pre>
  
  <p>/opt/oracle/product/18c/dbhomeXE/network/admin/sqlnet.ora</p>
  
  <pre><code># sqlnet.ora Network Configuration File: /opt/oracle/product/18c/dbhomeXE/network/admin/sqlnet.ora
  # Generated by Oracle configuration tools.
  
  #NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
  SQLNET.INBOUND_CONNECT_TIMEOUT = 0
  </code></pre>
  
  <p>dblink:</p>
  
  <pre><code>CREATE DATABASE LINK "MAG"
    CONNECT TO "waproking" IDENTIFIED BY VALUES ':1'
    USING 'dg4odbc';
  </code></pre>
  
  <p>isql -v MSSQL user password works! queries also work!</p>
  
  <p>Select from SQLDeveloper:</p>
  
  <pre><code>select * from table@mag;
  </code></pre>
  
  <p>returns the following error:</p>
  
  <pre><code>ORA-28511: utracono połączenie RPC z heterogenicznym odległym agentem korzystającym z SID=ORA-28511: utracono połączenie RPC z heterogenicznym odległym agentem korzystającym z SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=dg4odbc)))
  ORA-02063: poprzedzające line z MAG
  28511. 00000 -  "lost RPC connection to heterogeneous remote agent using SID=%s"
  *Cause:    A fatal error occurred in one of the following places
             -- the connection between the ORACLE server and the agent
             -- the heterogeneous services remote agent itself
             -- the connection to the non-Oracle system
             This error occurred after communication had been established
             successfully.
  *Action:   Check for network problems and remote host crashes. The problem is
             probably in the agent software. If so, contact a customer support
             representative of the agent vendor.
  Error at Line: 1 Column: 23
  </code></pre>
  
  <p>Oracle HS log:
  /opt/oracle/product/18c/dbhomeXE/hs/log/dg4odbc_agt_13858.trc:</p>
  
  <pre><code>Oracle Corporation --- 2018-11-20 21:31:04.582408000
  
  Heterogeneous Agent Release 
  18.0.0.0.0 
  
  Oracle Corporation --- 2018-11-20 21:31:04.582208000
  
      Version 18.0.0.0.0
  
  Entered hgogprd
  HOSGIP for "HS_FDS_TRACE_LEVEL" returned "DEBUG"
  Entered hgosdip
   setting HS_OPEN_CURSORS to default of 50
   setting HS_NLS_NUMERIC_CHARACTERS to default of ".,"
   HOSGIP returned value of "waproking" for HS_FDS_RECOVERY_ACCOUNT
   HOSGIP returned a value for HS_FDS_RECOVERY_PWD
   setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG
   setting HS_IDLE_TIMEOUT to default of 0
   setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
   setting HS_NLS_NCHAR to default of "AL32UTF8"
   setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"
   setting HS_FDS_DATE_MAPPING to default of "DATE"
   setting HS_FDS_TRUNC_ANSI_DATE to default of "OFF"
   setting HS_RPC_FETCH_REBLOCKING to default of "ON"
   setting HS_FDS_FETCH_ROWS to default of "100"
   setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
   setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"
   setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
   setting HS_FDS_MAP_NCHAR to default of "TRUE"
   setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
   setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
   setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
   setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
   setting HS_FDS_QUERY_DRIVER to default of "TRUE"
   setting HS_FDS_SUPPORT_STATISTICS to default of "FALSE"
   setting HS_FDS_QUOTE_IDENTIFIER to default of "TRUE"
   setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"
   setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"
   setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"
  Default value of 64 assumed for HS_FDS_SQLLEN_INTERPRETATION
   setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo;gtw$:OPTTables;gtw$:OPTColumns;gtw$:OPTPrimaryKeys;gtw$:OPTForeignKeys;gtw$:OPTProcedures;gtw$:OPTStatistics"
   setting HS_FDS_DELAYED_OPEN to default of "TRUE"
   setting HS_FDS_WORKAROUNDS to default of "0"
   setting HS_WORKAROUNDS to default of "0"
  Exiting hgosdip, rc=0
   ORACLE_SID is "dg4odbc"
   Product-Info:
    Port Rls/Upd:0/0 PrdStat:0
    Agent:Oracle Database Gateway for ODBC
    Facility:hsa
    Class:ODBC, ClassVsn:18.0.0.0.0_0024, Instance:dg4odbc
  Exiting hgogprd, rc=0
  HOA 11/20 21:31:04.583891000: (hotker_ExecuteRpcs) ncrorpi: status  = 0
  HOA 11/20 21:31:04.588171000: (hotker_ExecuteRpcs) ncrorpi: status  = 0
  Entered hgoinit
  HOCXU_COMP_CSET=1
  HOCXU_DRV_CSET=873
  HOCXU_DRV_NCHAR=873
  HOCXU_DB_CSET=873
  HS_LANGUAGE not specified
  LANG=en_US.UTF-8
  HOCXU_SEM_VER=122020
  HOCXU_VC2_MAX=4000
  HOCXU_RAW_MAX=2000
  Entered hgolofn at 2018/11/20-21:31:04
  HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1"
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd888b020
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88b1970
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88bcf20
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88ce080
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88bedf0
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd888c200
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88e7e80
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd885fb90
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88dc660
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd888b400
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd884e2f0
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88a45c0
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88ce7f0
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88cb230
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88dd000
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88b45e0
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd885bdd0
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88558e0
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88ac9e0
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88ce6b0
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88bacf0
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88b3f60
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88a6980
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd888f2f0
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88b5050
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd888c7f0
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd8860450
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88a7710
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88db300
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88b9ec0
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88b9690
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88c45d0
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88c45b0
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88cd980
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88d02d0
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88a8890
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd8857390
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88a7470
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88a8540
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88a8270
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88d9f90
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88cf8e0
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88bc190
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88a6cb0
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Entered hgolofns at 2018/11/20-21:31:04
   symbol_peflctx=0xd88a6620
   hoaerr:0
  Exiting hgolofns at 2018/11/20-21:31:04
  Exiting hgolofn, rc=0 at 2018/11/20-21:31:04
  HOSGIP for "HS_OPEN_CURSORS" returned "50"
  HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
  HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
  HOSGIP for "HS_NLS_NUMERIC_CHARACTERS" returned ".,"
  HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"
  HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
  HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
  HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
  HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
  Invalid value of 64 given for HS_FDS_SQLLEN_INTERPRETATION
  treat_SQLLEN_as_compiled = 1
  Exiting hgoinit, rc=0 at 2018/11/20-21:31:04
  Entered hgolgon at 2018/11/20-21:31:04
   reco:0, name:waproking, tflag:0
  Entered hgosuec at 2018/11/20-21:31:04
  Exiting hgosuec, rc=0 at 2018/11/20-21:31:04
  HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "waproking"
  HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"
  HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"
  HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"
  HOSGIP for "HS_FDS_TRUNC_ANSI_DATE" returned "OFF"
  HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
  HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"
  HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"
  HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
  HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
   using waproking as default schema
  HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
  Entered hgocont at 2018/11/20-21:31:04
   HS_FDS_CONNECT_INFO = "MSSQL"
   RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
  Entered hgogenconstr at 2018/11/20-21:31:04
   dsn:MSSQL, name:waproking
   optn:
  Entered hgocip at 2018/11/20-21:31:04
   dsn:MSSQL
  Exiting hgocip, rc=0 at 2018/11/20-21:31:04
  Exiting hgogenconstr, rc=0 at 2018/11/20-21:31:04
  Entered hgopoer at 2018/11/20-21:31:04
  </code></pre>
  
  <p>MSSQL Server Logs:</p>
  
  <pre><code>2018-11-20 21:27:40.75 Logon       Błąd: 18456; ważność: 14; stan: 58.
  2018-11-20 21:27:40.75 Logon       Login failed for user ''. Przyczyna: Próba logowania z użyciem uwierzytelniania programu SQL Server nie powiodła się. Serwer jest skonfigurowany tylko do obsługi uwierzytelniania systemu Windows. [KLIENT: XXX.XXX.XXX.XXX]
  </code></pre>
  
  <p>the mssql database is configured in mixed mode, and connections to it operate from the oracle 11g database</p>
  
  <p><strong>my attention was drawn by an empty username in the MSSQL!</strong></p>
  
  <pre><code>2018-11-20 21:27:40.75 Logon       Login failed for user ''
  </code></pre>
  
  <p>Anyone can suggest something? ;)</p>