Here’s the scenario: you are developing a web application using PHP on an Ubuntu server. You need to pull in data from an external database as part of the app. Unfortunately, the external database is Microsoft SQL Server. Accessing a PostgreSQL or MySQL database via PHP is very easy, but as I discovered, accessing MS SQL Server is less straightforward.
Microsoft provide a native PHP driver for SQL Server, which is available via direct download, or via Microsoft’s Web Platform Installer. Unfortunately, as you might guess, it’s only available for PHP on Windows. The source code is available, but the driver seems to rely on Windows components, so simply compiling it on Linux doesn’t seem to be an option. I’ve not heard of anyone successfully using the drivers with Linux, at least. Fear not though, all is not lost!
There is a free, open source driver for accessing SQL Server called FreeTDS, which works on Linux and a number of other platforms. When I tried to set it up with PHP I discovered that most of the documentation online is fairly out of date and involves a lot of arcane messing around with the command line, compiling source code, and so on. There are simpler ways, at least on Ubuntu, but no one seems to have explained how to set things up.
Before starting, you’ll need to have PHP working on your web server, and you’ll need to configure the SQL Server to accept TCP/IP connections from the Ubuntu system. I’ve tested the method described below with PHP on Lighttpd via fastcgi running on Ubuntu 10.04, accessing Microsoft SQL Server 2005 Express, but it should work for other webservers (e.g. Apache) and SQL Server editions. It should also work on a Debian system but I haven’t tested that either. Anyway, assuming you’ve got the prerequisites sorted, simply follow these instructions to access SQL Server databases from PHP on your Ubuntu server:
- Install the packages
freetds-bin, freetds-common, tdsodbc, odbcinst, php5-odbcand
unixodbc. This provides the libraries you need.
- Copy the contents of
/etc/odbcinst.ini. This registers the FreeTDS driver with the ODBC layer.
- Restart your webserver to load the ODBC module into PHP.
That’s it! You should now be up and running. Try testing the database connection with something like this:
$db = new PDO('odbc:Driver=FreeTDS; Server=hostname_or_ip; Port=port; Database=database_name; UID=username; PWD=password;');
die("Unable to open database.<br>Error message:<br><br>$exception.");
echo '<h1>Successfully connected!</h1>';
$query = 'SELECT * FROM table_name';
$statement = $db->prepare($query);
$result = $statement->fetchAll(PDO::FETCH_NUM);