How to configure Oracle Database Gateway for ODBC to access SQL Server data from Oracle Database over db links.
In Oracle, you can access MS SQL Server data using database link with Oracle Database Gateway (formerly Oracle Heterogeneous Services). With either of these two options
The focus of this post in the second option of setting up Database Gateway for ODBC to access SQL Server. Before we start, I am assuming you have some basic knowledge of Oracle networking and database on Linux and already have the following installed. (and also this is intended for practice purpose only)
- Oracle Database 11.2 on RHEL or OEL
- SQL Server 2008 or later.
- Optional. You can Install Oracle Database Gateway for ODBC in a separate oracle home. In this case I will be using Oracle Database home.
We will need a ODBC driver in order to configure Oracle Gateway for ODBC and fortunately there one from Microsoft. The following steps will guide you through installing and configuring Oracle Gateway for ODBC for SQL Server.
1 – Install ODBC Driver Manager.
2 – Download and Install ODBC Driver.
3 – Configure odbc.ini.
4 – Create init<instance name>.ora for heterogeneous services.
5. Register with the Listener.
6. configure tnsnames.ora.
7. Create database link in Oracle.
Step 1 – Install ODBC Driver Manager, the steps are documented on this link.
Step 2 – Download and Install ODBC Driver.
Follow this article to download and install the Microsoft ODBC Driver 11 for SQL Server. once the drive is installed you can verify it with sqlcmd command. sqlcmd gets installed along with Microsoft ODBC Driver.
sqlcmd -SServerName -Puserid -Upassword
serverName is the name of the windows server where sql server is installed.
userid and password are SQL Server credentials you want to connect to.
Step 3 – Configure odbc.ini
Add following to /etc/odbc.ini file
[sql_connection]
Driver=ODBC Driver 11 for SQL Server
Server=sqlserver_name,1433
Database=master
[sql_connection] is just an alias for this ODBC entry. You can enter a name which represents the SQL Server you intent to connect to and has to be within the square brakets.
-Database is the default database to open when login in to SQL Server.
Step 4 – Create init<instance name>.ora for heterogeneous services
Create a initialization file, think it like a oracle instance which we will later register with the listener. This initialization parameter file should be created under ORACLE_HOME/hs/admin folder. Give a name which is easy to associate with the SQL Server you are connecting to.
$ORACLE_HOME is wither your oralce home or oracle database gateway home in case you installed database gateway into another home.vi $ORACLE_HOME/hs/admin/initDG4SQL.ora
#————————————————————
# HS init parameters needed for the Database Gateway for ODBC
#————————————————————
HS_FDS_CONNECT_INFO = lfdevdb1
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
#————————————
# ODBC specific environment variables
#————————————
set ODBCINI=/etc/odbc.ini
Step 5. Register with the Listener: register the the newly created initialization (initDG4SQL.ora) file with oracle listener. Go to ORACLE_HOME/network/admin and edit the listener.ora file and add the following.
(SID_DESC =
(ORACLE_HOME = /oracle/app/oracle/product/11.2/gwhome1)
(SID_NAME=DG4SQL)
(ENV=”LD_LIBRARY_PATH=/usr/lib64:/oracle/app/oracle/product/11.2/gwhome1/lib”)
(PROGRAM=dg4odbc)
)
Step 6. configure tnsnames.ora: Configure the tnsnames.ora. Note that this should be on the database home where the db link to sql server will be created. Go to ORACLE_HOME/network/admin and edit the tnsnames.ora file and add the following.
DG4SQL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = OraDbHost)(PORT = 1521))
(CONNECT_DATA =
(SID = DG4SQL)
)
(HS=OK)
)
Step 7. Create db link: And finally we are ready to create a db link and acess sql server. Login to sqlplus and create the database link to connect to sql server.
Replace sql server user name and password with targets’s SQL Server user id and password.
CREATE DATABASE LINK SQLLINK
CONNECT TO <sql server user name>
IDENTIFIED BY <sql sevrer password>
USING ‘DG4SQL’
SELECT * FROM “sysobjects”@SQLLINK
Note that, if the SQL Server table is is either mixed or small case then you will have to put the table name within double quotes else you don’t need the quotes the SQL Server table name is in upper case.
