Modify Connection For Oracle Database Gateway To SQL Server

Google did not help me on this one and had to RTFM. Hope this helps you.

During installation, connection to SQL Server was defined; now, another SQL Server should be used instead.

Need to modify initdg4msql.ora where $ORACLE_HOME is the Gateway Home.

db01-oracle:/home/oracle$ cat $ORACLE_HOME/dg4msql/admin/initdg4msql.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server

#
# HS init parameters
#
# Michael Dinh : Oct 25, 2023
# HS_FDS_CONNECT_INFO=[VMXXENTPOCD01]:1434//TEST_BACKUP
HS_FDS_CONNECT_INFO=[AVSFHEQDDB01]:1433//LATEST
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

db01-oracle:/home/oracle$

Create database link from Oracle to SQLServer database.

SQL> CREATE PUBLIC DATABASE LINK SQLSERVER1 CONNECT TO TESTADMIN IDENTIFIED BY "latest123" USING 'dg4msql';

Database link created.

SQL> select * from LA@SQLSERVER1;
select * from LA@SQLSERVER1
              *
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The SELECT permission was denied on the object 'LA', database
'LaTest', schema 'dbo'. {42000,NativeErr = 229}[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The metadata
could not be determined because every code path results in an error; see previous errors for some of these. {HY000,NativeErr =
11529}
ORA-02063: preceding 2 lines from SQLSERVER1
SQL>

For SQLServer, provided dbreader role to TestAdmin. I am not aware of all the privleges as another team is managing SQLServer.

SQL> col name for a15
SQL> col address  for a15
SQL> col zip for a15
SQL> select * from LA@SQLSERVER1;

Name            Address         Zip
--------------- --------------- ---------------
Michael         Address A       123-4567
Clyde           Address B       345-6789
Mahesh          Address C       456-6789

SQL>