Blog

Our blog for cool new technologies and random stuff.

Connecting to a Microsoft SQL Server database from Python under Ubuntu

Free tools are great, but the world ain’t all sunshine and rainbows. Sometimes, we may need to connect to a Microsoft SQL Server database from one of our Python applications running under Linux. Fortunately, there are ways to achieve this.

I am assuming we got this:

  • • A Microsoft SQL Server installation running under Windows. I tested this using Microsoft SQL Server 2008 R2 Express, but hopefully this will work with other versions as well.
  • Ubuntu Linux. I am using 12.04 LTS.

Without further ado, here are the steps you should follow to get this working.

1. SQL Server setup

Your SQL Server installation must be setup to allow external connections. If the DB is not administered by you this might not be a problem, but in case you do have administrator level access and need to do it yourself, read here.

Now you must have setup a port in which SQL Server is listening. Remember it.

Make sure you are not blocked by Windows firewall or such when you attempt to connect to the Windows computer. Attempting a telnet will help us check if there are connection problems. For example, try running telnet <sqlserverpc> <port> from Ubuntu and check the connection doesn’t fail.

Regarding authentication, I have only tried this with the sa login enabled (ie. not using Windows Authentication). You may read on how to do that here.

2. Install required packages under Ubuntu

These are the things we are going to need:

  • FreeTDS is is a set of libraries that allows programs to natively talk to Microsoft SQL Server databases. It’s what we usually call a driver.
  • UnixODBC acts as a driver manager and is the implementation of the ODBC API.
  • pyodbc is a Python 2.x and 3.x module that allows you to use ODBC to connect to almost any database.

From a terminal, run:

sudo apt-get install unixodbc unixodbc-dev freetds-dev tdsodbc

From the Virtualenv of our Python application (if you are not using one, you should!) run pip install pyodbc.

3. Setup server in FreeTDS’s settings

Edit the file /etc/freetds/freetds.conf and replace placeholders appropriately. Note that we are calling our server sqlserver.

[sqlserver]
    host = <ip address of the computer running SQL Server>
    port = <port>
    tds version = 7.0

After this you can test the connection with this command:

tsql -S sqlserver -U <username> -P <password>

Then run some SQL Server command to make sure everything works fine. For example you may run a DB query like this:

select * from <database name>.dbo.<table name>
go

If it worked, it will print the results of the query. Quit with Ctrl+D.

4. Setup unixODBC to use FreeTSD & add a data source

First, run odbcinst -j to know where our configuration files are located. We will need to edit two files: the “drivers” and “system data source”. I assume they are /etc/odbcinst.ini and /etc/odbc.ini respectively, but the output of the command will tell you this.

Edit /etc/odbcinst.ini like this:

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
# Some installations may differ in the paths
#Driver = /usr/lib/odbc/libtdsodbc.so
#Setup = /usr/lib/odbc/libtdsS.so
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1

If the paths for Driver and Setup do not work in your installation, you can find where these files are located by running find / -name “libtds*”.

Edit /etc/odbc.ini like this, to add a data source named sqlserverdatasource:

[sqlserverdatasource]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = sqlserver
Database = <name of your database>

Now you may test the connection to out data source works by running isql -v sqlserverdatasource <username> <password>.

5. Connect to our data source from a Python application

If everything is fine, with the help of pyodbc it should be really easy! You may try the following snippet:

import pyodbc

dsn = 'sqlserverdatasource'
user = '<username>'
password = '<password>'
database = '<dbname>'

con_string = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (dsn, user, password, database)
cnxn = pyodbc.connect(con_string)

That should be it :-)

21 thoughts on “Connecting to a Microsoft SQL Server database from Python under Ubuntu

  1. Excellent! You've made this real easy.
    I'd recommend using locate rather than find to locate the ftds libs, mine ended up in /usr/lib/odbc/

    Thank you

  2. Alan, I've followed your tut religiously and I still obtain this error:

    pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

    I've falled stumped, any clues?

  3. You, sir, are a genius, Thanks! Hours have been saved here.

    It is interesting how you cannot insert the server details directly in the connection string using Python on Linux, and instead you have to create a DSN and reference that in the connection string… just not very user friendly imo!

  4. Regards, this is happening to me

    nelson@Nelson-Latitude:/etc/freetds$ tsql -S 172.16.22.32 -U sa
    Password:
    locale is "es_NI.UTF-8"
    locale charset is "UTF-8"
    using default charset "UTF-8"
    63Error 20009 (severity 9):
    Unable to connect: Adaptive Server is unavailable or does not exist
    OS error 110, "Expiró el tiempo de conexión"
    There was a problem connecting to the server

  5. Excuse me if English is not correct, but I'm using GoogleTranslate.

    Thank you for the information you have provided, it has served me. I did everything you mentioned, among other things further, however reports me the following error:

    locale is "es_PE.UTF-8"
    locale charset is "UTF-8"
    using default charset "UTF-8"
    Msg 18456 (severity 14, state 1) from PC-07 Line 1:
             "Login failed for user 'username'."
    Error 20002 (severity 9):
             Adaptive Server connection failed
    There was a problem connecting to the server

    I'm still looking on Google, but if you already know the solution to this incidence, I'd appreciate the proportions.

    Greetings.

  6. <a href="yelp.com/biz/xtra-clean-of-santa-monica-santa-monica-2">carpet cleaning Santa Monica</a> <a href=yelp.com/biz/xtra-clean-of-santa-monica-santa-monica-2>Santa Monica carpet cleaning</a> upholstery cleaning Santa Monica yelp.com/biz/xtra-clean-of-santa-monica-santa-monica-2

  7. There is such a storm, it looks as <a href="http://chwilowkawroclaw.tupochwilowki.pl">szybka pożyczka przez internet</a> <a href=http://pozyczka.bez.zaswiadczen.tupokredyt.pl>pożyczka bez zaświadczeń</a> pożyczka chwilówka http://kredytychwilowkilodz.tupochwilowki.pl unusually large compared to the body, his alimentive,thoracic, muscular and bony systems are smaller and less developed thanthe average. bdyxlcwg jszebdl You must go and <a href="http://szybkapozyczkaprzezinternet.tupochwilowki.pl">szybkie pożyczki online</a> <a href=http://kredyt.bez.zaswiadczen.tupokredyt.pl>pożyczki chwilówki online</a> szybkie pożyczki przez internet http://kredyt.na.dowod.tupokredyt.pl a confession to make to you. wuass alrv An earthly paradise in which men should <a href="http://chwilowkaskok.tupochwilowki.pl">szybkie chwilówki</a> <a href=http://tupochwilowki.pl>pożyczki pozabankowe dla zadłużonych</a> kredyt bez biku http://chwilowkaskok.tupochwilowki.pl kinsey went out. Some lady saw a <a href="http://szybkikredytnadowod.tupochwilowki.pl">szybkie pożyczki online</a> <a href=http://szybka.pozyczka.bez.zaswiadczen.tupokredyt.pl>pożyczki chwilówki</a> pozyczki na dowod http://kredytychwilowka.tupochwilowki.pl this. bbloogas esyaqd Constant repetition of the same kinds of thoughts or emotions finallymakes permanent changes <a href="http://kredytychwilowka.tupochwilowki.pl">kredyty pozabankowe</a> <a href=http://pozyczki.pozabankowe.tupokredyt.pl>szybka pożyczka online</a> szybkie pożyczki online http://tupochwilowki.pl jungle made no reply. brerdthp yegrumqni So far as to <a href="http://pozyczkaprzezinternetbezzaswiadczen.tupochwilowki.pl">pozyczki bez biku</a> <a href=http://chwilowki.na.dowod.tupokredyt.pl>pozyczki bez biku</a> pożyczki pozabankowe dla zadłużonych http://tupochwilowki.pl talk about the preparations for hate week. qfknzcz The agents of goldstein <a href="http://pozyczkanadowod.tupochwilowki.pl">pozyczki bez biku</a> <a href=http://kredytbezzaswiadczen.tupochwilowki.pl>pożyczka bez zaświadczeń</a> pozyczka na dowod http://tupochwilowki.pl the cell. Machines in the <a href="http://tupokredyt.pl">pożyczki pozabankowe dla zadłużonych</a> <a href=http://kredytbezporeczycieli.tupochwilowki.pl>pożyczki chwilówki online</a> pożyczki chwilówki http://pozyczkanadowodbezzaswiadczen.tupochwilowki.pl that wall of virtue, even if it were. njsqmtyoo litsulyen Winston let out the water and disgustedly <a href="http://kredytchwilowka.tupochwilowki.pl">pozyczka na dowod</a> <a href=http://szybkapozyczka.tupochwilowki.pl>pożyczki chwilówki przez internet</a> pożyczki przez internet http://kredytbezzaswiadczen.tupochwilowki.pl clung to him. txivhi

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>