Fun in the world of FreeBSD interconnectivity

The problem is thus: you’re using anything but Windows, and you need to access data from a Windows server; this means you’re probably dealing with MS SQL Server of one form or another. (If you’re lucky it’s something like MySQL, and then it’s painfully simple, but these things are never simple.)

The ultimate goal, in my case, was to bring the data from MS SQL Server over to my web application, running in PHP. Although I’ve struggled with the specifics in the past, the solution really isn’t so bad. What you need:

1. ODBC connector setup on the MS SQL Server side, and remote access enabled; I’m by no means an expert here, so I’ll avoid the detail here where Google could server you better.
2. FreeTDS installed. “…a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server and Sybase databases.”

There are two configuration files you need to worry about for FreeTDS, and by default these are both in /usr/local/etc/: odbc.ini and freetds.conf.

freetds.conf: For this, you have to create your own section for the particular server, but any options that you don’t include will come from the [global] section’s defaults, and these seem to work just fine. In my own example, the only things I needed to set up were like so:


[Xirtam]
host = 172.30.80.42
port = 1433
nt domain = ZEE


The options there should be fairly self-explanatory.

odbc.ini: Here, you first define your server in the top section, [ODBC Data Sources], with an optional description, i.e. “Xirtam=MS SQL Server 2005 connection”. Then, in a section below, you define the gritty detail:


[Xirtam]
Driver=/usr/local/lib/libtdsodbc.so.0
Description=FreeTDS Connection
Trace=No
Servername=Xirtam
UID=administrator
Port=1433


The item identified in brackets above, “Xirtam” is the DSN. This is used in the final connection code, which you could test with isql or some other tool, or just jump straight to the PHP connection code:


$connect = odbc_connect($DSN, $user, $pass);
$result = odbc_exec($connect, "USE databaseOfDoom");


The rest is left as an exercise to the reader.

1 comment June 10th, 2007