Accessing Microsoft SQL Server from PHP on Ubuntu using PDO, ODBC and FreeTDS

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.

Available PHP/MS-SQL drivers

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.

Getting FreeTDS working

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:

  1. Install the packages freetds-bin, freetds-common, tdsodbc, odbcinst, php5-odbc and unixodbc. This provides the libraries you need.
  2. Copy the contents of /usr/share/doc/freetds-common/examples/odbcinst.ini into /etc/odbcinst.ini. This registers the FreeTDS driver with the ODBC layer.
  3. 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:

<?php
try
{
  $db = new PDO('odbc:Driver=FreeTDS; Server=hostname_or_ip; Port=port; Database=database_name; UID=username; PWD=password;');
}
catch(PDOException $exception)
{
  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);
$statement->execute();
$result = $statement->fetchAll(PDO::FETCH_NUM);
?>

Comments

Anonymous - Fri, 03/09/2010 - 14:36

Permalink

This works for the simple query you have in your post but if you do anything more complicated odbc will segfault apache.

For instance:

<?php
try
{
$db = new PDO('odbc:Driver=FreeTDS; Server=hostname_or_ip; Port=port; Database=database_name; UID=username; PWD=password;');
}
catch(PDOException $exception)
{
die("Unable to open database.
Error message:

$exception.");
}
echo 'Successfully connected!';
$query = 'SELECT * FROM table_name WHERE table_name.COLUMN = ?';
$statement = $db->prepare($query);
$statement->bindValue(1, 'Value', PDO::PARAM_STR);
$statement->execute();
$result = $statement->fetchAll(PDO::FETCH_NUM);
?>

/var/log/apache/error.log:
[Fri Sep 03 07:30:03 2010] [notice] child pid 4007 exit signal Segmentation fault (11)

Better to use pdo_dblib. Just make sure to set a DateTime compatible date format in /etc/freetds/locales.conf

That's interesting, I didn't come across this problem during my testing. When I get the chance I'll try the code I'm working on in Apache and see if it segfaults there. Searching the web for FreeTDS/Apache segfault problems brings up a number of mentions a number of similar problems, but no conclusive information about causes/fixes, at least on a quick check. I'll post back if I find out any more details. I avoided using pdo_dblib because the official PHP site lists it as being experimental, but if it's more reliable than using FreeTDS/ODBC then I guess it is a more sensible choice for future projects!

Thanks for the link, looks like some useful information!

Incidentally, I carried out a bit more testing and discovered that the FreeTDS/ODBC method outlined in the original article I wrote doesn't return results for some of the queries in the web app I'm working on (i.e. no rows are found, even though there should be some). The exact same query does return results when using pdo_dblib, so it appears that there are other problems than the bindParam() segfault with the use of FreeTDS via ODBC.

Anonymous - Thu, 01/12/2011 - 15:16

Permalink

I've been following in your footsteps here. Although I've been around the houses and wasted lots of time compiling a pdo module (mssql.so) which I couldn't quite get to work.
I found your method didn't quite work for me either, and I had to create an ODBC.INI file with Drivers and DSNs in it before I could get a connection.
Will now be trying it out in anger to see if things fall over as warned about above!
Cheers, anyway...
Alex

Good luck! If you get the chance, please post back with your findings, I'm sure other people would appreciate any further information.

Since writing this article I've pretty much given up on ODBC and switched to dblib instead, or sqlsrv when running PHP on Windows. While it's a bit worrying that dblib is officially marked experimental, so far I haven't run into any problems with it. I guess whether or not you can live with the risk depends on how critical the site you're setting up is.

Anonymous - Wed, 21/03/2012 - 22:47

Permalink

I've tried a number of options in connecting from Ubuntu 11.10 to SQL Server 2008 R2 in PHP without sucess. However, if you follow the steps you have listed above, it works great.

Thank you!

Anonymous - Wed, 04/04/2012 - 11:11

Permalink

Thanks a 1.9 million time.
Really saves my day.

---regards,
Ghulma

Anonymous - Tue, 28/05/2013 - 21:01

Permalink

I am using Linux Mint 14 Nadia on my workstation, so, odbcinst.ini was in /usr/share/tdsodbc/

And then I just did this:

$ sudo apt-get install freetds-bin freetds-common tdsodbc odbcinst php5-odbc unixodbc
$ sudo mv /etc/odbcinst.ini /etc/odbcinst.ini.bak
$ sudo find / -name odbcinst.ini
$ sudo cp /usr/share/tdsodbc/odbcinst.ini /etc/
$ sudo service apache2 restart

Thank you very much Pete Donnell.

Anonymous - Mon, 15/07/2013 - 16:52

Permalink

Thank you very much for the snippet. After following the instructions, I was successfully able to connect to the MSSQL DB.

Thank you,

Petey Rock

Anonymous - Fri, 23/08/2013 - 00:03

Permalink

I want to bro kiss you man!! lol many thanks!

Anonymous - Thu, 24/10/2013 - 11:12

Permalink

this really helped me
<?php
try
{
$db = new PDO('odbc:Driver=FreeTDS; Server=hostname_or_ip; Port=port; Database=database_name; UID=username; PWD=password;');
}
catch(PDOException $exception)
{
die("Unable to open database.Error message:$exception.");
}
echo 'Successfully connected!';
$query = 'SELECT * FROM table_name WHERE table_name.COLUMN = ?';
$statement = $db->prepare($query);
$statement->bindValue(1, 'Value', PDO::PARAM_STR);
$statement->execute();
$result = $statement->fetchAll(PDO::FETCH_NUM);
?>

Anonymous - Thu, 07/11/2013 - 19:30

Permalink

I am using Ubuntu 13.04, so, odbcinst.ini was in /usr/share/tdsodbc/

And then I just did this:

$ sudo apt-get install freetds-bin freetds-common tdsodbc odbcinst php5-odbc unixodbc
$ sudo mv /etc/odbcinst.ini /etc/odbcinst.ini.bak
$ sudo find / -name odbcinst.ini
$ sudo cp /usr/share/tdsodbc/odbcinst.ini /etc/
$ sudo service apache2 restart

and I am getting the following errors

Unable to open database.
Error message:

exception 'PDOException' with message 'SQLSTATE[08001] SQLDriverConnect: 0 [unixODBC][FreeTDS][SQL Server]Unable to connect to data source' in /var/www/clients/client2/web6/web/sqltest1.php:4 Stack trace: #0 /var/www/clients/client2/web6/web/sqltest1.php(4): PDO->__construct('odbc:Driver=Fre...') #1 {main}.

Not sure what went bad but not sure what to do next
Thanks

I know it's an old issue, but posting in case anyone else stumbles across this. I had a similar issue running Ubuntu 14.04. Same error message. I also couldn't find odbcinst.ini. However, I tried the following (different PDO parameters) and things worked just fine (without any modification of ini files)
$db = new PDO("dblib:host=xxx.xxx.xxx.xxx;dbname=MYDBNAME", "USER", "PASS");

Thanks for the great article and and for important discussions by all of you.

I have tried with

$db = new PDO("dblib:host=243.234.234.232;dbname=dbname", "user", "pswd");

and got

Unable to open database.
Error message:

exception 'PDOException' with message 'could not find driver' in /var/www/html/ll/test/test.php:7 Stack trace: #0 /var/www/html/ll/test/test.php(7): PDO->__construct('dblib:host=198....', 'hipcask', 'logic9944') #1 {main}.

When I use

$db = new PDO('odbc:Driver=FreeTDS; Server=63.45.32.34; Port=1433; Database=database; UID=user; PWD=pswd;');

I get

Unable to open database.
Error message:

exception 'PDOException' with message 'SQLSTATE[IM002] SQLDriverConnect: 0 [unixODBC][Driver Manager]Data source name not found, and no default driver specified' in /var/www/html/ll/test/test.php:5 Stack trace: #0 /var/www/html/ll/test/test.php(5): PDO->__construct('odbc:Driver=Fre...') #1 {main}.

Any help would be greatly appreciated.

Anonymous - Fri, 08/11/2013 - 09:07

Permalink

my connection works perfectly but iam using column id of the table to get the value actually i want to use column name. when i tried column name itis not working

please help

yebowhatsay - Mon, 06/10/2014 - 14:43

Permalink

needed to install php5-sybase too for this to work

apt-get install php5-sybase

Add new comment

CAPTCHA