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())



6 comments:

  1. Edgar Allen Poe wrote about this moment..

    Previously my hacks grew longer, but configure FreeTDS now no longer,
    `Sir,' say I, `or Madam, truly your greatness I adore;
    The fact is I was sobbing and so gently you came blogging,
    And so harshly you came clobbing, clobbing at my woes before,
    That I scarce was sure I believed you' - now my server's driver works with glore; -
    Grateful here, forevermore.

    ReplyDelete
  2. I followed each step as it is. And it worked fine. But I am not able to connect to sql server 2008 express running on another machine on windows.
    I think my odbc.ini entry is incorrect
    here it is
    /usr/local/etc/odbc.ini

    [MSSQLTest]
    Driver = SQL Server Native Client 11.0
    Server = 192.168.1.56\SQLExpress,1433

    /usr/local/etc/odbcinst.ini

    [SQL Server Native Client 11.0]
    Description=Microsoft SQL Server ODBC Driver V1.0 for Linux
    Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0
    Threading=1
    UsageCount=3

    Please reply me if you need further inputs to find solution
    Thank You


    ReplyDelete
    Replies
    1. First make sure sql server accepts TCP connection and 2nd, native client 12 tries to connect to def instance so remove \SQLEXPRESS and try to connect again. Hope this helps.

      Delete
    2. ufff....!!! finally I solved it..everything was proper even TCP connection,port etc. I did not installed SQL server management studio before so could not able to create user to access db instance. Now Installed it, created a new user, assigned permission, now everything’s fine :)

      Thank You for replying me..and thanks to bardia
      for this wonderful piece of information.

      Delete
  3. Thanks for the article. I wonder if it possible to use this driver in PHP.

    ReplyDelete
  4. When I use sqlcmd -S this the returned message:
    SqlState HYT00, Login timeout expired
    A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
    TCP Provider: Error code 0x6F

    ReplyDelete