Connecting to a MS SQL server. What an annoying process if you’re running Linux or Mac OSX. Looking past the fact that using MS SQL to me feels sort of odd these days connecting is non-the-less required. Ultimately our goal is to establish an ODBC connection to the database we can use to then leverage RODBC or pyODBC. Here I’ll cover the steps required to install all the necessary parts.
Running on a Mac
I’m going to cover the Mac OSX operations first as they require one more crucial step. From here it will be easy to cover the linux setup as well.
Step 1: brew install unixodbc
This is, assuming you have homebrew installed. If you don’t there are plenty of guides out there to help with that. Note that homebrew dumps all its install files in /usr/local/Cellar. So when you install unixODBC you should see a folder appear in the cellar folder.
Step 2: brew install freetds –with-unixodbc
As you might have guessed this installs FreeTDS. The with argument is crucial as it sets up the links between FreeTDS and unixODBC, meaning that FreeTDS will know where to look for the ODBC drivers. If you don’t do this it will drive you a little crazy and you’ll be forced to move config files around a bit.
Step 3: Setup freetds.conf
Now we need to set up the config file for freetds (using homebrew found in /usr/local/Cellar/freetds/version.number/etc). Go to this location and open the file in the editor of your choice (I’m using VIM so vim freetds.conf)
The the config setup should look like:
[EXNAME] host = destination ip
port = 1433
tds version = 7 (or 8 depending on your MS SQL server)
Input your information ass appropriate and close and save the file. (in vim hit i to enter insert mode. When done hit ESC to exit insert mode and then write/quit: :wq and hit enter)
The [EXNAME] represents the local DNS name we will establish for our destination server. You will see it referenced in the unixODBC config files and in connections strings.
Step 4: Test the TDS setup
To test the TDS setup we are going to try to connect to the server viz a stripped down SQL tool known as tsql. This comes with FreeTDS. To test the connection:
tsql -S EXNMAE -U MyUserName -P MyPassWord
Hit enter. You should see a tsql prompt:
locale is …..
locale charset is …..
This means the connection was successful. To exit tsql: 1> exit
Step 4b: Some deeper testing
This is optional. If you want to actually try to get some data out of the DB before setting up unixODBC the easiest way is actually to pipe a small table into a local file. Note that this step requires knowing the database name, a table name and so on. Also pick a small table.
freebcp MyDatabaseName.dbo.MyTableName out ~/foo.test -c -t ‘|’ -S EXNAME:1433 -U MyUserName -P MyPassWord
Now, if everything (including your DB access permissions) is working you should see data in the file ~/foo.test (try looking with less: less ~/foo.test )
Step 5: Setup unixODBC .ini files
Ok now we need to setup the unixODBC files. Before we leave the FreeTDS folder though we’ll want to note the location of the tds drivers, specifically libtdsodbc.so.
Check to make sure they are located in/usr/local/Cellar/freetsd/version.number/lib and note this filepath. In fact change directory to that location and check the permissions on the dirver la -la ./libtdsodbc.so and make sure it is user executable. If it isn’t make it (chmod to the number of your choice.
With this directory noted (you’ll need it later), change directory to the unixODBC install location and navigate to the .ini file location:/usr/local/Cellar/unixodbc/version.number/etc
You should see two files, odbc.ini and odbcinst.ini. Let’s start with odbcinst.ini because we will need to reference it inodbc.ini.[FreeTDS] Description = FreeTDS
Driver = /usr/local/Cellar/freetsd/version.number/lib/libtdsodbc.so
UsageCount = 1
Now we will point odbc.ini to the [FreeTDS] object we just created.[EXNAME] Driver = FreeTDS
Description = ODBC INI FILE
ServerName = EXNAME
UID = MyUserName
PWD = MyPassWord
Running on Linux (Ubuntu 12.04 and 14.04 LTS)
Essentially everything is exactly the same. This filepaths will obviously be different as you won’t be using brew. One thing to point out is that ODBC.ini will be a little different.
[FreeTDS] Description = FreeTDS
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
UsageCount = 1
Again, you will need to make sure to change the file permissions on both libtdsodbc.so and libtdsS.so to make them executable.
You should now be able to connect with isql, isql DNS MyUserName MyPassWord and actually run some queries. Additionally, things like RODBC and pyODBC will now work file. If you run into any issues there is probably a typo somewhere or things are in the wrong place. To test run oslq, osql -S DNS -U MyUserName -P MyPassWord it will essentially tell you where you messed up.