Access to MS SQL Server Database: FreeTDS Installation

To access a MS SQL Server Database via unixODBC you need a driver. There are several drivers in the market. A free version is FreeTDS.

FreeTDS Installation

Install from rpm

  • For RedHat RHEL 7 freetds is available as rpm in the Epel Repository.
  • For RedHat RHEL 6 freetds is available as rpm in the Epel Repository, but only the outdated version 0.91

The following examples do not use these packages.

Download

Go to
http://www.freetds.org/
and download the stable release
ftp://ftp.freetds.org/pub/freetds/stable/freetds-patched.tar.gz
I got version freetds-0.95.79

Install

In this example the file freetds-patched.tar was downloaded to directory /opt

cd /opt
# unpack
tar xfz freetds-patched.tar.gz
cd freetds-0.95.79
# configure FreeTDS
; tdsver is the default version, if nothing is specified in odbc.ini
; if you have compiled unixODBC from source as shown in this blog, specify --with-unixodbc=/usr/local/
./configure --silent --with-tdsver=7.0 --with-unixodbc=/usr/local/
# compile
make
# install - needs root privileges
make install

For configuration and compilation you do not need root privileges, only for the last step make install you need root privileges.

First Test

The tsql utility is provided as part of FreeTDS expressly for troubleshooting.

/usr/local/bin/tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v0.95.79
             freetds.conf directory: /usr/local/etc
     MS db-lib source compatibility: no
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 7.0
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: no
                            OpenSSL: no
                             GnuTLS: no

For your first connection you need the following informations about your target MS SQL Server database:

  • Hostname (or ip address)
  • Port number (usually 1433)
  • Database name
  • Username
  • Password
# replace hostname, dbname, user and password with your values:
echo -e 'select @@version\nGO' \
| /usr/local/bin/tsql -H hostname -p 1433 -D dbname -U user -P password
locale is "en_US.UTF-8"
locale charset is "UTF-8"
Default database being set to test801
1> 2>
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
        Feb 10 2012 19:39:15
        Copyright (c) Microsoft Corporation
        Express Edition (64-bit) on Windows NT 6.2  (Build 9200: ) (Hypervisor)

(1 row affected)
1>

When you see the version of your SQL Server database, your connection is successfull.

Configure unixODBC

Next step is to integrate the Freetds Driver into unixODBC.
With FreeTDS we have two alternatives:

    1. ODBC-only configuration: all configuration data in the file odbc.ini
      This alternative will be used in the following examples.
    2. ODBC-combined configuration: configuration data in the file odbc.ini

and in freetds.conf
Details: FreeTDS User Guide, Chapter 4. Preparing ODBC
http://www.freetds.org/userguide/prepodbc.htm

Configure dsn in odbc.ini

The file odbc.ini in usually located /usr/local/etc/odbc.ini, /etc/odbc.ini, /etc/unixODBC/odbc.ini.
If you have compiled unixODBC from source as described in the previous article, you will use /usr/local/etc/odbc.ini.

For each connection a datasource name dsn is configured in odbc.ini

; testdsn is the datasource name (not case sensitive)
[testdsn]
; driver: unixODBC will use the specified driver
driver          = /usr/local/lib/libtdsodbc.so
; server: hostname (or ip address) of your MS SQL Server
server          = hostname
; port: port number on the specified host (usually 1433)
port            = 1433
; database: name of database
database        = dbname
; tds_version: 
;    7.0 for Microsoft SQL Server 7.0
;    7.1 for Microsoft SQL Server 2000
;    7.2 for Microsoft SQL Server 2005
;    7.3 for Microsoft SQL Server 2008
tds_version     = 7.3

Test the configured dsn

The configured dsn can be tested with isql:
/usr/local/bin/isql (if your compiled unixODBC from source) or /usr/bin/isql (unixODBC installed as package)

$ echo "select @@version; | /usr/local/bin/isql -v testdsn username password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                                                                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
        Feb 20 2014 20:04:26
        Copyright (c) Microsoft Corporation
        Express Edition (64-bit) on Windows NT 6.3  (Build 10586: ) (Hypervisor)
                                                                                                                  |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
SQL> quit

Next Step

If the isql Test works, your are ready to configure the Oracle Gateway to ODBC and the Database Link in your Oracle Database: Configure Oracle Gateway for ODBC with FreeTDS