{"id":4977,"date":"2024-04-05T07:00:00","date_gmt":"2024-04-05T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=4977"},"modified":"2024-04-05T12:54:13","modified_gmt":"2024-04-05T09:54:13","slug":"pos-mporoyme-na-syndesoyme-oracle-database-me-sql-server-me","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-mporoyme-na-syndesoyme-oracle-database-me-sql-server-me\/","title":{"rendered":"How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows"},"content":{"rendered":"

In a previous article we saw how we can execute queries on a remote SQL Server from an Oracle Database located on Linux via Oracle Database Gateway<\/strong>. In this article we will see how to install Oracle Gateway respectively on Windows.<\/p>\n\n\n\n

Oracle Gateway can be installed either on one of the database servers or on a third party machine that will act as an intermediary. In this example we will proceed with the installation on Windows SQL Server.<\/p>\n\n\n\n

<\/p>\n\n\n\n

Prerequisites<\/h2>\n\n\n\n

We should have downloaded the Oracle Gateway software for Windows from the official link to the Windows Server here<\/a>.<\/p>\n\n\n\n

The footsteps<\/h2>\n\n\n\n

On the SQL Server Instance<\/h4>\n\n\n\n

To begin with, we will have to create a login in the SQL Server Instance that will have read access to the database that we want to read from the Oracle Database. In our example we will create a user named oraclepw<\/em> and will have access to the base mig_db<\/em>:<\/p>\n\n\n\n

USE [master]\nGO\nCREATE LOGIN [oraclegw] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF\nGO\nUSE [mig_db]\nGO\nCREATE USER [oraclegw] FOR LOGIN [oraclegw]\nGO\nUSE [mig_db]\nGO\nALTER ROLE [db_datareader] ADD MEMBER [oraclegw]\nGO<\/pre>\n\n\n\n
Then we go to the Windows Server where Oracle Gateway will be installed and create one ODBC <\/strong>connection to the Oracle Database Server. To do this we are looking from the beginning ODBC Data Sources (64-bit) <\/em>and select \u2013> System DSN <\/em>\u2013> Add\u2026<\/em> .<\/p>\n\n\n\n
In the tab System DSN<\/em> we define a name and the server we will connect to. In the example, Oracle Gateway will be installed on the same machine as the target SQL Server, so we write in the Server field, localhost:<\/p>\n\n\n\n
\"\"
01<\/figcaption><\/figure>\n\n\n\n
Then we fill in the login credentials we created at the beginning to connect to SQL Server:<\/p>\n\n\n\n
\"\"
02<\/figcaption><\/figure>\n\n\n\n
After completing the ODBC creation, we can start installing the software. During the installation it will ask us to define the location where the installation will take place and with which components, in the components we choose Oracle Database Gateway for ODBC<\/em><\/strong>:<\/p>\n\n\n\n
\"\"
03<\/figcaption><\/figure>\n\n\n\n
When the installation is complete it will ask us to fix it listener<\/strong>, there we will define the port that will communicate between Oracle Gateway and Oracle Database:<\/p>\n\n\n\n
\"\"
04<\/figcaption><\/figure>\n\n\n\n
After the creation of the listener is complete, we should go to the path where we installed the Oracle Gateway and create an empty file init <\/strong>which contains the name of the ODBC we will call it like below:<\/p>\n\n\n\n
C:\\app\\tg\\user\\product\\19.0.0\\tghome_1\\hs\\admin\\initMIG_DB.ora<\/code><\/pre>\n\n\n\n
In this file we fill in the following where HS_FDS_CONNECT_INFO <\/code>we set the name of the ODBC we made and as HS_LANGUAGE <\/code>the encoding of the destination Oracle Database:<\/p>\n\n\n\n
# This is a customized agent init file that contains the HS parameters\n# that are needed for the Database Gateway for Microsoft SQL Server\n\n#\n# HS init parameters\n#\nHS_FDS_CONNECT_INFO=MIG_DB\nHS_FDS_TRACE_LEVEL=off\nHS_NLS_NCHAR=UCS2\nHS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8\nHS_FDS_FETCH_ROWS=1\r<\/code><\/pre>\n\n\n\n
Then we need to modify the listener.ora<\/strong> which is located in the corresponding path where we did the installation:<\/p>\n\n\n\n
C:\\app\\tg\\user\\product\\19.0.0\\tghome_1\\network\\admin\\listener.ora\n<\/code><\/pre>\n\n\n\n
And let's add the following entry defining as SID_NAME <\/code>the ODBC name we set to be the same as the Gateway init and as ORACLE_HOME <\/code><\/em>the corresponding path where the initial installation was made:<\/p>\n\n\n\n
 SID_LIST_LISTENER=\n   (SID_LIST=\n      (SID_DESC= \n        (SID_NAME=MIG_DB)\n         (ORACLE_HOME=C:\\app\\tg\\user\\product\\19.0.0\\tghome_1)\n         (PROGRAM=dg4odbc)\n      )\n   )<\/code><\/pre>\n\n\n\n
On Oracle Database Linux Server <\/h4>\n\n\n\n
Then on the Linux where the Oracle Database is located we need to add to tnsnames.ora<\/strong> record that looks at the Oracle Gateway we built, setting as HOST <\/code>its name or IP and as CONNECT_DATA <\/code>the name of the gateway we set in the listener:<\/p>\n\n\n\n
vi $ORACLE_HOME\/network\/admin\/tnsnames.ora<\/pre>\n\n\n\n
* Be careful to put the name \/ IP where the Oracle Gateway is installed as host.<\/em><\/p>\n\n\n\n
MIG_DB =\n  (DESCRIPTION=\n    (ADDRESS=(PROTOCOL=tcp)(HOST=SMATZOURANISLP)(PORT=1521))\n      (CONNECT_DATA=(SID=MIG_DB))\n      (HS=OK)\n    )<\/code><\/pre>\n\n\n\n
Finally we make it Database Link<\/strong>, specifying a name, the SQL Server login, the password and the name of the record we added to tnsnames.ora:<\/p>\n\n\n\n
sqlplus \/ as sysdba\n\nCREATE DATABASE LINK SQLDBLINK CONNECT TO \"oraclegw\" IDENTIFIED BY \"password\" USING 'MIG_DB';<\/pre>\n\n\n\n
The test<\/h2>\n\n\n\n
After creating the Database Link, we can query the Oracle Database to call the database tables in the SQL Server as below:<\/p>\n\n\n\n
select * from dbo.customers@sqldblink;<\/pre>\n\n\n\n
\"\"
05<\/figcaption><\/figure>\n\n\n\n
In the event that the following error appears to us:<\/p>\n\n\n\n
ERROR at line 1:\nORA-02085: database link SQLDBLINK connects to HO.WORLD<\/code><\/pre>\n\n\n\n
Then we need to disable them global names<\/strong> with the following command:<\/p>\n\n\n\n
alter system set global_names = false scope=both;<\/pre>\n\n\n\n
In the event that the error appears to us:<\/p>\n\n\n\n
ERROR at line 1:\nORA-28545: error diagnosed by Net8 when connecting to an agent\nUnable to retrieve text of NETWORK\/NCR message 65535\nORA-02063: preceding 2 lines from <DBLINK>\n<\/code><\/pre>\n\n\n\n
Then we add the following line to the SQLNET.ora<\/strong><\/em> Oracle Gateway file:<\/p>\n\n\n\n
C:\\app\\tg\\user\\product\\19.0.0\\tghome_1\\network\\admin\\SQLNET.ora<\/code><\/pre>\n\n\n\n
SQLNET.NO_NTLM=FALSE<\/pre>\n\n\n\n
Sources:<\/h2>\n\n\n\n