Monday, June 1, 2015

Configure Oracle Gateway 12c to Query SQL Server DB from Oracle DB

Below are the steps to install and configure Oracle Gateway 12c [winx64_12102_gateways] on Windows 2008 Enterprise R2 OS to query Microsoft SQL Server database from Oracle Database.

Oracle Gateway 12c Installation Steps

From the software directory run the setup.exe file as an administrator. Then complete the below 8 steps wizard.

ODBC System DSN Configuration to connect to Microsoft SQL Server database

On the server create a new ODBC System DSN Name as below to connect to Microsoft SQL Server Database and test the connectivity.

Oracle Gateway 12c Configuration
a. Update the Oracle sqlnet.ora, tnsnames.ora & listener.ora files as part of gateway configuration.

b. Create a new initSQLServerSID.ora file as below,

c. Start the Listener service on the Gateway server,

d. Update the C:\Windows\System32\drivers\etc\hosts file on the Gateway server and add the below entry,       localhost

e. Verify the successful TNSPING for the SQL Server's TNSENTRY that you did in the above tnsnames.ora file.

Configuration on Oracle Database Server to connect to SQL Server via the new Oracle 12c Gateway

Perform the below steps on any oracle database server from where you want to access data from Microsoft SQL Server database via the new Oracle Gateway 12c server,

a. Add the TNSENRY of Gateway server on the Oracle Database Server,


b. Now create a database link object to connect to the SQL Server database.

CONNECT TO <SQL Server DB Username>
IDENTIFIED BY <SQL Server DB User password>

c. Now you can run the below query from Oracle database to get the result from SQL Server XXMYSQLDB Database.


For any queries please don't hesitate to contact me on