Connect MSSQL using ODBC in Linux (CentOS)


For connecting MSSQL using ODBC in Linux you can use unixODBC and freeTDS.  unixODBC is a tool for using ODBC in Non Windows platform. MSSQL uses Tabular Data Stream (TDS) as a communication protocol which is same like in Sybase. freeTDS is an implementation of TDS protocol.

Installing unixODBC
      1.      Install gcc
#yum install gcc

      2.      Download unixODBC and untar it
#mkdir /root/downloads
#cd /root/downloads
#wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.0.tar.gz
#tar –xzf unixODBC-2.3.0.tar.gz
#cd unixODBC-2.3.0

      3.      Install unixODBC
#./configure
#make
#make install

Installing freeTDS
     1.      Set environmental variable at /etc/profile. Add following lines at end
#nano /etc/profile


# TDS
SYBASE=/usr/local
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$SYBASE/lib
export SYBASE LD_LIBRARY_PATH
/etc/profile

     2.      Download freeTDS and untar it
#cd /root/downloads
#wget ftp://ftp.astron.com/pub/freetds/stable/freetds-stable.tgz
#tar –xzf freetds-0.82.tar.gz
#cd freetds-0.82
     3.      Install freeTDS
# ./configure --with-tdsver=8.0 --with-unixodbc=/usr/local
#make
#make install

     4.      Now all your library will be inside /usr/local/lib and configuration files inside /usr/local/etc

     5.      Make template to register ODBC driver with unixODBC
#nano /usr/local/etc/tds.driver.template


[FreeTDS]
Description     = v0.63 with protocol v8.0
Driver          = /usr/local/lib/libtdsodbc.so
tds.driver.template
     6.      Now install the driver entry using above template
# odbcinst -i -d -f /usr/local/etc/tds.driver.template

     7.      Make template to create ODBC data source name
#nano /usr/local/etc/tds.datasource.template


[MSSQLTest]
Driver  = FreeTDS
Description     = Sample Database
Trace   = No
Server      = 192.168.0.1
Port      = 1433
Database        = TestDB
tds.datasource.template
     8.      Now create odbc data source using above template
# odbcinst -i -s -f /usr/local/etc/tds.datasource.template

     9.      Finish. You can test it by giving command
                       # isql -v MSSQLTest username password

Comments

Reply to this post

Post a Comment