Connecting to SQL Server from RHEL or Centos

Install unixODBC

See http://msdn.microsoft.com/en-us/library/hh568449.aspx.

It should be noted that version 2.3.0 of unixODBC has some significant bugs in it, in particular concerning multiple connections to the same database which can cause segmentation faults. Hence best install the latest version, 2.3.2 (as of April 2015).

# remove any existing unixODBC drivers
sudo rm /usr/lib64/libodbc*

# download and unzip the unixODBC driver
curl -O 'ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.2.tar.gz'
tar -xz -f unixODBC-2.3.2.tar.gz
cd unixODBC-2.3.2

# install the unixODBC driver
# note, adding "--enable-stats=no" here is not specified by Microsoft
export CPPFLAGS="-DSIZEOF_LONG_INT=8"
./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/etc --enable-gui=no --enable-drivers=no --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --enable-stats=no 1> configure_std.log 2> configure_err.log
make 1> make_std.log 2> make_err.log
sudo make install 1> makeinstall_std.log 2> makeinstall_err.log

# the Microsoft driver expects unixODBC to be here /usr/lib64/libodbc.so.1, so add soft links to the '.so.2' files
cd /usr/lib64
sudo ln -s libodbccr.so.2   libodbccr.so.1
sudo ln -s libodbcinst.so.2 libodbcinst.so.1
sudo ln -s libodbc.so.2     libodbc.so.1

Check the unixODBC installation with the following:

ls -l /usr/lib64/libodbc*
odbc_config --version --longodbcversion --cflags --ulen --libs --odbcinstini --odbcini
odbcinst -j
isql --version

Install the Microsoft ODBC Driver for Linux

See http://msdn.microsoft.com/en-us/library/hh568454.aspx

First of all, go to https://www.microsoft.com/en-us/download/details.aspx?id=36437 and download the appropriate zip file to your Linux server. Then install it as follows:

cd /path/to/your/driver/file/directory
tar -xz -f msodbcsql-11.0.2270.0.tar.gz
cd msodbcsql-11.0.2270.0
sudo ./install.sh install --accept-license --force 1> install_std.log 2> install_err.log

Check the msodbc installation with the following:

ls -l /opt/microsoft/msodbcsql/lib64/
dltest /opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0 SQLGetInstalledDrivers
cat /etc/odbcinst.ini   # should contain a section called [ODBC Driver 11 for SQL Server]

Prepare a file for defining the DSN to your database with a temporary file something like this:

[MySSQLServerDatabase]
Driver      = ODBC Driver 11 for SQL Server
Description = My MS SQL Server
Trace       = No
Server      = mydbserver.mycompany.com

In that file, leave the 'Driver' line exactly as specified above, but modify the rest of the file as you prefer. Then run the following:

# register the SQL Server database DSN information in /etc/odbc.ini
sudo odbcinst -i -s -f /path/to/your/temporary/dsn/file -l

# check the DSN installation with:
cat /etc/odbc.ini   # should contain a section called [MySSQLServerDatabase]

Test the Connection

Try the connection to your database with Python code something like the following:

import pyodbc
cnxn = pyodbc.connect('DSN=MySSQLServerDatabase;UID=myuid;PWD=mypwd', autocommit=True)
cnxn.close()