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:
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.
Simon [ADMIN]
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
Dave Taylor
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?
Simon [ADMIN]
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 :)
Jon
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)
Simon [ADMIN]
Ahem, yes it was a...test :) Still, all corrected now, thanks for the heads up.
Jonathan
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?
Simon [ADMIN]
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.
Tash Pemhiwa
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.
Maja
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
<?phphow can i test, if freetds really is configured in php ??
$con = mssql_connect('mybigserver', 'dbUSER', 'mypassword');
$db = mssql_select_db('myDB');
?>
cheers
Maja
Simon [ADMIN]
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.
Jeremy Hicks
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.
Reeg
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
Travis Truax
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-
Travis Truax
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
sixthfore
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
Nishad Somanathan
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)
zezinho
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.
Rex
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
Hakim
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