Monday, November 25, 2013

Connecting to SQL Server 2005 Using Perl and DBI on RHEL/CentOS

I wasted a good bit of time getting this to work correctly. I had done it once before, years ago, but lost the script. Basically, I'm connecting using Perl's DBI (with ODBC) and FreeTDS.

My RPMs are (as of now):

rpm -qa | egrep -i \(freetds\|odbc\)



freetds-0.91-2.el6.x86_64

perl-DBD-ODBC-1.23-1.el6.rf.x86_64

unixODBC-2.2.14-12.el6_3.x86_64



Basically:

1. install the appropriate RPMs

2. Edit /etc/freetds.conf and add each SQL Server DB server you want to connect to:

[mySQLServer1]
host = mysqlserver1s.hostname.or.ip.address
port = 1433
tds version = 7.0

[mySQLServer2]
host = mydbserver2.mydomain.com
port = 1433
tds version = 7.0

3. edit /etc/odbcinst.ini

[FreeTDS]
Driver  = /usr/lib64/libtdsodbc.so.0
Description = v7

4. Finally, edit /etc/odbc.ini and put in a stanza for each database you wish to connect to. You may have multiple DBs per database server/host

[mydb1]
Driver = FreeTDS
# ServerName corresponds to a stanza in /etc/freetds.conf - not an IP address or hostname
ServerName=mySQLServer1
# Port is optional, you can specify it in /etc/freetds.conf, instead
Port = 1433
#TDS Version is optional... you can specify it in /etc/freetds.conf, instead
Database = mydatabaseinstance

[mydb2]
Driver=FreeTDS
ServerName=mySQLServer1
Database=mydatabaseinstancetwo
# Here we have multiple DBs on the same server

[mydb3]
Driver=FreeTDS
ServerName=mySQLServer2
Database=yetanotherdbinstance

After all this, you can test your connectivity with isql:

isql -v mydb2 MYWINDOWSDOMAIN\\myuser MYPASSWORD

+---------------------------------------+
| Connected!                                          |
|                                                             |
| sql-statement                                        |
| help [tablename]                                   |
| quit                                                       |
|                                                             |
+---------------------------------------+
SQL>


As you can see, the first entry after the verbose flag corresponds to the stanza name in /etc/odbc.ini

Here's the connection info for Perl:

#!/usr/bin/perl -w

use DBI;
use strict;
my $dsn = "mydb1";
# this corresponds to the relevant stanza in /etc/odbc.ini
my $user = 'MYWINDOWSDOMAIN\user';
# single quote marks remove the need to escape the "\"
my $pass = "myBigDifficultPassword";
my $dbh = DBI->connect("dbi:ODBC:$dsn", "$user", "$pass") 
      or die "Cannot connect to DB $dsn: $DBI::errstr\n\n";