This article explains how to connect to Microsoft SQL Server using unixODBC + FreeTDS. It includes installation and configuration instructions for Ubuntu and CentOS.

Requirements [top]

The following pieces are required to connect to MSSQL from Linux:

FreeTDS
UnixOBDC
An Iguana instance running on a Linux Server
An MSSQL Server

Note: FreeTDS utilizes unixODBCs driver manager, among other things.

Background [top]

Setup is actually very simple:

Installing unixODBC will install isql, odbcinst, and libodbc.so
Installing freeTDS will install freeTDS shared libraries and tsql

Note: .freetds.conf is not required if you are using only ODBC, .freetds.conf is only required if using FreeTDSs versions of DB/CT-library. This means you can configure using only odbc.ini, optionally using odbcinst.ini.

Concepts [top]

What is ODBC?

ODBC is a standard. It defines APIs for accessing database management systems.
DMBS independence is achieved by using ODBC drivers as a translation layer between the application and the database.
Database drivers, like device drivers, are software which encapsulate logic and provide standard APIs, allowing higher-level layers to access lower-level functions in a generalized manner.
A driver allows ODBC compliant applications to use a datasource. A datasource name, or DSN, is how identifiers in code are mapped to actual parameters required to make a real connection. A DSN identifies a particular connection.
ODBC also provides a Driver Manager. The driver manager knows about configured drivers and DSNs. The driver manager enables run-time lookup of DSNs. This is very useful. Some parameters can even be supplied at run-time, e.g., passwords.

What is FreeTDS?

TDS is a protocol. It stands for Tabular Data Stream. Protocols allow two computers to communicate and describe how this should happen at the bit level. It allows data to be sent across networks as a stream of bytes.
The DB and CT libraries provided by Sybase and Microsoft provide proprietary, application level libraries to transfer data between the database server and the client. These libraries can translate the TDS protocol. There is also an ODBC library that is able to perform this task.
FreeTDS an open source implementation equivalent of these libraries. We are only interested in ODBC.

Install [top]

CentOS

Run the following commands using a terminal window, or the CLI:
1. Get the latest package info:
```
yum check-update
```
2. Install libodbc.so, libtdsS.so, isql, and isqlinst:
```
sudo yum install unixODBC
```
3. Install libtsdsodbc.so and tsql:
```
sudo yum install freetds
```
Create a file called odbc.ini

Ubuntu

Run the following commands using a terminal window, or the CLI:
1. Get the latest package info:
```
sudo apt-get update
```
2. Install libodbc.so, libtdsS.so, isql, and isqlinst:
```
sudo apt-get install unixodbc
```
3. Install libtsdsodbc.so and tsql:
```
sudo apt-get install tdsodbc
```
Create a file called odbc.ini

Configure [top]

All that is required is to configure the odbc.ini file.
Note: freetds.conf is not required.

The odbc.ini configuration will be something like this:
Note: The <comments> in angle brackets should not be included.

CentOS

Bare minimum

Driver = /usr/lib64/libtdsodbc.so
Setup = /usr/lib64/libtdsS.so
Server = your_server_name
Port = 1433

With user etc

Driver = /usr/lib64/libtdsodbc.so
Setup = /usr/lib64/libtdsS.so
Server = your_server_name <network name or IP address for the server>
Port = 1433 <1433 = default - a different port can be used>
Database = dbname <if omitted the default database will be used>
Username = dbusername <if omitted then integrated security is used - needs kerberos>
Password = dbpassword

Ubuntu

Bare minimum

Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
Server = your_server_name
Port = 1433

With user etc

Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
Server = your_server_name <network name or IP address for the server>
Port = 1433 <1433 = default - a different port can be used>
Database = dbname <if omitted the default database will be used>
Username = dbusername <if omitted then integrated security is used - needs kerberos>
Password = dbpassword

Note: Often when a package installs a shared library, it will append a version number, so double check the library names and symlink them to what is shown above, i.e.,

sudo ln -s /usr/lib/x86_64-linux-gnu/libodbc.so.1 /usr/lib/x86_64-linux-gnu/libodbc.so

Troubleshooting

Incorrect port number: SQL Server defaults to port 1433, but a different port can be used. Speak to your database administrator (DBA) or network administrator.
Using integrated security: Kerberos is needed on Linux, this SQL Server page Using Integrated Authentication has some information.

Old Ubuntu notes [top]

Iguana has been verified to work with SQL Server from Linux, via unixODBC + FreeTDS driver.

This example demonstrates setting it up using Ubuntu 10.04 x64 (where required FreeTDS packages are available from the package system).

Adjust accordingly if you have a different type of system.

First, we need to install the packages (or otherwise obtain or compile them from source if your package system doesn’t provide them):

apt-get install freetds-bin tdsodbc

Then, create a freetds configuration (this can be local to the user, with the configuration file ~/.freetds.conf , or global)

Using local configuration variant (this is also cleaner from system administration perspective, as the entire configuration can be contained in a non-privileged user account).

Create a file ~/.freetds.conf with the following contents (adjust your server host), and test connectivity with tsql utility:

For access from Iguana, we’ll need it to be able to load libodbc.so

Iguana looks for libodbc.so – default installs of unixODBC don’t always provide this exact name (often only as libodbc.so.1).

If you have libodbc.so.* but not libodbc.so – create a symlink – e.g. libodbc.so -> libodbc.so.1

Next, configure the unixODBC Driver – it can be done from a template using odbcinst, or manually.

Create a data source configuration – odbcinst -j can be used to display where unixODBC looks for configuration files.

Again, as you can see, this can be done locally under the current user, or globally in /etc.

Create a configuration file, adjusting to your own Servername and Database.

Verify connectivity with isql.

Now, assuming everything is configured correctly, you should be able to connect to the database from Iguana.

“Export Tables” screen on a legacy Database channel can be used as a quick test to verify the ability to work with a given database (WITHOUT executing them).

If you see SQL statements in Preview, that means Iguana is able to connect to the database.

(Feel free to skip to next step and try out connecting to the database from Translator directly)