Agfdhyk

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.

my configuration:

First server:

• Linux Centos 7 64 bit


• Oracle 18xe unixODBC 2.3.1


• ODBC driver: MSSQL ODBC V17 (libmsodbcsql-17.2.so.0.1)

Second server:
Windows 2012R2
MS SQL 11.0.6020.0

Configuration:

/etc/odbcinst.ini:

[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

/etc/odbc.ini:

[MSSQL]

Driver = ODBC Driver 17 for SQL Server

Server = 94.72.71.346

Database = DEMO

Port = 1433

Trace = yes

TraceFile /tmp/odbctrace.out

/opt/oracle/product/18c/dbhomeXE/hs/admin/initdg4odbc.ora:

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

/opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora:

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)

        )

    )

/opt/oracle/product/18c/dbhomeXE/network/admin/tnsnames.ora:

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))

/opt/oracle/product/18c/dbhomeXE/network/admin/sqlnet.ora

# 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

dblink:

CREATE DATABASE LINK "MAG"

  CONNECT TO "waproking" IDENTIFIED BY VALUES ':1'

  USING 'dg4odbc';

isql -v MSSQL user password works! queries also work!

Select from SQLDeveloper:

select * from table@mag;

returns the following error:

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

Oracle HS log:
/opt/oracle/product/18c/dbhomeXE/hs/log/dg4odbc_agt_13858.trc:

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

MSSQL Server Logs:

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]

the mssql database is configured in mixed mode, and connections to it operate from the oracle 11g database

my attention was drawn by an empty username in the MSSQL!

2018-11-20 21:27:40.75 Logon       Login failed for user ''

Anyone can suggest something? ;)