FreeTDS and ODBC on Ubuntu

FreeTDS is a collection of libraries for Unix and Linux that allows programs to talk to Microsoft SQL Server and Sybase databases. The following installation was on an Ubuntu 12.04 server.

Installing the necessary packages

# drivers for both FreeTDS and ODBC in a single package
$ sudo apt-get install tdsodbc

Configurations

FreeTDS

Edit /etc/freetds/freetds.conf to specify the server to connect to.

[my_awesome_server]
    host        = 192.168.0.33
    port        = 1433
    tds version = 8.0
ODBC

Edit /etc/odbcinst.ini to specify the location of the FreeTDS drivers.

...

[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log

[FreeTDS]
Description = Free TDS driver
# Driver on Ubuntu 32 bit
Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/i386-linux-gnu/odbc/libtdsS.so

# Driver on Ubuntu 64 bit
# Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
# Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

# Driver on Ubuntu 8.04
# Driver = /usr/lib/odbc/libtdsodbc.so
# Setup = /usr/lib/odbc/libtdsS.so

...

Edit /etc/odbc.ini to specify a DB to use.

# set a DSN
[MyAwesomeDBConnection] # you can now refer to this connection 
                        # by this name when connecting via odbc.

# Some info about the connection
Description = Connection to AwesomeDB

# name of the odbc driver to use as specified in /etc/odbcinst.ini
Driver      = FreeTDS

# Database name to connect to
Database    = AwesomeDB

# Server name as specified in /etc/freetds/freetds.conf
ServerName  = my_awesome_server
TDS_Version = 8.0

Diagnostic tools

tsql

Not to be mistaken with T-SQL (Transac SQL). tsql is a light weight diagnostic tool to test FreeTDS connections and queries. It might possibly be already installed with FreeTDS.

$ sudo apt-get install freetds-bin

# using the server name alias created in /etc/freetds/freetds.conf
$ tsql -S my_awesome_server -U myuserid -P sekret21 -D AwesomeDB
    locale is "en_CA.UTF-8"
    locale charset is "UTF-8"
    using default charset "UTF-8"
    Default database being set to AwesomeDB
    > 1

# from scratch
$ tsql -H 192.168.0.33 -p 1433 -U myuserid -P sekret21
locale is "en_CA.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
> 1
isql
$ sudo apt-get install unixodbc

# quick connection howto
$ isql

# connection syntax
$ isql dsn username password options

# useful options
-mx     # limit column display width to x

from within isql

> help help                         # list all help options
> help                              # list tables
> help table_name                   # list columns in table
> help catalog schema table type    # any argument may be set to "" or null

    e.g.

        help % "" "" ""             # list of catalogs
        help "" % "" ""             # list of schemas
        help null null b% null      # all tables beginning with b
        help null null null VIEW    # list of views
pyodbc

When connecting with pyodbc (Python), preferably use a dsn already preset in /etc/odbc.ini.

conn =  pyodbc.connect(
        'DSN=MyAwesomeDBConnection;' # as specified in /etc/odbc.ini
        'UID=myuserid;PWD=sekret21;')

Make sure to set the client's charset to use for that connection in /etc/freetds/freetds.conf.

# ... freetds.conf ...

[my_awesome_server]
    host        = 192.168.0.33
    port        = 1433
    tds version = 8.0
    client charset = UTF-8