Skip to main content

Creating a Datasource with FreeTDS and unixODBC

In a couple of previous posts I've been working up to using Python to talk to SQL Server from Linux. There are a few moving parts involved so I'm documenting as I go.

Thus far I've done the following:
  1. Installed unixODBC
  2. Installed FreeTDS
  3. Installed pyodbc
  4. Installed pymssql
Note that pyodbc and pymssql accomplish the same thing in slightly different ways, and I'll document both as I get those set up.

To put the first two items above in perspective, unixODBC provides the underpinnings for the ODBC API on Unix/Linux platforms. unixODBC does not, however, provide what you need to talk to specific databases. For that you need ODBC drivers for each database with which you wish to communicate, which is where FreeTDS comes in.

FreeTDS is an implementation of the Tabular Data Stream protocol, which is the protocol used by SQL Server (and Sybase), and FreeTDS includes ODBC libraries that you can install in unixODBC. So when you put these two pieces together, you can talk to SQL Server from Linux over ODBC.

If you were following along with the previous couple of posts at this point you'd have unixODBC and FreeTDS installed, but FreeTDS hasn't yet been installed as a driver in unixODBC, which is what this post will cover. I found a lot of dated information out in the interwebs so hopefully this will bring things more up to date. Specifically I'm doing this on Ubuntu 12.04, so beware that the specifics of where things get installed on other distros might differ slightly (find | grep is your friend!).


Installation basically involves creating a template file for FreeTDS that we can read into unixODBC to install FreeTDS as an available ODBC driver, and then running odbcinst to install the FreeTDS driver in unixODBC.

  1. Open up a terminal and navigate to /etc/ODBCDatasources
  2. Using vim or whatever editor you prefer, create a new file. It can be called anything; I called mine tds.driver.template since that's the name I saw someone use in another post about getting this set up.
  3. Type the following into the newly created file and save it:
    Description = FreeTDS v0.91
    Driver = /usr/local/lib/
  4. Install the driver into unixODBC by running the following command:
    sudo odbcinst -i -d -f tds.driver.template
After running that final command you should see something like this:
odbcinst: Driver installed. Usage count increased to 1.
    Target directory is /etc

If you get an error -- the one I ran into was "odbcinst: SQLInstallDriverEx failed with Invalid install path" -- double-check everything and make sure to run the odbcinst command as root. I originally wasn't doing sudo which is why I got the error I saw, but when I ran the command with sudo it went away and the driver was installed.

For an extra warm fuzzy you can have unixODBC list the installed drivers:
odbcinst -q -d

Creating a Datasource

As you might be familiar with from the ODBC of the Windows world, you can pre-define ODBC datasources to easily access the datasource via an alias instead of providing all the connection details when you wish to connect.

Let's go ahead and create a datasource pointing to a SQL Server database so we have that in place for future use from Python.

Again in the /etc/ODBCDatasources directory, create a new file for your datasource. You can call the file whatever you want; I called mine dsn_foo

In that new file enter the following, adjusting according to the server and database to which you want to connect:
Driver = FreeTDS
TDS_Version = 7.2
Description = My foo datasource
Server = my.sql.server
Port = 1433
Database = foo

Couple of points about the datasource configuration file:
  • [foo] at the top is the alias/datasource name by which you'll refer to the datasource when you want to connect to it
  • Driver = FreeTDS is obviously referring to the FreeTDS driver alias that was created in unixODBC in the steps above
  • TDS_Version is dependent upon the version of SQL Server to which you're connecting. 7 is for SQL 7, 7.1 is for SQL 2000, and 7.2 is for SQL 2005 and 2008.
  • Concerning the Server setting -- I read some references to using Servername instead and providing the alias created in freetds.conf file, but other sources explicitly said NOT to use that. I stuck with Server and provided a host name since that works.
  • The rest of the settings are self-explanatory, but there's a list of all the configuration options available in the FreeTDS User Guide. Note that some settings I'm using in my examples work even though they differ from what's listed in the guide.
  • From my testing you cannot include the username and password in the datasource configuration file. Obviously that's a balance of handy vs. security concerns anyway. Some examples show including it but according to my testing as well as the FreeTDS documentation you have to provide the username and password as part of the connection string as opposed to storing it in the configuration file.

Save that file, and then install the datasource in unixODBC:
sudo odbcinst -i -s -l -f dsn_foo

If it installs successfully in true *nix philosophy you'll see nothing as a response to this action. If like me you like verifying things worked, you can list the datasources installed in unixODBC:
odbcinst -q -s

If you see your datasource listed, it's installed.

You can also check out what unixODBC puts in its configuration file based on your installation by looking at /etc/odbc.ini

Testing the Datasource

The final step in this process is to run a query against the datasource to make sure everything's working, and we'll do that using the isql tool that's included with unixODBC.

In a terminal run the following:
isql foo username password

In the command above foo is the datasource alias, and then as outlined above you do have to provide the username and password since they aren't included in the datasource configuration itself.

If everything's in order you'll get a SQL> prompt and can run SQL statements against your database.

We're getting closer to our final goal, running SQL statements against SQL Server from Python! I'll cover that in my next post.


Can this be used to connect to a SQL Server that is set for Windows authentication only?
Anonymous said…
Andreas Mimberg said…
This comment has been removed by the author.
Kris Edison said…
Can I have multiple connections on dsn_foo file?
Anonymous said…
I haven't tried multiple datasources per file but I'd think that'd work since each would have a clear delineation within the file. You'd have to test it to be sure.
Kris Edison said…
BTW, do I really need to do
odbcinst -i -d -f
odbcinst -i -s -l -f
everytime before running any program? Or it's just weird thing on my system.
Anonymous said…
Shouldn't need to do that every time no -- that'd be a one-time thing. All the relevant info gets written to a config file when you run those commands so once it's in there it's in there.
Anonymous said…
Thanks for aggregating the steps from the doc of the individual components. None of which indicates a clear solution as you have. This helped getting unixodbc set up for connecting R from ubuntu to SQL Server.
redarc coder said…
You are the real MVP dude, nice job ;D
Richard Majece said…
It's clear for me that information from will help you with writing bibliography. You can use it if you want to write high quality academic writer.

Popular posts from this blog

Installing and Configuring NextPVR as a Replacement for Windows Media Center

If you follow me on Google+ you'll know I had a recent rant about Windows Media Center, which after running fine for about a year suddenly decided as of January 29 it was done downloading the program guide and by extension was therefore done recording any TV shows.

I'll spare you more ranting and simply say that none of the suggestions I got (which I appreciate!) worked, and rather than spending more time figuring out why, I decided to try something different.

NextPVR is an awesome free (as in beer, not as in freedom unfortunately ...) PVR application for Windows that with a little bit of tweaking handily replaced Windows Media Center. It can even download guide data, which is apparently something WMC no longer feels like doing.

Background I wound up going down this road in a rather circuitous way. My initial goal for the weekend project was to get Raspbmc running on one of my Raspberry Pis. The latest version of XBMC has PVR functionality so I was anxious to try that out as a …

Running a Django Application on Windows Server 2012 with IIS

This is a first for me since under normal circumstances we run all our Django applications on Linux with Nginx, but we're in the process of developing an application for another department and due to the requirements around this project, we'll be handing the code off to them to deploy. They don't have any experience with Linux or web servers other than IIS, so I recently took up the challenge of figuring out how to run Django applications on Windows Server 2012 with IIS.

Based on the dated or complete lack of information around this I'm assuming it's not something that's very common in the wild, so I thought I'd share what I came up with in case others need to do this.

This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.

Assumptions and CaveatsThe operating system is Windows Server 2012 R2, 64-bit. If another variant of the operating system is being used, these instructions may not work properly.All of the soft…

Fixing DPI Scaling Issues in Skype for Business on Windows 10

My setup for my day job these days is a Surface Pro 4 and either an LG 34UC87M-B or a Dell P2715Q monitor, depending on where I'm working. This is a fantastic setup, but some applications have trouble dealing with the high pixel density and don't scale appropriately.
One case in point is Skype for Business. For some reason it scales correctly as I move between the Surface screen and the external monitor when I use the Dell, but on the LG monitor Skype is either massive on the external monitor, or tiny on the Surface screen.
After a big of digging around I came across a solution that worked for me, which is to change a setting in Skype's manifest file (who knew there was one?). On my machine the file is here: C:\Program Files\Microsoft Office\Office16\LYNC.EXE.MANIFEST
And the setting in question is this:
Which I changed to this: <dpiAware>False/PM</dpiAware>
Note that you'll probably have to edit the file as administr…