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!
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Comments
30 Responses to “Adding support for MS SQL Server to PHP in Linux”Trackbacks
Check out what others are saying about this post...-
[...] Linux zu kompilieren und einzubinden. Danne Lundqvist hat dazu in seinem Blog einen entsprechenden Artikel [...]
-
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. …
-
[...] 1/6/2010 Update Adding support for MS SQL Server to PHP in Linux [...]
-
[...] Lundqvist has a new post showing how to get MS SQL Server support to work with your PHP installation on linux with the help [...]
-
[...] Adding support for MS SQL Server to PHP in Linux [...]
-
[...] here: Adding support for MS SQL Server to PHP in Linux January 5th, 2010 | Tags: but-very, linux, little-on-how, nformation-on-how, not-very, php, [...]
-
[...] are a lot of tutorials out there on how to build the extension yourself on Mac or Linux machine (this site for example), but none seemed to work for [...]
-
[...] Adding support for MS SQL Server to PHP in Linux This entry was posted in Linux, Programming, Uncategorized, Web Designing by mehrdust. Bookmark the permalink. [...]
Danne,
I was using FreeTDS with PHP but had to stop due to its lack of support for encrypted connections to MS SQL Server. The firm responsible for this server suddently changed it to encrypted mode so I had to go back to PHP on a Windows server, plus their native MSSQL driver.
Does it support UTF-8 encoding?
In olden times, the TDS install would use uppercase where PHP needed lowercase or vice versa.
Not sure it that’s still true or not.
Also, if the MSSQL driver is not working, install the Sybase driver.
Microsoft bought the Sybase code of antiquity and added layer after layer after layer of the Next Bit Thing (Ole, Jet, etc) to it, just by wrapping yet another function call around the last failed not so big thing.
The core code still runs the same as the Sybase. The one time they issued a patch that broke it, they got so much grief that they are unlikely to repeat *that* again.
Anyway, long story short, Sybase driver works just fine, possibly slightly faster, as you bypass several layers of Microsoft cruft.
Nice article. I’ve come to similar solution when I was developing php application to connect to MSSQL database. My conclusions were posted on my blog. Altough text is in slovene maybe someone will be able to get some usufual information (use google translation).
I think you need to change –enable=msdblib to –enable-msdblib
Great job, thanks very much!
and yes -enable=msdblib should be –enable-msdblib
Yep – you’re right. I’ve fixed the typo in the post.
It’s –enable-msdblib
Nice article but is there any chance you can expand up on how to get the latest version of PHP etc in step 2. I’m a Linux virgin and I’m trying to get this setup for my project testing.
Looking through it I’m stumped on number 1 too! help!
Excellent instructions. I’d been racking my head about this for a few hours now.
Thanks a million for posting.
Great tutorial. Worked perfectly. Thank you
Thank you for the steps..
i have setup this in my one ubntu pc, its working but in another pc i am getting error unable to connect Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: 192.168.0.6
While i am able to connect mssql from command line using tsql command.
Hey Kuldip, did you figure your problem out?
Guess what? I am having the same problem as you…
I can connect with sqsh, but when I try to use the mssql function it gives the same error:
Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: 192.168.0.111
Am I missing something?
Thanks in advance.
Iam able to connect with
tsql -H servername -p 1433 -U sa
password:*****
but when i try
$db=mssql_connect(‘192.168.0.1:1433′,’sa’,'*******’);
or
$db=mssql_connect(‘192.168.0.1′,’sa’,'*******’);
or
$db=mssql_connect(’servername’,’sa’,'*******’);
i am getting the same error
Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server.
anyone who solved this to help? I will really appreciate.
I am going down the path of MSSQL + PHP to avoid replication of data push through the MySQL 3.5.1 driver (which seems to be pretty badass under Windows 2003 server for us). This way I can pull directly from MSSQL vs. relying on my stored procedures that add a crazy level of complexity to the equation.
Compiled my php configuration rather simply by adding the –with-mssql (which is a switch under the ./configure –help | grep “mssql” lookup). So, rather happy with this.
Stop the server, restarted and it seems to be at least loaded. Time to go back and see if it actually worked, but the exploration in your article seems to be lacking down some examples to those who compile from source. Maybe giving an example for those who compile that can add the switch on the config.nice options after you’ve already done a compile once before .. might be nice.
Otherwise good article thus far.
I’ve been using this method for a while, but when I set up a new server, with more recent Debian, Apache and PHP 5.2.6 versions, I had problems with WHERE-clauses in mssql_query SELECT statements.
After three months searching the problem still isn’t solved, but I read that in PHP 5.3 support for mssql extension is dropped completely.
Is there an alternative method?
This was very helpful. Thanks.
[...] Adding support for MS SQL Server to PHP in Linux [...]
great article. what versions of sql server have been successfully tested with freetds?
I’ve had nothing but problems using mssql, however using odbc setup Linux/PHP talks to MSSQL without issue. Here’s how I set mine up: http://members.cox.net/midian/howto/phpMSSQL.htm
I’m trying to use this guide to install the mssql extension but I’m stuck. I get the following errors when I run the ‘make’ command in the mssql directory:
/root/php-5.3.8/ext/mssql/php_mssql.h:68: error: redefinition of typedef ‘LPBYTE’
/usr/local/freetds/include/sqlfront.h:35: error: previous declaration of ‘LPBYTE’ was here
/root/php-5.3.8/ext/mssql/php_mssql.c: In function ‘php_mssql_do_connect’:
/root/php-5.3.8/ext/mssql/php_mssql.c:767: warning: cast from pointer to integer of different size
/root/php-5.3.8/ext/mssql/php_mssql.c: In function ‘php_mssql_get_column_content_without_type’:
/root/php-5.3.8/ext/mssql/php_mssql.c:1120: warning: passing argument 1 of ’spprintf’ from incompatible pointer type
make: *** [php_mssql.lo] Error 1
I was able to compile the mssql module with freetds 0.91 by doing the following:
Open php_mssql.h:
Comment out the line that says “typedef unsigned char *LPBYTE;”
Then try running “make” and see how it works!