MinhTech.com

Yet another technology tutorial blog.

Oracle 11g Database Link to Microsoft SQL Server

By • Feb 9th, 2015 • Category: Oracle

Here is how to create a database link from Oracle Database 11g to Microsoft SQL Server. First, configure the Microsoft ODBC Driver 11 for SQL Server. Next, configure the Database Gateway for ODBC (DG4ODBC). Finally, create the database link.

Download Microsoft ODBC Driver 11 for SQL Server

> tar -zxvf msodbcsql-11.0.2270.0.tar.gz
> cd msodbcsql-11.0.2270.0

Download the Microsoft ODBC Driver 11 for SQL Server for RedHat Linux directly from Microsoft here. Extract the files to a directory and change to that directory.

Install unixODBC 2.3.0

> ./build_dm.sh
> cd /tmp/unixODBC.28674.20460.17226/unixODBC-2.3.0; make install

Note that the Microsoft ODBC Driver 11 for SQL Server is compatible with only unixODBC 2.3.0, not 2.2.x or 2.3.1 or later. Fortunately, the files from Microsoft include a script to automatically download, compile, and install the correct version of unixODBC. Run the build_dm.sh script, change to the temporary directory specified by the script, and install unixODBC. Note that you may need to remove unixODBC first with yum remove unixodbc.

Install Microsoft ODBC Driver 11 for SQL Server

> cd $HOME/msodbcsql-11.0.2270.0
> ./install.sh verify
Microsoft ODBC Driver 11 for SQL Server Installation Script
Copyright Microsoft Corp.

Starting install for Microsoft ODBC Driver 11 for SQL Server

Checking for 64 bit Linux compatible OS ………………………………. OK
Checking required libs are installed …………………………………. OK
unixODBC utilities (odbc_config and odbcinst) installed ………………… OK
unixODBC Driver Manager version 2.3.0 installed ……………………….. OK
unixODBC Driver Manager configuration correct ………………………… OK*
Microsoft ODBC Driver 11 for SQL Server already installed ………… NOT FOUND

Install log created at /tmp/msodbcsql.11449.16545.5960/install.log.

One or more steps may have an *. See README for more information regarding these steps.

> ./install.sh install

Change back to the directory containing the driver files. Run install.sh verify to confirm that all prerequisites are installed and configured properly. Then run install.sh install to compile and install the Microsoft ODBC Driver 11 for SQL Server. The driver directory will be /opt/microsoft/msodbcsql/11.0.2270.0 .

Check unixODBC & Microsoft ODBC Driver 11 for SQL Server Installations

> odbc_config --version
2.3.0

> odbcinst -j
unixODBC 2.3.0
DRIVERS…………: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size…….: 8
SQLLEN Size……..: 8
SQLSETPOSIROW Size.: 8

> odbcinst -q -d
[ODBC Driver 11 for SQL Server]

> odbcinst -q -d -n "ODBC Driver 11 for SQL Server"
[ODBC Driver 11 for SQL Server]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Threading=1
UsageCount=1

> sqlcmd -S microsoft_sql_server_hostname -U username -P password
> select count(*) from information_schema.columns;
> GO
> quit

Setup ODBC DSN for a Microsoft SQL Server Database

> vi /etc/odbc.ini
[ODBC Data Sources]
minh=MS SQL Server

[minh]
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Description=Microsoft ODBC Driver 11 for SQL Server
Server=microsoft_sql_server_hostname,1433
Database=database
User=username
Password=password
QuotedId=YES
AnsiNPW=YES
Threading=1
UsageCount=1
AutoTranslate=No

> isql -v minh username password
SQL> select count(*) from information_schema.columns;
SQL> quit

Create a Database Source Name (DSN) record and test it using isql.

Create a DG4ODBC Agent Initialization File

> vi $ORACLE_HOME/hs/admin/initminh.ora
HS_FDS_CONNECT_INFO = minh
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_TRACE_LEVEL = 0
HS_LANGUAGE = AMERICAN_AMERICA.WE8MSWIN1252
HS_KEEP_REMOTE_COLUMN_SIZE = LOCAL
HS_NLS_LENGTH_SEMANTICS = CHAR
HS_NLS_NCHAR = UCS2

set ODBCINI=/etc/odbc.ini

Edit listener.ora

> vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
   (SID_LIST =
      (SID_DESC =
         (SID_NAME = minh)
         (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)
      )
   )

> lsnrctl reload
> lsnrctl status
Service "minh" has 1 instance(s).
 Instance "minh", status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

Edit the listener.ora file and add a new listener. Be careful and use the correct directory paths depending on your $ORACLE_HOME location. Reload the listener and check its status.

Edit tnsnames.ora

> vi $ORACLE_HOME/network/admin/tnsnames.ora
minh =
   (DESCRIPTION =
      (ADDRESS =
         (PROTOCOL = tcp)
         (HOST = hostname)
         (PORT = 1521))
      (CONNECT_DATA =
         (SID = minh ))
      (HS = OK)
   )

> tnsping minh
TNS Ping Utility for Linux: Version 11.2.0.3.0 – Production on 09-FEB-2015 09:36:38

Copyright (c) 1997, 2011, Oracle. All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = hostname) (PORT = 1521)) (CONNECT_DATA = (SID = minh)) (HS = OK))
OK (0 msec)

Edit the tnsnames.ora file and add a new network service. Then use tnsping to test the listener.

Create the Database Link

> sqlplus
SQL> create public database link minh_link connect to "username" identified by "password" using ‘minh‘;
SQL> select count(*) from information_schema.columns@minh_link;

is a technology junkie.
Email this author | All posts by

2 Responses »

  1. Dear Minh,

    i create dblink along your instruction , but belong error ,pls help me

    *********************************************************************
    Link : “PSP”
    Error : ORA-28545: error diagnosed by Net8 when connecting to an agent
    Unable to retrieve text of NETWORK/NCR message 12564
    ORA-02063: preceding 2 lines from PSP
    *********************************************************************

    Linux uccsdb2dbi1 2.6.39-200.24.1.el6uek.x86_64 #1 SMP Sat Jun 23 02:39:07 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux

    Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production

    [oracle@uccsdb2dbi1 admin]$ more initpsp.ora
    HS_FDS_CONNECT_INFO = psp
    HS_FDS_TRACE_LEVEL = off
    HS_FDS_SUPPORT_STATISTICS=FALSE

    psp =
    (DESCRIPTION =
    (ADDRESS =
    (PROTOCOL = tcp)
    (HOST = 192.168.0.159)
    (PORT = 1521))
    (CONNECT_DATA =
    (SID = psp ))
    (HS = OK)
    )

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = psp)
    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    (PROGRAM = /u01/app/oracle/product/11.2.0/db_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/db_1/lib:/lib64:/lib)
    )
    )

    ~

  2. Hi,
    I am having the same problem. I did everything above and can test connect driver. However, the database link has the same error. Where should I check:

    SID_LIST_REGREGY =
    (SID_LIST =
    (SID_DESC =
    (ORACLE_HOME= /db04/REGREGY/11.2.0.4)
    (SID_NAME = REGREGY)
    )
    (SID_DESC =
    (SID_NAME=SQLSERV
    (ORACLE_HOME=/db04/REGREGY/11.2.0.4)
    (ENVS=LD_LIBRARY_PATH=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.s
    o.2270.0:/usr/lib64:/db04/REGREGY/11.2.0.4/lib)
    (PROGRAM=dg4odbc)
    )
    )

    tnsnames.ora

    SQLSERV=
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=houegydb2)(PORT=1527))
    (CONNECT_DATA=(SID=sqlserv))
    (HS=OK)
    )

    ERROR:
    SQL> select count(*) from information_schema.columns@sqlserv_link;
    select count(*) from information_schema.columns@sqlserv_link
    *
    ERROR at line 1:
    ORA-28545: error diagnosed by Net8 when connecting to an agent
    Unable to retrieve text of NETWORK/NCR message 65535
    ORA-02063: preceding 2 lines from SQLSERV_LINK

Leave a Reply