Successful Microsoft SQL Server Support for PHP on Linux

Posted in Databases, PHP and Zend Framework on Thursday, the 13th of May, 2010.

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.

Comments

Posted by Hakim on Sunday, the 15th of August, 2010.

Hi,

Thanks a lot for this very clear explanations.

Is this describe settings running in Production ? And if so, did you face any further issue ?

Thanks for your feedback,
Hakim

Posted by Simon Harris on Sunday, the 15th of August, 2010.

Hi Hakim -

Yeah, this is pretty much exactly what we have in production. No further issues at all so far, though in most cases we only do pretty tame things with SQL Server (namely logging) so your mileage may vary.

All the best,
Simon

Posted by Dave Taylor on Monday, the 23rd of August, 2010.

This is a great write up Simon. We are using the exact same setup in a production system.

My question is regarding handling SQL errors. It seems that when using PDO, the only error message I ever get back from the DB is "General error: 156 General SQL Server error:
Check messages from the SQL Server [156] (severity 15)". This is when catching exceptions on DB calls. I notice that in the freeTDS logs, I can see the actual error that came back from the DB. Are we missing something or is PDO masking these errors?

Posted by Simon Harris on Thursday, the 26th of August, 2010.

Hi Dave, thanks for the kind words.

I'm not sure I can help with the question, I'm afraid, though I did notice the behaviour and found it pretty frustrating. This post seems to be picking up a fair bit of search traffic now, so maybe someone who can shed more light on that will pass through :)

Posted by Jon on Monday, the 27th of September, 2010.

Hi Simon

Just noticed that two paragraphs from the end, you mentioned mysql_connect rather than mssql_connect. Just checking whether your readers are eagle-eyed? ;o)

Posted by Simon Harris on Monday, the 27th of September, 2010.

Ahem, yes it was a...test :) Still, all corrected now, thanks for the heads up.

Posted by Jonathan on Thursday, the 17th of February, 2011.

Exellent - just what I was looking for :) Haven't tried it yet, but just wondering if you use an SSL connection to the remote mssql server?

Posted by Simon Harris on Friday, the 18th of February, 2011.

Hi Jonathan, glad to help :)

That's a good question, and I actually don't recall the answer for certain. I worked on this quite some time ago, and I'm not with that company any more so I can't go digging around in the setup.

I don't remember any faffing about with shared keys, but I do suspect the connection was over a VPN, so it's possible that encryption was taken care of at a lower level.

Posted by Tash Pemhiwa on Sunday, the 27th of February, 2011.

Great article! I stumbled upon this article after a Google search when my curiosity about using MS SQL Server on Linux was aroused while reading about PDO. I initially found something stating that you needed dblib to get it running. A further Google "dblib MS SQL linux" brought me here. I've gotta say, Simon, your blog is awesome. Keep writing.

Posted by Maja on Tuesday, the 1st of March, 2011.

i have tested your going on and implemented freetds on my red hat linux server, but get always

"Call to undefined function mssql_connect() in /var/www/... on line 3"

here my php file

<?php
$con = mssql_connect('mybigserver', 'dbUSER', 'mypassword');
$db = mssql_select_db('myDB');
?>
how can i test, if freetds really is configured in php ??

cheers
Maja

Posted by Simon Harris on Tuesday, the 1st of March, 2011.

Tash -

Thanks for the kind words - that's a lovely comment to receive.

Maja -

Did you configure PHP using the --with-mssql option? I suspect not, because it looks like you don't have the MSSQL extension installed.

Posted by Jeremy Hicks on Wednesday, the 20th of July, 2011.

This simply will not work for me. No matter what I do, I always get a configure error that says /usr/local is not a FreeTDS installation directory. I've created the missing files that pdo-dblib is looking for but it doesn't do the trick.

Posted by Reeg on Monday, the 12th of December, 2011.

thank you very much Simon you guided me to the right path but i have noticed another search result that would help others if needed :
http://www.php.net/manual/en/mssql.setup.php

Posted by Travis Truax on Wednesday, the 4th of April, 2012.

I setup the MSSQL Native ODBC driver for Linux like Ariz blogged about, and it all went well until I tried to connect to something other than a default instance.

Using the common naming convention of server\instance gets you a long wait and a timeout.

Using the specific port for the instance and just circumventing the instance name string entirely does connect, but bizarrely to the default instance still.

Unless I'm blind (always possible), the MS docs for this driver don't even mention the existence of SQL Server instances or how to connect to them.

Travis-

Posted by Travis Truax on Monday, the 9th of April, 2012.

After trying a bunch of stuff I finally got it to work. Docs others posted show the port in its own entry in the DSN, but it sure doesn't work that way on my box.

I had to append the port to the end of the server entry in the ODBC DSN.

Server = host.domain.com\INSTANCE, port

Posted by sixthfore on Friday, the 24th of August, 2012.

Not sure how useful this would be to anyone, but a successful 'yum search freetds' yields this result:


Loaded plugins: dellsysid, fastestmirror, security
====================================================================== Matched: freetds =======================================================================
freetds.x86_64 : Implementation of the Sybase/Microsoft TDS (Tabular DataStream) protocol
freetds-devel.x86_64 : Header files, libraries and development documentation for freetds
freetds-doc.x86_64 : Development documentation for freetds
mnogosearch.x86_64 : Web indexing and search system for a small domain or intranet

Posted by Nishad Somanathan on Wednesday, the 10th of October, 2012.

Hi Sir,
thanks for the frretds installation steps.

During the installation, (in Xampp for linux)when i use the "./configure --prefix=/usr/local/freetds \
--with-apxs2=/opt/lampp/bin/apxs \
--with-zlib \
--enable-pdo \
--with-mssql=/usr/local/freetds \
--with-pdo-dblib=/usr/local/freetds"

i get the following error:

"configure: WARNING: unrecognized options: --with-apxs2, --with-zlib, --enable-pdo, --with-mssql, --with-pdo-dblib" is shown and I aw the MSSQL server is not accesible from my PHP(installed in centos)

Posted by zezinho on Monday, the 8th of September, 2014.

A big thank you, as of today this really helped me connecting with a Mageia Linux 4 server : all software was already there, but the /etc/freetds.ini file trick was hard to find.

Posted by Rex on Friday, the 29th of May, 2015.

We need to edit /path/to/lampp/etc/freetds.conf and add the entries as below to connect to SQL server and using LAMPP in linux.

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

Enter your comment: