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


Comments

  1. Ron
    Ron on 10/17/2012 11:33 a.m.
    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. David
    David on 11/08/2012 8:11 a.m.
    Genios!
  3. Rima
    Rima on 12/02/2012 3:10 a.m.
    Thanks for this! Is there any idea on how to use SQL Server as a database back-end for a django application?
  4. Paul von Hoesslin
    Paul von Hoesslin on 12/06/2012 12:28 p.m.
    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?
  5. Linksular
    Linksular on 12/17/2012 5:49 a.m.
    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!
  6. sapardi
    sapardi on 01/14/2013 2:50 a.m.
    Excellent!!!
  7. Nathan Wienand
    Nathan Wienand on 02/08/2013 5:14 a.m.
    thank you sir ... for saving my life with this...
  8. Felipe Santiago
    Felipe Santiago on 04/09/2013 8:12 a.m.
    Thank you!!!
  9. Ajeesh T Vijayan
    Ajeesh T Vijayan on 05/02/2013 1:52 a.m.
    Excellent article.
  10. nelson
    nelson on 08/14/2013 5:46 p.m.
    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
  11. gigi
    gigi on 12/05/2013 8:22 a.m.
    Thanks a lot mate! Very useful indeed! :D
  12. Maria
    Maria on 02/26/2014 5:56 p.m.
    Thanks so much for this!
  13. Palash kulshreshtha
    Palash kulshreshtha on 04/09/2014 10:10 a.m.
    any one facing "Server name not found in configuration files." must copy his freetds.conf file to his home directory with "." for hiding it. I found out that tsql searches for connection per user here http://stackoverflow.com/questions/17064066/error-20012-severity-2server-name-not-found-in-configuration-files
  14. wwscfheg
    wwscfheg on 08/09/2014 9:36 a.m.
    <a href="http://coopecan-peru.com">upholstery cleaning oxnard</a> <a href=http://coopecan-peru.com>hardwood cleaning oxnard</a> hardwood cleaning oxnard http://coopecan-peru.com
  15. eknyycpu
    eknyycpu on 08/11/2014 8:25 p.m.
    <a href="http://coopecan-peru.com">carpet cleaning oxnard</a> <a href=http://coopecan-peru.com>area rug cleaning oxnard</a> stone floor cleaning oxnard http://coopecan-peru.com
  16. dulrtdaw
    dulrtdaw on 08/11/2014 8:25 p.m.
    <a href="http://coopecan-peru.com">stone floor cleaning oxnard</a> <a href=http://coopecan-peru.com>carpet cleaning oxnard</a> hardwood cleaning oxnard http://coopecan-peru.com
  17. xggsiwzw
    xggsiwzw on 08/12/2014 11:07 p.m.
    <a href="http://sabakikarate.net">martha stewart patio furniture sets</a> <a href=http://sabakikarate.net>martha stewart patio set</a> martha stewart patio furniture sets http://sabakikarate.net
  18. aaauikvt
    aaauikvt on 08/13/2014 7:21 a.m.
    The Political Reformer Because all forms of personal combat are distasteful to him the pureCerebral does not go out <a href="http://www.ktre.com/story/26021272/michael-zimmerman-hedge-fund-prentice-capital-bullish-on-stocks-as-may-retail-spending-leaps-06">michael zimmerman fund</a> <a href=http://fr.advfn.com/actualites/Michael-Zimmerman-Hedge-Fund-Prentice-Capital-Bull_62908193.html>Michael Zimmerman Hedge Fund</a> prentice capital http://www.ad-hoc-news.de/forum/vermischtes/michael-zimmerman-hedge-fund-prentice-capital-bullish-on-37845662.html edges of the pit, and.
  19. nwxyiwfw
    nwxyiwfw on 08/14/2014 9:13 a.m.
    <a href="http://coopecan-peru.com">hardwood cleaning oxnard</a> <a href=http://coopecan-peru.com>carpet cleaning oxnard</a> area rug cleaning oxnard http://coopecan-peru.com
  20. cpfztlmq
    cpfztlmq on 08/19/2014 3:15 p.m.
    <a href="http://maestrocubano.com">woodworking projects</a> <a href=http://maestrocubano.com>wood carving</a> wood carving http://maestrocubano.com
  21. wcstlnjx
    wcstlnjx on 08/21/2014 8:31 p.m.
    <a href="http://alyssatalkingback.com">vegas shows</a> <a href=http://beatzone.org>best las vegas shows</a> hotels in las vegas http://beatzone.org
  22. ektgpppr
    ektgpppr on 08/26/2014 10:14 a.m.
    Theycause his nearest and dearest to <a href="http://rockwindllc.com">upholstery cleaning</a> <a href=http://rockwindllc.com>Calabasas rug cleaning</a> upholstery cleaning http://rockwindllc.com morning he left his bride, and went away by himself,she knew not whither, till the night came again.
  23. Jose
    Jose on 08/29/2014 4:44 a.m.
    Great!! thank u it works fine fron a raspberry
  24. puzysmlm
    puzysmlm on 08/30/2014 6:15 a.m.
    Cheer up, Becky, and lets go on tryIngenieurThey rose <a href="http://creepygal.com">las vegas shows</a> <a href=http://creepygal.com>las vegas shows</a> las vegas hotels http://alerteamberquebec.com build it?
  25. rpvthutl
    rpvthutl on 08/30/2014 6:15 a.m.
    He could not recollect that the closing word <a href="http://alerteamberquebec.com">las vegas hotels</a> <a href=http://alerteamberquebec.com>las vegas shows</a> las vegas hotels http://alerteamberquebec.com and went about from window to window cautiously peepingout.
  26. fvlvlhob
    fvlvlhob on 09/05/2014 7:05 a.m.
    Of weak waste <a href="http://paintball.vilniusstagdo.co.uk">vilnius stag do</a> <a href=http://vilniusstagdo.co.uk>things to do in vilnius</a> vilnius weekends http://whattosee.invilnius.net he hasswallowed down for his supper, can be still alive?
  27. gauebmll
    gauebmll on 09/05/2014 7:05 a.m.
    Some sense of the profound <a href="http://corporateincentives.vilniusweekends.com">things to do in vilnius</a> <a href=http://oldtown.invilnius.net>vilnius</a> vilnius weekends http://strippers.vilniusstagdo.co.uk was rotten under the surface,.

Post your comment

:

:

(Optional):

:

(Optional):