Connecting to MSSQL Database from Linux/Mac

Detailed tutorial about how to configure a Linux or OSX machine to connect to M SSQL

06.01.2015

Before you can connect to an MS SQL database from a Linux or OS X machine, there is some additional configuration that is required. This guide is intended to walk users through the configuration process. There are slight differences in configuration between Linux and OS X, so follow the appropriate section.

Configuration for Linux

Required Packages

  • FreeTDS - A free implementation of the Tabular Data Stream protocol that is used by Microsoft for their database products.
Manjaro LinuxUbuntu
$ sudo pacman -S freetds$ sudo apt-get install freetds-dev freetds-bin tdsodbc
  • unixODBC - An open specification for providing application developers with a predictable API with which to access Data Sources.
Manjaro LinuxUbuntu
$ sudo pacman -S unixodbc$ sudo apt-get install unixodbc unixodbc-dev
  • myODBC - ODBC driver/connector for mariadb.
Manjaro LinuxUbuntu
$ sudo pacman -S myodbc$ sudo apt-get install libmyodbc

Configuring FreeTDS

Edit the freetds.conf file:

$ sudo vim /etc/freetds/freetds.conf

Add an entry for a new server as follows:

[SERVERNAME]
host = HOSTNAME or xxx.xxx.xxx.xxx
port = 1433
tds version = 8.0

Where SERVERNAME is the name of the database server you are going to connect to, hostname is the FQDN or IP address of the server. Here is an example configuration:

[iutahdbs]
host = iutahdbs.uwrl.usu.edu
port = 1433
tds version = 8.0

That’s it. Now test the FreeTDS connection by making a query:

$ tsql -S SERVERNAME -U USERNAME

If you have provided a valid username, you will be prompted for a password. If everything checks out, you will get the prompt, “1>”.

You should now be able to query a database. Sometimes the database name can be a little bit tricky. Make sure you’re using the right format (DATABASE.dbo.TABLENAME):

1> select * from iUTAH_Logan_OD.dbo.Variables
2> go

Configuring ODBC

Edit the ODBC configuration file called odbc.ini.

$ sudo vim /etc/odbc.ini

Add the following, replacing the necessary parts:

[ODBC Data Sources]
ODBCNAME = Microsoft SQL Server

[ODBC_NAME_REFERENCE]
TDS_Version = 8.0
Driver = FreeTDS
Description = Add your description here.
Trace = No
Servername = SERVERNAME
Database = DATABASE_SCHEMA_NAME

[Default]
Driver = /usr/lib/libtdsodbc.so
  • ODBC_NAME_REFERENCE - This can be anything you want to call it. You’ll refer to this in your Django configuration (dsn).

  • SERVERNAME - This needs to be the same as SERVERNAME from freetds.conf

  • DATABASE_SCHEMA_NAME - This is the default database schema that will be used for queries.

Make sure that the driver points to a valid file. They are placed in different locations on some machines. On the Ubuntu server, I found it here: /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

If everything went well, you should be able to query the database like this:

$ isql -v ODBC_NAME_REFERENCE USERNAME PASSWORD

*Note that if your password contains any special characters, such as an exclamation mark, you must put the password inside single quotations.

Test a query like this:

select * from dbo.Variables

As far as connecting to a database from Linux, that’s all there is to it.

Configuration for Mac OS X

The easiest way to obtain the required packages is with homebrew, a package manager for OS X. To install homebrew, paste the following into the terminal:

ruby -e `$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)`

Required Packages

  • FreeTDS - A free implementation of the Tabular Data Stream protocol that is used by Microsoft for their database products.

  • unixODBC - An open specification for providing application developers with a predictable API with which to access Data Sources.

$ brew install freetds --with-unixodbc

FreeTDS Configuration

Edit the freetds.conf file. It will most likely be located here: /usr/local/etc/freetds.conf If for some reason the file is in a different location, you can find it like this:

$ sudo find / -name freetds.conf

Append the following to the end of the file:

[SERVERNAME]
host = HOSTNAME or IP Address (eg. iutahqc.uwrl.usu.edu)
port = 1433
tds version = 7.1

Where SERVERNAME is the name of the database server you are going to connect to, hostname is the FQDN or IP address of the server. Here is an example configuration:

[iutahqc]
host = iutahqc.uwrl.usu.edu
port = 1433
tds version = 7.1

Now test the FreeTDS connection by making a query:

$ tsql -S SERVERNAME -U USERNAME

If you have provided a valid username, you will be prompted for a password.

You should now be able to query a database. Sometimes the database name can be a little bit tricky. Make sure you’re using the right format (DATABASE.dbo.TABLENAME):

1> select * from iUTAH_Logan_OD.dbo.Variables
2> go

unixODBC Configuration

First, edit the ODBC configuration file. It’s probably located at /usr/local/etc/odbc.ini

You can also search for it with the following command:

$ sudo find / -name odbc.ini

Modify the file to contain the following, making modifications as needed:

[ODBC Data Sources]
ODBCNAME = Microsoft SQL Server

[ODBC_NAME_REFERENCE]
TDS_Version = 7.1
Driver = FreeTDS
Description = Add your description here.
Trace = No
Servername = SERVERNAME
Database = DATABASE_SCHEMA_NAME

[Default]
Driver = /usr/lib/libtdsodbc.so
  • ODBC_NAME_REFERENCE - This can be anything you want to call it.

  • SERVERNAME - This needs to be the same as SERVERNAME from freetds.conf

  • DATABASE_SCHEMA_NAME - This is the default database schema that will be used for queries.

Make sure that the driver points to a valid file. Here is an example configuration that matches the FreeTDS config that we have just setup:

[ODBC Data Sources]
ODBCNAME = Microsoft SQL Server

[iutahqc]
TDS_Version = 7.1 
Driver = FreeTDS
Description = Add your description here.
Trace = No
Servername = iutahqc
Database = iUTAH_Logan_OD

[Default]
Driver = /usr/local/lib/libtdsodbc.so

If everything went well, you should be able to query the database like this:

$ isql -v ODBC_NAME_REFERENCE USERNAME PASSWORD

*Note that if your password contains any special characters, such as an exclamation mark, you must put the password inside single quotations.

Test a query like this:

select * from dbo.Variables

At this point, you should be able to connect to the database via command line. However, in order to access the database via python, we must configure pyodbc.

First, create a new file called FreeTDS_driver and add the following:

[FreeTDS]
Description = TDS Driver (MS SQL)
Driver = /usr/local/lib/libtdsodbc.so

Save the file, and run this command to register the driver:

$ sudo odbcinst -d -i -f FreeTDS_driver

pyodbc Configuration

Download the source code here. Unzip the directory where ever you want

Edit setup.py within the pyodbc directory. Look for the line that says “OS/X now ships with iODBC.” Right underneath that, remove the letter i from the statement:

settings["libraries"].append("iodbc")

so that it reads:

settings["libraries"].append("odbc")

Now that the setup.py file is configured correctly, pyodbc is ready to be installed. From your virtual environment, do a pip install of pyodbc and use the modified package.

$ pip install -e PATH_TO_PYODBC

If everything was successful, your machine is now configured to connect to an MS SQL database.