Sunday, August 19, 2012

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.


chesapeakesailor said...

Can this be used to connect to a SQL Server that is set for Windows authentication only?

Matt Woodward said...


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