Adding support for MS SQL Server to PHP in Linux

Adding support for MS SQL Server in PHP is not very difficult. Searching (Google/Bing/whatever) reveals lots of information on how to do this with Windows - naturally - but very little on how to go about it using Linux. Most people use precompiled PHP installations and I will show how to add MS SQL Server support to a precompiled PHP installation here. Those of you compiling PHP yourselves will probably understand what to do and what not based on the information here as well.

1. Install FreeTDS

First download and install FreeTDS from freetds.org. Use the following build commands to enable support for MS SQL Server (as root or using sudo).

./configure --enable-msdblib --prefix=/usr/local/freetds
make && make install

Unfortunately you need to tweak the installation somewhat as PHP still checks for files in FreeTDS that is no longer part of the installation. Just make sure these files exist (empty) by issuing the below commmands. (If you use another –prefix path above you will need to change the path accordingly)

touch /usr/local/freetds/include/tds.h
touch /usr/local/freetds/lib/libtds.a

2. Get the PHP source and compile the mssql extension

Yes - you need the complete PHP source even though you already have a precompiled PHP installed. You will not touch your PHP installation and we are not going to compile all of PHP. We need the source to be able to compile the mssql extension.

It is advised to always use the source of the same PHP version you have installed!

Unpack the source and compile the mssql extension. Remember again to change the path accordingly if you installed freeTDS in another location.

cd php*/ext/mssql
phpize
./configure --with-mssql=/usr/local/freetds
make

The extension should now be compiled and ready to install. You will find the binary in the immediate sub directory modules.

3. Install the extension

Find out where PHP expects to find extension libraries. The simplest way to check this is through the command line.

php -i | grep extension_dir

Some distributions use different php.ini files for command line PHP and the PHP web server module. So it might be good to double check using the function phpinfo() in a php script loaded through the web server (using your browser is easiest).

<?php phpinfo();

Then search for extension_dir in the configuration information displayed. For example, on my laptop running Ubuntu, the path is /usr/lib/php5/20060613+lfs.

Continuing from above without having moved away from the directory where you compiled your mssql extension.

cp modules/mssql.so /usr/lib/php5/20060613+lfs/

The extension is in the right place and all you have to do now is to make sure PHP actually loads it. To do this add the extension somewhere in the php.ini file. For example in the section Dynamic Extensions to keep it somewhere logical.

extension=mssql.so

3. Restart the web server

If using the Apache 2 web server you would normally issue

/etc/init.d/apache2 restart

4. Post installation

Well that’s about it. You should have a workable mssql extension added to your PHP installation. You should be able to continue using your platforms chosen way of upgrading PHP without affecting the MS SQL Server support.

However, you might need to dig into the freetds.conf file. If you have followed my steps without altering the installation path you will find the freetds.conf file in /usr/local/freetds/etc/freetds.conf.

Sometimes it is difficult getting the connection work without adding it to the the freetds.conf. Especially since you may have to use different values for the tds version directive depending on the MS SQL Server version. Examples:

[logisticsServer]
host = ntmachine.localdomain
port = 1433
tds version = 7.0

[intranetServer]
host = 192.168.1.145
port = 1433
tds version = 4.2

Again. Check that you are using the correct freetds.conf file and that you are using the correct tds version! More information on this at freetds.org. This and the above mentioned “missing files” that PHP is looking for are the two most common pitfalls.

Good luck!

Tags: extension, freetds, mssql, PHP