FreeTDS

FreeTDS on Linux Mint 15

Posted on by Matt Woodward

If you’re trying out or moving to Linux Mint and you’re used to setting up FreeTDS on Ubuntu, you’ll find that things are just slightly different on Mint.

Quick and easy fix:
sudo apt-get install freetds-bin tdsodbc

Also note that libtdsodbc.so in a different place on Mint than on Ubuntu, so instead of it being located at /usr/local/lib/libtdsodbc.so it’ll be here:
/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

Creating a Datasource with FreeTDS and unixODBC

Posted on by Matt Woodward

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.

FreeTDS Quick Start

Posted on by Matt Woodward

This is the first of a couple of follow-ups to my last post which covered how to install pyodbc on Ubuntu. The ultimate goal here is to be able to use Python (my new development weapon of choice) to communicate with SQL Server from GNU/Linux (specifically Ubuntu).

Part of this equation is to install FreeTDS, which is a set of C libraries that facilitate talking to SQL Server from Linux. I’m still wading through both pyodbc and pymssql but from what I can tell thus far both these solutions use (or can use) FreeTDS. (Random aside: if you’re familiar with jTDS that’s a Java implementation of FreeTDS. And another fun fact, TDS stands for “Tabular Data Stream,” which is the protocol used by the native SQL Server client from Microsoft.)

Installing and using FreeTDS is simple enough but I figured I’d take notes for my own reference and share in case they help anyone else wanting to set this up.

Installation

If you’re familiar with compiling and installing things from source on Linux there’s nothing new here, and if you’re not, this will show you just how easy this is.

First, download FreeTDS by clicking on the “stable release” link on the right-hand side of http://freetds.org, extract the tar file, and in a terminal, navigate to the directory to which the files were extracted.

Next, type the following three commands, hitting enter after each one:
./configure
make
sudo make install

You’ll see a ton of stuff being spit out to your terminal as you do this, but unless you see actual error messages that cause the execution of any of this to abort, that’s all there is to it.

You can test the installation and get some information about the compile settings, etc. by typing this in a terminal:
tsql -C

With FreeTDS installed, now you just need to add some information to a configuration file to provide details about the servers and databases to which you wish to connect.

Configuration

In your terminal, navigate to /usr/local/etc/ and in that directory you’ll see the file freetds.conf Open that file in vim or your favorite text editor (note that you need to open the file using sudo).

The file contains some global settings and a couple of sample server entries, and you’ll just be adding a new section to this config file.

At the bottom of the file add the following information, adjusting as appropriate with the connection information for your server and database.

[foo]
    host = my.sqlserver.com
    port = 1433
    tds version = 7.2

A few notes about these settings:

  • The [foo] in brackets at the top can be anything you want. That’s simply an alias for these configuration settings, and we’ll see how this is used in the “testing” section below.
  • The host is the host name or IP address of the server
  • The port is (obviously) the port number. By default SQL Server uses port 1433 but of course change accordingly if you’re running on a non-standard port or using a named instance (which under the covers is just a port).
  • The tds version setting is specific to the version of SQL Server (or Sybase) to which you’re connecting. Use 7.0 for SQL Server 7, 7.1 for SQL 2000, and 7.2 for SQL 2005 or 2008 (full details about TDS versions).

The FreeTDS docs have more details about the configuration file, including some additional settings you can include in the configuration file.

Testing

With your configuration file updated and saved, you can test your settings using the tsql utility that is installed as part of FreeTDS.

In a terminal enter the following:
tsql -S foo -U username -P password -D databasename

Notes about these flags and values:

  • The -S flag for the “server” is referring to the alias you created in the configuration file above.
  • -U is the user name and -P is the password for the database to which you wish to connect.
  • -D is the database name you wish to use after connecting to the server. If you don’t provide a database name, it will connect to the default database for the user.

After you connect successfully you should see this prompt:
1>

At that prompt you can enter and execute SQL statements against the database to which you connected.

More information about the tsql utility can be found on the “Confirm the install” page of the FreeTDS web site.

Errors and Troubleshooting

I personally didn’t run into any issues in setting up FreeTDS that couldn’t be chalked up to user error due to a lack of willingness to RTFM on my part.

That said, I just installed everything on a new machine this weekend and I do get an innocuous error after connecting to SQL Server:
Error 100 (severity 11):
    unrecognized msgno

At least I’m assuming it’s innocuous since everything seems to be working, and unfortunately I couldn’t find any additional information about this error (though admittedly I didn’t hit up the mailing list yet).

Getting More Information

FreeTDS is an extremely well-documented project, so be sure and take advantage of the User Guide, which has a wealth of information about using FreeTDS in various programming languages, troubleshooting, advanced configuration, and more.

That’s pretty much it for FreeTDS. Next time we’ll bring pyodbc and pymssql back into the picture and start running some queries against SQL Server using Python.