Skip to main content

Installing MS SQL Server driver on Ubuntu Linux

Although making a connection to SQL Server can be done using unixODBC and FreeTDS, it does not work with some collations on SQL Server 2008. Recently, Microsoft released an ODBC driver for Linux that works on 64-bit systems only. Installing the driver is tricky. I begin by installing the build-essential, which is required for compiling from source. Checkinstall can be installed if you want to add the following application to the 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 the 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 the 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 the following command to find out if there are other missing dependencies.
$ ldd sqlncli-11.0.1790.0/lib64/libsqlncli-11.0.so.1790.0
Ensure "/usr/local/lib" is in the 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())

Comments

  1. UnknownMay 5, 2013 at 4:57 PM

    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. UnknownMay 22, 2013 at 5:34 AM

    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. AnonymousMay 22, 2013 at 11:19 AM

      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. UnknownMay 23, 2013 at 4:35 AM

      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. AnonymousSeptember 2, 2013 at 3:18 AM

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

    ReplyDelete
  4. UnknownJuly 19, 2014 at 7:48 AM

    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

Post a Comment

Popular posts from this blog

Installing pyodbc on ubuntu

Installing pyodbc is straight forward but if  getting     "error: command 'gcc' failed with exit status 1"  when installing pyodbc, make sure you have installed  requirements  as below. sudo apt-get install unixODBC-dev g++ pip install pyodbc
2 comments
Read more

Monkey Patching Django Model Class

Django is shipped with some useful application, also there are lots of third party re-usable application written for Django. Sometime you need to modify or customize these applications(Models of application) to meet your software requirements. One of the usual solution is adding a Model class to have a one-to-one relationship with the third party Models. This solution has some disadvantages such as having more tables and classes which can highly adversely affect database performance, scalability and add more complexity to the software development.  I think of another solution to this problem. I used Django South along with monkey patching of third party Model classes. I added a field to Django user class. Here is a walk through over this approach. 1. Add an application: python manage.py startapplication userpatch 2. Add the following  code to add the new field to the user class from django.db import models from django.contrib.auth.models import User ...
1 comment
Read more