Connecting Ubuntu 8.04 to Microsoft SQL Server

While I won’t digress too far from the subject, I will say that I’m excited to finally be at the point in my life where I am stable enough to step back and reflect.  I’m greatly looking forward to this phase as I transition from a user of information to  a creator and editor of such things.

I had a lot of trouble getting my Ubuntu 8.04 LTS Hardy workstation to talk to my SQL Server.  Let me give a few details about the items involved here.  My workstation runs Ubuntu 8.04;  on the workstation I’m running VMware 6.04.  Inside VMware I have an image which is running Microsoft Enterprise Server 2003.  Ontop of this I’m running Microsoft SQL Server Enterprise 2005.  This information applies to you if you are within Ubuntu or any modern user-oriented Linux and trying to connect to any type of Microsoft SQL (MSSQL) Server;  my server just happens to be a VM on my workstation.  We’ll start by assuming you’re MSSQL is properly setup.

Now go into Synaptic Package Manager.  Search and install the following plus whatever requirements it prompts for:  a) iodbc   b) libiodbc2   c) libct3   d)  tdsodbc   e) unixodbc .  Now, you might have to reboot before this works;  I don’t think so but this took me a couple of occasions sporadically over two days so I can’t discount the necessity of a reboot.

Create a launcher shortcut for the iODBC interface.  The executable is located: /usr/bin/iodbcadm-gtk  .  Go into Terminal, which I believe is the Bash-Terminal by default in Ubuntu 8.04.

Goto home directory:  cd ~

Do:  sudo gedit .iodbc.ini

Contents of <.iodbc.ini>

[ODBC Data Sources]
ODBCdsn = ODBC Server

[ODBCdsn]
Driver      = /usr/lib/odbc/libtdsodbc.so
Description = ODBC Server
Trace       = No
Servername  = ODBC
Database    =         {name of your database}

[Default]
Driver = /usr/lib/odbc/libtdsodbc.so

The iODBC is basically setup.  We now need to setup the FreeTDS driver which does the translation for communications purposes to MSSQL.

Do:  sudo gedit /etc/freetds/freetds.conf

Append to bottom of contents of <freetds.conf>

[ODBC]
host = xxx.xxx.xxx.xxx   {IP address of your database server}
port = 1433                      {Port of your database server}
tds version = 8.0              {Google:  FreeTDS if you are on a different version of MSSQL}

Startup the iODBC shortcut we created above.  Goto the User DSN tab.  Click test and authenticate with a valid username and password.  Everything should be working now.  Now that we’ve covered the basic setup, we can always add more options such as buffer length, timeout strings, etc.  All of the above can also be customized and renamed.  Simply make sure that the “ODBC” in “.odbc.ini” is the same as “ODBC” in “freetds.conf”, where you can change “ODBC” to whatever name you want provided the case-sensitive similarity is maintained between the two files.

So there it is.  Simple as a n00b needs it and powerful enough to be a base for power users.  Enjoy!