I’ve recently been trying to connect to a Microsoft SQL Server from a CentOS 6.5 system, using my tool of choice, Perl 5.10.

I chose to use unixODBC, and Microsoft’s own closed-source driver.

There are a few excellent articles out there on using unixODBC, such as EasySoft’s tutorial. The authors of that article, EasySoft, sell their own ODBC driver, which I didn’t try but probably should have. Or, perhaps FreeTDS would have been easier.

This has been moderately problematic, due to Microsoft’s driver requiring unixODBC 2.3.0, which does not exist in the standard package repositories. They expect you to build from source. So, I tried to build it as a .rpm, but this didn’t work quite perfectly. I looked on rpmfind, and found that there’s a unixODBC 2.3.1 available in Fedora Core 17, of course, building that on CentOS didn’t help because the MS driver checks for the existence of unixODBC 2.3.0 exactly, during its installation script. Gee, thanks MS!

Now I could go off on a lengthy rant here about MS being a bit NIH and not understanding package management (well Windows still has no such concept)… but no. Here’s how I got round this – it’s incomplete, but due to other committments I had to drop this work, so if this is of use to others, great…

So, I took the 2.3.1 .spec file, and added a patch that renamed all the .so files to .so.2, as required by perl-DBD-ODBC and built 2.3.0 with it… it didn’t quite work, as the MS driver is linked against libodbcinst.so.1! So, one quick ln -s libodbcinst.so.2.0.0 libodbcinst.so.1 later, I have the combination of unixODBC 2.3.0, perl-DBD-ODBC 1.48-1 and MS’ libmsodbcsql-11.0.so.2270.0 working together. A bug report about similar problems with unixODBC and the Oracle driver was filed here – it looks like the MS driver could never work with unixODBC out of the box; a new MS driver linked against the correct libraries is what’s required.

Perhaps I should have built unixODBC from source as the MS page suggests, but I thought I’d be better off taking the same packaging instructions as similar RPM-based distros do. Anyway, I’ve got it working.

My .odbc.ini file looks like (data source name and hostname modified):

[MyDataSource]
Driver = ODBC Driver 11 for SQL Server
Server = tcp:mysqlserver.organisation.com

The perl script I used to test it:

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use DBD::ODBC;
my $server = "mysqlserver.organisation.com";
my $db_user = "myuser";
my $db_pass = 'secret';
my $dbh = DBI->connect("DBI:ODBC:MyDataSource", $db_user, $db_pass) or die "$DBI::errstr\n";
print "Connected\n";
my $query = "Select count(*) from [mytable].dbo (NOLOCK)";
my $sth = $dbh->prepare($query) or die "$DBI::errstr\n";
$sth->execute or die "$DBI::errstr\n";
# go do stuff!
# Close the database
$sth->finish;
$dbh->disconnect;

You can find my modified unixODBC.spec here, and the small patch to rename the .so’2 can be found here.

Advertisements