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";