DB Link from oracle -> ms sql ORA-28545 & ORA-02063

Question

We have Oracle 12c – installed on Windows Server 2016 and MS SQL Server 2017 – installed on Windows Server 2019.

When we try to use DB link from Oracle -> MS SQL via SELECT count(*) FROM SMSOUT@TOSIMSQLSERVER we obtain the following error:

ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from TOSIMSQLSERVER
28545. 0000 -  "error diagnosed by Net8 when connecting to an agent"
*Cause:    An attempt to call an external procedure or to issue SQL
           to a non-Oracle system on a Heterogeneous Services database link
           failed at connection initialization.  The error diagnosed
           by Net8 NCR software is reported separately.
*Action:   Refer to the Net8 NCRO error message.  If this isn't clear,
           check connection administrative setup in tnsnames.ora
           and listener.ora for the service associated with the
           Heterogeneous Services database link being used, or with
           'extproc_connection_data' for an external procedure call.
Error at Line: 1 Column: 29

The DB Link is created by CREATE DATABASE LINK TOSIMSQLSERVER CONNECT TO "oms" IDENTIFIED BY "…our_pwd…" USING 'simsqlserver';

\hs\admin\initsimsqlserver.ora:

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = simsqlserver
HS_FDS_TRACE_LEVEL = OFF

\network\admin\listener.ora:

# listener.ora Network Configuration File: C:\app\oracle\product\12.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\oracle\product\12.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle\product\12.2.0\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC =
      (SID_NAME = simsqlserver)
      (ORACLE_HOME = C:\app\oracle\product\12.2.0\dbhome_1)
      (PROGRAM = dg4odbc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle12.pds.opds.cz)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

\network\admin\tnsnames.ora:

# tnsnames.ora Network Configuration File: C:\app\oracle\product\12.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle12.pds.opds.cz)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oracle.pds.opds.cz)
    )
  )

LISTENER_ORACLE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracle12.pds.opds.cz)(PORT = 1521))

simsqlserver = 
(DESCRIPTION =
  (ADDRESS=(PROTOCOL=TCP)(HOST=oracle12.pds.opds.cz)(PORT=1521)) 
    (CONNECT_DATA=(SID=simsqlserver)) 
    (HS=OK)
)

\network\admin\sqlnet.ora:

# sqlnet.ora Network Configuration File: C:\app\oracle\product\12.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Can somebody help me find where is the problem ? The SQL ODBC data source test is OK. Also TNSPING to simsqlserver is OK.

EDIT 11.03.2020: Our Oracle is:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
"CORE   12.2.0.1.0            Production"
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

After setting HS_FDS_TRACE_LEVEL = 16 and after lsnrctl stop + lsnrctl start we have the following logs:

\oracle\diag\rdbms\oracle\oracle\trace\alert_oracle.log:

2020-03-11T13:27:40.813789+01:00
HS:  Unable to establish RPC connection to HS Agent...
HS:  ... Agent SID = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12.pds.opds.cz)(PORT=1521))(CONNECT_DATA=(SID=simsqlserver))), NCR error = 65535 Unable to retrieve text of NETWORK/NCR message 65535

HS:  Unable to establish RPC connection to HS Agent...
HS:  ... Agent SID = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12.pds.opds.cz)(PORT=1521))(CONNECT_DATA=(SID=simsqlserver))), NCR error = 65535 Unable to retrieve text of NETWORK/NCR message 65535

\oracle\diag\rdbms\oracle\oracle\alert\log.xml:

<msg time='2020-03-11T13:27:40.938+01:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='ORACLE12'
host_addr='::1' module='SQL Developer' pid='2952'>
<txt>HS:  Unable to establish RPC connection to HS Agent...
</txt>
</msg>
<msg time='2020-03-11T13:27:40.938+01:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='ORACLE12'
host_addr='::1' module='SQL Developer' pid='2952'>
<txt>HS:  ... Agent SID = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12.pds.opds.cz)(PORT=1521))(CONNECT_DATA=(SID=simsqlserver))), NCR error = 65535 Unable to retrieve text of NETWORK/NCR message 65535
</txt>
</msg>

Answer 1

Try removing the

SQLNET.AUTHENTICATION_SERVICES= (NTS) 

line from sqlnet.ora. That isn't part of the specified configuration for DG4ODBC, per MOS Doc ID 1266571.1.