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

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:
    [FreeTDS]
    Description = FreeTDS v0.91
    Driver = /usr/local/lib/libtdsodbc.so
  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:
[foo]
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.

Comments

Can this be used to connect to a SQL Server that is set for Windows authentication only?
Andreas Mimberg said…
This comment has been removed by the author.
Kris Edison said…
Can I have multiple connections on dsn_foo file?
Matt Woodward 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.
Matt Woodward 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.
Rich Andrews 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

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 …

Setting Up Django On a Raspberry Pi

This past weekend I finally got a chance to set up one of my two Raspberry Pis to use as a Django server so I thought I'd share the steps I went through both to save someone else attempting to do this some time as well as get any feedback in case there are different/better ways to do any of this.

I'm running this from my house (URL forthcoming once I get the real Django app finalized and put on the Raspberry Pi) using dyndns.org. I don't cover that aspect of things in this post but I'm happy to write that up as well if people are interested.

General Comments and Assumptions

Using latest Raspbian “wheezy” distro as of 1/19/2013 (http://www.raspberrypi.org/downloads)We’lll be using Nginx (http://nginx.org) as the web server/proxy and Gunicorn (http://gunicorn.org) as the WSGI serverI used http://www.apreche.net/complete-single-server-django-stack-tutorial/ heavily as I was creating this, so many thanks to the author of that tutorial. If you’re looking for more details on …

The Definitive Guide to CouchDB Authentication and Security

With a bold title like that I suppose I should clarify a bit. I finally got frustrated enough with all the disparate and seemingly incomplete information on this topic to want to gather everything I know about this topic into a single place, both so I have it for my own reference but also in the hopes that it will help others.Since CouchDB is just an HTTP resource and can be secured at that level along the same lines as you'd secure any HTTP resource, I should also point out that I will not be covering things like putting a proxy in front of CouchDB, using SSL with CouchDB, or anything along those lines. This post is strictly limited to how authentication and security work within CouchDB itself.CouchDB security is powerful and granular but frankly it's also a bit quirky and counterintuitive. What I'm outlining here is my understanding of all of this after taking several runs at it, reading everything I could find on the Internet (yes, the whole Internet!), and a great deal…