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!

Tagged with: , , ,
Posted in PHP
30 comments on “Adding support for MS SQL Server to PHP in Linux
  1. Marc Delisle says:

    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.

  2. pcdinh says:

    Does it support UTF-8 encoding?

  3. 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.

  4. Roky says:

    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).

  5. CH says:

    I think you need to change –enable=msdblib to –enable-msdblib

  6. Jay says:

    Great job, thanks very much!

    and yes -enable=msdblib should be –enable-msdblib

  7. Danne says:

    Yep – you’re right. I’ve fixed the typo in the post.

    It’s –enable-msdblib

  8. Alun Rowe says:

    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.

  9. Alun Rowe says:

    Looking through it I’m stumped on number 1 too! help!

  10. SG says:

    Excellent instructions. I’d been racking my head about this for a few hours now.

    Thanks a million for posting.

  11. Razvan says:

    Great tutorial. Worked perfectly. Thank you

  12. Kuldip says:

    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.

  13. krlos says:

    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.

  14. Erax kariz says:

    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.

  15. John from Buffalo says:

    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.

  16. KlaasV says:

    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?

  17. Mark Foster says:

    This was very helpful. Thanks.

  18. [...] Adding support for MS SQL Server to PHP in Linux [...]

  19. kitchen says:

    great article. what versions of sql server have been successfully tested with freetds?

  20. Charles Patterson says:

    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

  21. Kamran says:

    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

  22. Paul says:

    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!

  23. Matthew Oliveira says:

    Thanks very much for the information. Can anyone tell me what TCP/UDP ports need to be opened for a PHP script to use this method (through freetds) to query a SQL Server database on the same network as the server running the script?

    Thanks!
    Matt

  24. Dan says:

    I’ve been trying to get PHP to talk to mssql for a while now. The secret was in step one; configuring the FreeTDS with the mssql. Thanks a ton.

  25. deepak says:

    Hi all i have a doubt. I am not able to configure in the /php*/ext/mssql directory. The files inside that directory are ‘config.m4′ ‘config.w32′ ‘CREDITS ‘ ‘mssql.dsp ‘ ‘mssql_win32_howto.txt’ ‘php_mssql.c’ ‘ php_mssql.h’ . Am i missing something here, you said the “Unpack the source”. Do i need to copy the source php-5.4.3 directory again in to the mssql directory. Please help me out as i am in some kind of emergency to finish up this installation of php

  26. If some one desires to be updated with most recent technologies after that he must be pay a quick visit this web page and be up to date all the time.

    My website :: Sit4less Certified Coupon Code *sites.google.com*

  27. As the admin of this web page is working, no question
    very soon it will be well-known, due to its quality contents.

    Take a look at my site Cult Furniture discount Code

  28. Everything is very open with a very clear description
    of the issues. It was definitely informative. Your website is very useful.
    Thank you for sharing!

    My weblog: AS Roma Store Coupon Code – asromastorecouponcode.wordpress.com -

  29. Thhe combination would slmetimes knock the imbibers out for a
    couple of days. Often described aas “a large dog in the body of a small dog,
    ” it is similar to the Norwegian Buhund and related to modern Welshi Corgis as well as Shetland Sheepdogs.
    Find Indian astrology with horoscope and live astrological prediction ,
    aries horoscope, daily, monthly, yearly analysis off birth
    chart and individual prediction of all planet with gemstone.

10 Pings/Trackbacks for "Adding support for MS SQL Server to PHP in Linux"
  1. [...] Linux zu kompilieren und einzubinden. Danne Lundqvist hat dazu in seinem Blog einen entsprechenden Artikel [...]

  2. abcphp.com says:

    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. …

  3. [...] 1/6/2010 Update Adding support for MS SQL Server to PHP in Linux [...]

  4. [...] Lundqvist has a new post showing how to get MS SQL Server support to work with your PHP installation on linux with the help [...]

  5. [...] Adding support for MS SQL Server to PHP in Linux [...]

  6. [...] 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, [...]

  7. [...] 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 [...]

  8. [...] 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. [...]

  9. [...] Винрарная статья по установке [...]

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>