Resolve Error ORA-28500 while accessing Heterogenous Database link (ORACLE TO MSSQL)

After creating a heterogenous database link from Oracle database  to MSSQL Server, getting error message “ORA-28500”


SQL>select sysdate from dual@DBLINK_MSSQL;
                         *
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Wire Protocol driver]Connection refused. Verify Host
Name and Port Number. {08001}
ORA-02063: preceding 2 lines from MSSQL_INTG




Reason


$ telnet MSSQLDB 1433
MSSQLDB/1433: Name or service not known


$ telnet 10.x.x.x 1433
Trying 10.x.x.x…
Connected to 10.x.x.x (10.x.x.x).
Escape character is ‘^]’.
^]
telnet> logout
^CConnection closed by foreign host.




I am able to telnet to MSSQL database Server using IP address , but cannot telnet to MSSQL server using hostname

Solution


Contact your Network admin to allow access to 1433 using Servername (MSSQLDB)


(or)


Change the below configuration


1. Set Oracle Home to point to your Gateway Oracle HOME


2. Navigate to $ORACLE_HOME/dg4msql/admin


Change below values in initdg4msql.ora


changed HS_FDS_CONNECT_INFO from MSSQLDB to IP address


#
HS_FDS_CONNECT_INFO=10.x.x.x:1433//wfcdb
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER




3. Restart the listener in Gateway Oracle HOME


4. Verify the database link is working fine.


SQL>select sysdate from dual@DBLINK_MSSQL;


==>Above Query should return current system date.