RDBMS-- Oracle to SQLSERVER dblink

When we talk about creating a Dblink from an Oracle Database (Oracle 11g or above) To MSSQL server, we actually talk about dg4odbc.
Well, creating a database link from an Oracle Database to a Microsoft Sql Server Database is based on the binary named DG4ODBC.(Database Gateway For ODBC) DG4ODBC is a new program that replaces hsodbc, which was used in earlier Oracle Database releases.
Actually, DG4ODBC is not the only thing required for this operation. There are linux drivers(I m talking about Oracle On Linux env bytheway, as %99 of the Oracle Environments that I have dealed with were  running on Linux), driver manager, oracle clients and driver managers that makes an Oracle to Sql Server dblink available to function.
The full path of reaching an Sql Server databae from an Oracle Instance is as follows:
Suppose we are using sqlplus to query the sql server;

"SQL*Plus -> Oracle Client -> DG4ODBC instance -> unixODBC -> ODBC driver -> Database"

So what we do here is ; we execute our query in a query tool such as sqlplus.
This tool used oracle client and when it sees the dblink, it executes the DG4ODBC program via the Oracle Database listener.
DG4ODBC passes the work to te Linux ODBC Driver Manager
Linux ODBC Driveer manager triggers the MS SQL Server ODBC driver to be taken to the stage and the MS Sql ODBC Driver of Linux connects to the Sql Server database and our query executed from Oracle Database gets data from Sql Server.

So as you may imagine , all of these components except the sqlplus and oracle client(there are already configured and nothing needs to be done for them) needs a proper configuration for this.

What we do to configure?

1) We first install a Microsoft Sql Server Driver to Linux OS where our Oracle database is running.
Then we add sql server related configuration to the odbc.ini as shown in the example below;
Note that: we specify the driver that comes with the ms sql server driver installation with the "Driver=" setting.

[root@ermanserver]# cat /etc/odbc.ini
[SQLSERVER_ODBC_DSN]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Server=10.54.54.12
Database=ERMANSQL
User=ermanuser
Password=ermanpass
QuotedId=YES
AnsiNPW=YES
Threading=1
UsageCount=1

Then we add the sql server driver related information to the odbcinst.ini as shown in the example below;
Note that: the installation driver may do this automatically, but still it is good to be checked

[root@ermanserver]# cat /etc/odbcinst.ini
[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/lib/psqlodbc.so
Setup=/usr/lib/libodbcpsqlS.so
Driver64=/usr/lib64/psqlodbc.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1

[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/libmyodbc3_r.so
Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc3_r.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1

[PostgreSQL64]
Description=ODBC for PostgreSQL (64 bit)
Driver=/usr/lib/psqlodbcw.so
Setup=/usr/lib/libodbcpsqlS64.so
Driver64=/usr/lib64/psqlodbcw.so
Setup64=/usr/lib64/libodbcpsqlS64.so
FileUsage=1

[MySQL64]
Description=ODBC for MySQL (64 bit)
Driver=/usr/lib/libmyodbc5.so
Setup=/usr/lib/libodbcmyS64.so
Driver64=/usr/lib64/libmyodbc5.so
Setup64=/usr/lib64/libodbcmyS64.so
FileUsage=1

[ODBC Driver 11 for SQL Server]  ----> "here is our driver information"
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Threading=1
UsageCount=1

Next, we check the sqlserver connection and see if it can be done using our newly installed driver and its configuration.
isql utility can be used here.

[root@ermanserver ]# . /root/unixODBC-2.3.0/exe/isql -v SQLSERVER_ODBC_DSN ermanuser ermanpassword
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

After seeing the SQL> prompt, we can say that our work with Linux configuration files are finished.

We jump to Oracle Database home and create an .ora file in $ORACLE_HOME/hs/admin directory.
Note that: the file name should be in the following format init<DSN_NAME_WE_HAVE_CONFIGURED_IN_ODBCINSTFILE>.ora

[oracle@ermanserver ~]$ cd $ORACLE_HOME/hs/admin

[oracle@ermanserver admin]$ ls
initSQLSERVER_ODBC_DSN.ora

The init file is important as we decleare lots of heterogenous parameters in this file.
The most important setting is the HS_FDS_SHAREABLE_NAME setting, as we set the Linux's ODBC driver manager related System object file(/usr/lib64/libodbc.so) here. Driver manager will choose our driver when it is needed.

[oracle@ermanserver admin]$ cat initSQLSERVER_ODBC_DSN.ora
HS_FDS_CONNECT_INFO=SQLSERVER_ODBC_DSN
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_FDS_TRACE_LEVEL=255
HS_KEEP_REMOTE_COLUMN_SIZE = LOCAL
HS_NLS_LENGTH_SEMANTICS = CHAR
HS_LANGUAGE=AMERICAN_AMERICA.WEISO8859P2
HS_NLS_NCHAR = UCS2
set ODBCINI=/etc/odbc.ini

After configuring the init file, we configure our listener.ora and tnsnames.ora files
Note that : we have used the IFILE here because this example was done on an autoconfig enabled EBS environment and that's why we didnt want our newly added tns entry to be overwritten by a future autoconfig run.
Also note that: we set the hostname and port of our local Oracle Database environment, as we want the clients to reach the listener and then the listener to execute the DG4ODBC program to connect them internally to sqlserver.

[oracle@ermanserver admin]$ cat /u01/app/oracle/product/11.20/dbhome_1/network/admin/PROD_ermanserver/PROD_ermanserver.ora

SQLSERVER =
    (DESCRIPTION =
       (ADDRESS =
          (PROTOCOL = tcp)
          (HOST = ermanserver)
          (PORT = 1523))
       (CONNECT_DATA =
          (SID = SQLSERVER_ODBC_DSN ))
       (HS = OK)
    )

In listener.ora file, we also use the database host and port because of the same reason.
Note that: all the settings related to odbc are important here. (PROGRAM, ENVS=LD_LIBRARY_PATH etc..)
Bytheway, we have added our sqlserver related configuration directly to the listener.ora here, you can add it to the ifile of the listener :) to prevent it getting overwritten by autoconfig. :)

[oracle@ermanserver admin]$ cat listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ermanserver)(PORT = 1523))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = PROD)
    )
        (SID_DESC =
          (SID_NAME = SQLSERVER_ODBC_DSN)
          (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
          (PROGRAM = /u01/app/oracle/product/11.2.0/dbhome_1/bin/dg4odbc)
          (ENVS=LD_LIBRARY_PATH=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0:/usr/lib64:/u01/app/oracle/product/11.2.0/dbhome_1/lib)
       )

  )

STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF

LOG_DIRECTORY_LISTENER = /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
LOG_FILE_LISTENER = LISTENER
TRACE_DIRECTORY_LISTENER = /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
TRACE_FILE_LISTENER = LISTENER
ADMIN_RESTRICTIONS_LISTENER = OFF
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER = OFF

IFILE=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener_ifile.ora

That's all :) We reload our listener and start using our dblink by  creating a dblink with the following syntax.

CREATE DATABASE LINK MYSQLSERVER CONNECT TO ermanuser identified by ermanpass
USING 'SQLSERVER';

Example query select * from "dbo"."tablename"@SQLSERVER

Well... We have reached the end of this post. Note that: The configuration files used in this post in taken from a real system. So this is a working example . I hope you find it useful.