Saturday, September 8, 2012

Installing MS SQL Server driver on Ubuntu Linux

Although making connection to sql server can be done by using unixODBC and  FreeTDS, it is not working with some collations on sql server 2008. Microsoft released an ODBC driver for Linux recently which works on 64-bit systems only.Installing the driver is somehow tricky. I begin by install the build-essential  which is required for compiling from source. checkinstall can be install if you want to add following application to package manager.

 sudo apt-get install build-essential

 Download unixODBC-2.3.0 and Microsoft ODBC dirver and extract.


sudo wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.0.tar.gz
tar xvf unixODBC-2.3.0.tar.gz 

wget http://download.microsoft.com/download/6/A/B/6AB27E13-46AE-4CE9-AFFD-406367CADC1D/Linux6/sqlncli-11.0.1790.0.tar.gz
tar xvf sqlncli-11.0.1790.0.tar.gz 

 Install the unixodbc using following commands:

./configure --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE
sudo make install

libssl is required by driver

sudo apt-get install libssl-dev libssl1.0.0 libssl1.0.0-dbg

Make the following symbolic links(first make sure libssl.so.1.0.0,libcrypto.so.1.0.0 exist in the path

sudo ln -s /lib/x86_64-linux-gnu/libssl.so.1.0.0 /usr/lib/libssl.so.10
sudo ln -s /lib/x86_64-linux-gnu/libcrypto.so.1.0.0 /usr/lib/libcrypto.so.10

Use following command to find out if there are other missing dependency.

ldd sqlncli-11.0.1790.0/lib64/libsqlncli-11.0.so.1790.0

make sure "/usr/local/lib" is in dynamic linker search path if it is not use the following command to add it temporarily.


sudo ldconfig /usr/local/lib

Almost done:

sudo bash ./install.sh install --force

Try to see if it is working:

sqlcmd -S localhost

If you are using pyodbc, the connection string should be like the following example.


touch test.py
vim test.py

#---test.py---

import pyodbc
conn=pyodbc.connect('DRIVER={SQL Server Native Client 11.0};DATABASE=dbnamw;Server=host;UID=username;PWD=password')
curs = conn.cursor()
curs.execute('select name from sysobjects')
print (curs.fetchone())