Successful Microsoft SQL Server Support for PHP on Linux

I recently had a requirement to enable a PHP application on Linux servers to connect to a remote Microsoft SQL Server database. Despite initial concerns that this was impossible or at least very difficult, compounded by numerous confused forum/blog posts suggesting it to be so, it turns out to be eminently achievable.

Unfortunately, there seems to be a lot of misinformation out there, so at the risk of adding to it, here's my writeup of getting PHP on Linux to talk to an MS SQL Server database, using both the mssql_* functions and the Zend_Db component of Zend Framework.

SQL Server Drivers for PHP

The major problem people have seems to be finding drivers for SQL Server for Linux. This bit is of course easy for Windows users, but it also turns out not to be too hard for Linux folks. The solution is a library named FreeTDS, an open-source implementation of the Tabular Data Stream protocol, which is the client-server protocol used by both MS SQL Server and Sybase. (So one little bonus here is that you might well get Sybase support for no extra effort, but I haven't tried it out so don't quote me on that).

The server we're on here is a Red Hat Enterprise Linux 5.5 machine. Some users report success finding FreeTDS in Yum repositories, which is an ideal way to go if possible. Unfortunately, a quick yum search freetds yielded no results, so if readers can share experience here then so much the better.

Still, compiling FreeTDS seems to be a pretty painless business. It's a case of fetching the latest stable version (currently 0.82) from the FreeTDS site, followed by a very standard configure/make/make install procedure:

[root@server ~]# wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
[root@server ~]# tar -zxvf freetds-stable.tgz
[root@server ~]# ./configure
[root@server ~]# make
[root@server ~]# make install

That installs a bunch of header files in /usr/local/include/, along with some libraries in /usr/local/lib, and that's about all there is to say about that.

Enabling PHP Support

Compiling PHP with SQL Server support is, at its simplest, a case of configuring the build using the --with-mssql=<dir> option. The directory in question is the FreeTDS installation directory, which by default is under /usr/local. This should give you PHP's MSSQL extension, and thereby support for the mssql_* functions, but we also wanted to access this particular database from within a Zend Framework app, using Zend_Db.

This is extra special fun, because behind the scenes, Zend_Db does not use the mssql_* functions; rather, it uses PDO to talk to SQL Server databases. PDO itself uses PDO_DBLIB, which in turns uses FreeTDS, which conveniently enough, we just installed!

Thus, adding PDO_DBLIB support to PHP is a question of specifying another configuration option, --with-pdo-dblib=<dir>, in which the directory is once again the FreeTDS installation directory, default /usr/lib/.

As an aside, a lot of people go looking for a --with-pdo-mssql option, and I found forum posts claiming this was needed. This is utterly untrue: that configuration option doesn't exist, because there is no direct support for MSSQL in PDO on Linux; the only way this will work is by using DBLIB.

So let's configure and compile PHP with the new options:

[root@server ~]# ./configure --prefix=/usr/local/php \
      --with-apxs2=/usr/local/httpd/bin/apxs \
      --with-zlib \
      # other options omitted for brevity...
      --enable-pdo \
      --with-mssql=/usr/local \
      --with-pdo-dblib=/usr/local

Follow that with the usual make and make install, and we're nearly there. That should give PHP both the MSSQL extension and PDO_DBLIB. However, it won't actually let you connect to a database, and this is where things get interesting.

Configuring FreeTDS for Connections

Unfortunately, at least on Linux, you can't just follow the documentation and call mssql_connect() on a hostname (or using the hostname,port syntax) and have it, well, connect. This will fail with an unambiguous "mssql_connect(): Unable to connect to server" message. Similarly, trying to fire up a Zend_Db adapter and have it connect will fail, albeit with more cryptic error messages:

FreeTDS: db-lib: exiting because client error handler returned 0
for msgno 20002

That's a particularly unhelpful message, but it simply means that a connection to the database server could not be made.

The solution is to open up the FreeTDS config file, which, with our default settings, is located at /usr/local/etc/freetds.conf, and define the database host in there. You'll probably see a couple of example hosts in there, so add yours at the bottom:

[mybigserver]
host = dbserver.example.org
port = 1433
tds version = 7.0

The name "mybigserver" is just an identifier for the host, and can be pretty much whatever you like, so long as you remember it for later. The host and port directives should be self explanatory, and the tds version should be 7.0 if the database host runs MS SQL Server, and 5.0 if you're talking to Sybase.

While you're in there, you might want to enable logging, as this was a big help for me in getting this working (although it did present some issues which we'll come to in due course):

[global]
; other settings omitted for brevity...
dump file = /tmp/freetds.log
dump file append = yes

Finally, it's just about time to write some code.

Connecting from PHP - the MSSQL Extension

Connecting to your SQL Server database from PHP should now be possible. Remember that host identifier we defined earlier? Well here's where you need it. Instead of passing a hostname (or hostname,port) to mssql_connect(), use the hostname identifier:

<?php

$con = mssql_connect('mybigserver', 'someusername', 'somepassword');
$db  = mssql_select_db('somedatabasename');

That worked just fine for me: finally a working connection to SQL Server, and so fingers crossed it should all have worked for you too.

Connecting from PHP - Zend_Db

The principle is much the same here, you need to connect using the host identifier rather than hostname. This is roughly what's needed in your database config .ini file:

[database]
adapter = PDO_MSSQL
host = mybigserver
username = someusername
password = somepassword
dbname = somedatabasename
pdoType = dblib

Creating a default Zend_Db adaptor from those settings worked like a charm for me. Do note the crucial pdoType directive, which must be set to dblib. If it is not set, Zend_Db will default its value to mssql, which will be fruitless:

PHP Fatal error:  Uncaught exception 'Zend_Db_Adapter_Exception'
with message 'The mssql driver is not currently installed'

This is because PDO on Linux does not have direct MSSQL support, as we spotted above, and therefore we must use DBLIB.

Logging-Related Issues

We previously switched on FreeTDS logging so that we could debug what it was doing behind the scenes. This was most helpful, as the level of detail in the FreeTDS log is considerable. However, logging is not without its issues, and this one was a pain to track down.

Having successfully connected command-line scripts to the SQL Server database using both mssql_connect() and Zend_Db, it transpired that scripts run under Apache were failing to connect, despite identical settings. Furthermore, those command-line scripts were failing to connect when run by any user other than root.

It turns out that the FreeTDS log file is owned by whichever user is running the code that first triggers logging - whether it's root, apache or just a normal shell user. No huge surprises there, but it's also writeable only by that user. The big surprise is that this completely prevents FreeTDS making server connections when run by another user. Delete the log file, or make it world-writable and everything starts working as if by magic.

Making things world-writable always seems a bit brute-force and amateurish. The FreeTDS docs also point out that leaving the logfile in /tmp is a security risk, as there could potentially be sensitive information in there. Furthermore, the logging is comprehensive enough that you'll fill up disk space at a fair rate if this is used in a production environment. I chose to disable FreeTDS logging and only re-enable it when debugging requires it - and with great care.