I've been biting my teeth on this one for a looooong time :( !
I am trying to create a DB_LINK to connect my Oracle DB > SQL Server.
Here are my files:
listener.ora:
# C:\app\oracle\product\21c\dbhomeXE\network\admin\listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mychvwz2mss01.xxx.ch)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = dg4odbc)
(ORACLE_HOME = C:\app\oracle\product\21c\dbhomeXE)
(PROGRAM = dg4odbc)
)
)
tnsnames.ora:
# C:\app\oracle\product\21c\dbhomeXE\network\admin\tnsnames.ora
DG4ODBC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mychvwz2mss01.xxx.ch)(PORT = 1521))
(CONNECT_DATA = (SID = dg4odbc))
(HS = OK)
)
# initdg4odbc.ora (Gateway init file)
# Must match your 64-bit System DSN name from ODBC Administrator:
HS_FDS_CONNECT_INFO = MSSQL_READONLY;ApplicationIntent=ReadOnly;Encrypt=no;TrustServerCertificate=yes
HS_FDS_SHAREABLE_NAME = C:\Windows\System32\msodbcsql18.dll
# Logging (turn on only when troubleshooting)
HS_FDS_TRACE_FILE_NAME = dg4mssql_trace
HS_FDS_TRACE_FILE_PATH = C:\Temp
#HS_FDS_TRACE_LEVEL = OFF
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_TRACE_FILE_COUNT = 10
HS_FDS_TRACE_FILE_SIZE = 20000000
# Optional niceties (safe defaults)
HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8
HS_KEEP_REMOTE_COLUMN_SIZE = ALL
HS_FDS_FETCH_ROWS = 100
HS_RPC_FETCH_SIZE = 500
DB_LINK:
SQL> CREATE DATABASE LINK DG4ODBC CONNECT TO "user" IDENTIFIED BY "password" USING 'DG4ODBC';
Database link created.
Then I tried to connect through the db link:
SQL> SELECT 1 FROM DUAL@DG4ODBC;
SELECT 1 FROM DUAL@DG4ODBC
*
ERROR at line 1: 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 DG4ODBC
NOTES:
1. I am not able to generate any log files for dg4mssql_trace!
2. There is no network issue - I am able to connect from the same box
3. When doing the connectivity test in ODBC - I get a success!
4. I am using Oracle 21c XE
5. listener status shows:
C:\Windows\system32>lsnrctl status
LSNRCTL for 64-bit Windows: Version 21.0.0.0.0 - Production on 14-AUG-2025 21:55:24
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mychvwz2mss01.xxx.ch)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 21.0.0.0.0 - Production
Start Date 14-AUG-2025 21:38:45
Uptime 0 days 0 hr. 16 min. 38 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\oracle\product\21c\dbhomeXE\network\admin\listener.ora
Listener Log File C:\app\oracle\product\21c\diag\tnslsnr\mychvwz2mss01\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mychvwz2mss01.xxx.ch)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=C:\APP\ORACLE\PRODUCT\21C\admin\XE\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "4adf447b032546ee9c58954e5e3f9f57" has 1 instance(s).
Instance "xe", status READY, has 2 handler(s) for this service...
Service "XE" has 1 instance(s).
Instance "xe", status READY, has 2 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Service "dg4odbc" has 1 instance(s).
Instance "dg4odbc", status UNKNOWN, has 1 handler(s) for this service...
Service "xepdb1" has 1 instance(s).
Instance "xe", status READY, has 2 handler(s) for this service...
The command completed successfully
6. When connecting to the SQL Server database I need to set the following parameter (apart from user/pass):
ApplicationIntent=ReadOnly
TrustServerCertificate=yes
7. The Oracle DB is running on Windows :(
→ I have been talking to CHATGPT5 for days now :( and still cannot pinpoint the issue !!!
What I also cannot understand is why I do not see ANY logs from:
HS_FDS_TRACE_FILE_NAME = dg4mssql_trace
HS_FDS_TRACE_FILE_PATH = C:\Temp
I have added 'Everyone' with Full rights - but I do not see any log files!