Instructions for connecting to MS-SQL server from a Unix server without paying for a MS-SQL Unix ODBC driver
Required Packages:
Relationships:
The libiodbc library provides the ODBC framework for Unix. A driver is necessary to connect from the ODBC framework to the MS-SQL server. This is accomplished by the freetds libraries. The perl modules DBI and DBD-ODBC allow perl to access ODBC databases.
Discussion:
FreeTDS was the missing link in my hunt for a free MS-SQL ODBC implementation for Unix. The perl modules and ODBC interfaces for Unix are well known and used but until now the only MS-SQL drivers for unix ODBC I could find required purchasing. FreeTDS finally made it possible bridge the gap into Microsoft world.
Installing:
The installation procedures from here are for FreeBSD using the ports system. Although the procedures will be different for other systems architectures, the intent will be the same.
1. Installing libiodbc.
Do not confuse this with unixODBC. They are not the same thing and it appears that unixODBC is more widely used since it turned up more often in my searches.
Version: libiodbc-3.0.5_1
cd /usr/ports/databases/libiodbc
make install
2. Installing freetds.
The install for me was a little more complicated than I will document. Essential I believe the port was messed up by the original developers. Specifically they named their un-tarred directory wrong and they didn't update their checksum. I got around this by renaming the main freetds directory, re-tarring/gzipping it, and using NO_CHECKSUM=1 in the make install line. This problem only exists if you're using the ports installation method and only until they fix it. Folks with other BSD, Linux, etc installing from source might not have these problems.
Version: freetds-0.61_1
cd /usr/ports/databases/freetds
make install WITH_IODBC=1
3. Installing p5-DBI and p5-DBD-ODBC.
Since the ports collections will complete dependencies, installing p5-DBD-ODBC will result in p5-DBI being installed automatically.
Version: p5-DBI-1.38 and p5-DBD-ODBC-1.06
cd /usr/ports/databases/p5-DBD-ODBC
make install
4. Configuring the ODBC.ini.
Contrary to the FreeTDS and libIODBC documentation, the odbc.ini file needs to be in /etc. To adhere to the documentation, I created the odbc.ini file in /usr/local/etc/libiodbc and sym-linked it to /etc. Here's the contents:
The TDS_Version of 8.0 is equivilent to MS-SQL server 2000 (since the previous version was SQL server 7). Port 1433 is the MS-SQL port to use. This'll be different for other database vendors.
;
; odbc.ini
;
[odbcname]
Database=databasename
Server=servername.company.com
TDS_Version=8.0
Port=1433
[Default]
Driver = /usr/local/lib/libtdsodbc.so
I found that I didn't even need to configure the freetds.conf or any other conf file than that.
Example:
Now for the scripting. To connect to the database from perl I used:
use DBI;
In the example, I need to authenticate with the domain for access to the SQL server, so the USERNAME I had to pass was "DOMAIN\USERNAME". In other words precede the username with the domain and a backslash. Everything else from there was standard perl DBI code.
my $dbh = DBI->connect('DBI:ODBC:odbcname','USERNAME','PASSWORD') || die DBI->errstr();