Building the PHP MS SQL Server extension from source on Ubuntu 8.10

Yesterday I wrote about how I got an enhanced Sybase CT extension for PHP built on Ubuntu 8.10 from source files and from Red Hat RPM archives. Today I wanted to write about installing the SQL Server extension from source on Ubuntu 8.10.

I have written about this once before but it was for a Red Hat environment. Seeing as we are converting all of our system at work to Ubuntu I had to do the same thing for the Ubuntu platform. And wouldn’t you know it, Debian and Red Hat are vastly different from each other.

Aside from the stupidity that is the Ubuntu package manager’s rendition of the SQL Server extension for PHP the fact remained for me that I had to be able to integrate database communication from LAMP to a SQL Server on Windows as well to a Sybase server on Unix. The Sybase bit was taken care of in yesterday’s write up. But how do we get PHP to talk to SQL Server on Ubuntu 8.10?

Throw out the notion that you can use the php-sybase package from the package manager. It munges stuff up pretty badly and at the same time does things to the Sybase extension that make it unusable for my needs. Not to mention that if their is a Sybase extension already installed then it really pukes because the php-sybase extension that installs the SQL Server functions actually installs the mssql_* functions then maps the sybase_* functions to them, which makes PHP squawk since those functions are already defined.

So the only way to make this happen is to build the SQL Server extension from source.

Building and installing the mssql extension from source

The follow steps will guide you through how I got the mssql extension built and installed on Ubuntu 8.10:

  • Build the FreeTDS library
    FreeTDS is a free, open source library of clients that allow Linux machines to talk to SQL servers and Sybase servers. Download the latest stable version of the FreeTDS library (as of this writing it was 0.82) and unpack the source to a directory on your machine. Once the source is unpacked, change to the directory where the source is located and configure and make the library:
    $ ./configure --prefix=/usr/local/freetds --enable-msdblib
    $ make
    $ sudo make install

    DO NOT MAKE CLEAN AT THIS POINT.

    Before you do anything else you must make sure that you have copied over all the necessary files from FreeTDS. “Wait, didn’t the installation routine do that?” you might ask. No, it doesn’t, because of a change made on the part of the FreeTDS developers. There are two files that are needed in order to build the mssql extension. Those files are among the make files and must be copied over to the freetds install directory.

    From inside the source directory of freetds, where you built it from, enter:
    $ sudo cp include/tds.h /usr/local/freetds/include
    $ sudo cp src/tds/.libs/libtds.a /usr/local/freetds/lib

  • Get the PHP source code
    Change to a directory where you wouldn’t mind having the entirety of the PHP source stashed. From the command line type:
    $ apt-get source php5
  • Copy the mssql extension files to a directory you can build from
    My preference is to leave source alone and work from copies. So I always copy the files I need to a different location and build from the copies. To that end, I created a directory at ~/SourceCode/php-mssql and copied the following files from the original PHP source directory:
    $ cp ext/mssql/config.m4 ~/SourceCode/php-mssql
    $ cp ext/mssql/php_mssql.c ~/SourceCode/php-mssql
    $ cp ext/mssql/php_mssql.h ~/SourceCode/php-mssql
  • Make and install the extension
    Make sure you have the php5-dev package installed on your system so that you can build PHP extensions from source. From inside the directory where your mssql extension source code is, at the command line, enter:
    $ phpize
    $ ./configure --with-mssql=/usr/local/freetds
    $ make
    $ make install

    Find out where your extensions directory is on your machine and quickly check it to make sure there is a php_mssql.so file living in it. On my machine the extension directory is /usr/lib/php5/20060613+lfs/. Yours may be different.

  • Configure PHP to use the new extension we just made
    Now we need to tell PHP to use the new extension we just built. To do that we need to create an ini file for the extension and put it inside of the extensions directory where PHP can find it. On my machine PHP looks for ini files to parse in /etc/php5/conf.d/ so naturally that is where I am going to go to to tell PHP to use this extension.

    $ cd /etc/php5/conf.d/

    Now we need to create an ini file and put into a directive to load the extension. You can use whatever editor you like. I prefer to use vim:
    $ sudo vim mssql.ini

    Inside this file place the following two lines:
    ; Enable the mssql extension
    extension=mssql.so

  • Configure your environment to load a much needed environment variable whenever the machine starts
    Much like the Sybase extension we did yesterday the mssql extension needs an environment variable in order to function properly. Again, this one caused me fits for a long time in Ubuntu. To be sure you can use the SQL Server extension from both the CLI and the web server you will need to add an environment variable to both the /etc/profile startup script AND the web servers environment variable setting script.

    $ sudo vim /etc/profile

    At the end of the file add:
    export FREETDSCONF=/etc/freetds/freetds.conf

    Now add this same entry into your web server’s environment variables. I am using apache and assuming you are to. If not, consult your web server’s documentation for how to do this:
    $ sudo vim /etc/apache2/envvars

    Now add these entries to the end of the file:
    export FREETDSCONF=/etc/freetds/freetds.conf

  • Configure FreeTDS
    In order for FreeTDS to communicate properly with the SQL server a DSN of sorts needs to be created. To make a DSN you need to edit the freetds.conf file:
    $ sudo vim /etc/freetds/freetds.conf

    Go to the end of the file and add the following lines (MAKE SURE TO INDENT ALL OF THE CONFIG VALUES FOR THE SERVERNAME):
    ;--- Custom MSSQL server name ---
    ; THIS CAN BE ANY NAMED SERVER REFERENCE YOU WANT TO CREATE
    [SERVERNAME]
      ; This is the host name of the MSSQL server
      host=HOSTNAME
      ; This is the port number to use
      port=PORTNUMBER
      ; This is the TDS version to use for anything over Server 2000
      tds version=8.0

    Now we need to add the freetds library to the load library stack.
    $ sudo vi /etc/ld.so.conf

    Go to the end of the file and add the following line:
    /usr/local/freetds/lib

  • Restart your web server
    Like everything that involves a change to the PHP environment or configuration on your machine, restart the web server. I am assuming this is being built upon an apache server. If not, you will need to know how to stop and start your web server or, at the very least, know how to reboot your machine:
    $ sudo apache2ctl stop
    $ sudo apache2ctl start

    Run a PHP info page or CLI call to see if it is loaded:
    $ php -m

    You should now see mssql.

Again, I hope this was helpful to you. If it was, please leave a comment and let me know. It something went haywire for you, leave me a comment, too. Systems being what they are, it is never unheard of for two almost identical systems to have vastly different experiences with building software from source.

32 Thoughts on “Building the PHP MS SQL Server extension from source on Ubuntu 8.10

  1. Thank you so much for this detailed info. I have been hunting for this info for a long time and you have just set me right.

  2. Pingback: Zend Framework in Action » Robert Gonzalez: Building the PHP MS SQL Server extension from source on Ubuntu 8.10

  3. C LaRose on August 19, 2010 at 9:14 am said:

    In the final section, you must indent the host, port and tds version lines. I spent hours and hours on debian system trying to figure out why I could not connect. Tried indenting those lines just to keep a clean freetds.conf and it fixed it.

  4. Seriously? I had no idea indents were necessary. Although mine are indented as well.

    I’ve added that to the instructions with credit given to you. Thanks for catching that and posting about it C LaRose!

  5. Nosnetrom on September 23, 2010 at 2:11 pm said:

    This is a very well written guide! I was able to follow everything, but I am still unable to connect to our SQL Server 2005 using isql or via a php Web page. In isql, I get the error message “Unable to connect to data source/Adaptive Server connection failed/Unexpected EOF from the server”. Any ideas on what I should do next?

  6. Is your FREETDSCONF environment var defined and pointing to the correct location of the conf file for FreeTDS? I’d start there, and make sure all permissions are correct.

  7. Nosnetrom on October 8, 2010 at 1:52 pm said:

    All is well. I CHMODed the files, but most importantly I discovered that I was using the wrong PHP functions. :-S The queries to MS SQL Server seem to be running a little slow, but they are indeed running. You da man! :-)

  8. Glad I could help you. :)

  9. Awesome! You are The man of the year with this post.

    Ubuntu 10 here! Thanks a bunch amigo!

  10. i got the mssql module loaded… however i have no success connecting to the mssql database across the network. the tmp/freetds.log spits out absolute jibberish…. funny thing is i can make out like field names that i recognize… so its reading it… however its not displaying it on my webpage. I get a null response. I’m just ready to drink a beer I think… my brain hurts. Been at this for a few days, and I feel like im just chasing my tail.

    here is my freetds.conf file if anyone wants to try to decipher jibberish.
    http://www.sendspace.com/file/a2dt3s

  11. Looking over your log file it appears that you are actually connecting. I’m not sure that I am the best resource to diagnose that though. What I would recommend is sending a message to the FreeTDS mailing list. The developers of FreeTDS have done a wonderful job of helping the community pretty quickly and may be able to help you better than I can when it comes to that.

    The mailing list address is freetds@lists.ibiblio.org

    They were having some problems with uptime recently but it looks like http://freetds.org is back online now too.

  12. Kenneth on April 26, 2011 at 9:23 am said:

    Thanks! great article…now I jsut to find out why it still isn’t working (code is known good, need to find out about my PHP and Ubuntu 10.10 setup…)

  13. I’ve never messed with it on 10.10, but there may be an issue with newer PHP core code. When I built the Sybase extension for PHP 5 I had to change a bunch of stuff in the c code in order to make it work because certain defines had changed between versions. Just a thought.

  14. gcupstid on August 25, 2011 at 12:39 pm said:

    I am so close but this is frustrating me to no end… I have freetds working. I can connect to my mssql database using the tsql command from freetds. I have the php source and have followed the steps above but I always get the following error when trying to make php. (./configure part works fine)

    In file included from /home/gcupstid/SourceCode/php-mssql/php_mssql.c:33:0:
    /home/gcupstid/SourceCode/php-mssql/php_mssql.h:68:24: error: redefinition of typedef ‘LPBYTE’
    /opt/freetds/include/sqlfront.h:35:22: note: previous declaration of ‘LPBYTE’ was here
    /home/gcupstid/SourceCode/php-mssql/php_mssql.c:181:2: error: ‘PHP_FE_END’ undeclared here (not in a function)
    /home/gcupstid/SourceCode/php-mssql/php_mssql.c: In function ‘php_mssql_get_column_content_without_type’:
    /home/gcupstid/SourceCode/php-mssql/php_mssql.c:1120:4: warning: passing argument 1 of ‘spprintf’ from incompatible pointer type
    /usr/include/php5/main/spprintf.h:40:12: note: expected ‘char **’ but argument is of type ‘unsigned char **’
    make: *** [php_mssql.lo] Error 1

    I am using freetds .92 and the latest php 5.3.8. I also tried with php 5.3.6. Any ideas would be super helpful. :D

  15. I’d be willing to bet that some of the constants defined in the PHP source are now different than previous builds. I ran into this when trying to compile a PHP 4 extension against PHP 5. INT became a LONG and in some places the keyword UNSIGNED had been added when they were not expected.

    My recommendation to you would be to check the make script, see what files are being included (header files) and see if you can spot where the inconsistencies are. I’m sure there is a faster way to do this, but this is kinda how I handled it when I ran into a similar situation back in the day.

  16. The lastest freetds was released 8/18/2011, which is just before I’ve seen posts about the “error: redefinition of typedef ‘LPBYTE’” errors. I was able to build php-mssql off of a earlier version of freetds:

    ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/old/0.82/freetds-0.82.tar.gz

  17. The LPBYTE problem looks to be an include order bug in the source. It is defined in both php_mssql.h and sqlfront.h, as the error message says. The sqlfront.h file looks to see if the constant PHP_MSSQL_H is defined, and skips the redefinition if so, to avoid the conflict. If php_mssql.h is included first, then the constant will be set and all will work according to plan. However, if sqlfront.h is included first, then the constant will not be set, and LPBYTE will be (illegally) defined in two places.

    While the text is different, the definition is equivalent (pointer to an unsigned char), so the quick and dirty solution is just to comment out that line in php_mssql.h and build.

  18. Thanks for your blog.
    I had a problem in SuSE 11.4, when compiling showed the error:
    “spprintf.h:40:12: note: expected char but argument is of type unsigned char”
    I solved the problem: I open php_mssql.h file, and comment out the line that says “typedef unsigned char *LPBYTE;”

    Thanks again

  19. Pingback: Кузнецов Александр » Подключение к MSSQL-серверу из PHP на Linux

  20. Min-Ying on February 27, 2012 at 1:42 am said:

    Thank you so much!!
    This detail procedure help me a lot.
    :D

  21. Honestly, this article was so well written I actually felt like I knew what I was doing… now if I can just get my DBA to give me valid user credentials…

    Thanks!

  22. Glad I was able to help with getting the extension installed. As for the DBA… perhaps a small bribe or death threat?

  23. Niks on May 15, 2012 at 12:20 am said:

    Nicely written , but still i have some problems in connecting “Unable to connect to data source/Adaptive Server connection failed/Unexpected EOF from the server”. As mentioned above by a user, which file should i chmod?
    Please help soon.

  24. Generally the files in question would be your FreeTDS conf file, your apache ENVVARS file and your PHP extension file. Make sure they are a minimum of read and execute, and that that the apache and php files are owned by root. Pretty sure the FreeTDS conf file needs to be owned by root as well, but I’m not certain. It’s been a while since I’ve been on a *nix system talking to SQL Server. Apologies.

  25. Niks on May 17, 2012 at 2:56 am said:

    HI Robert
    Thanks for your support.
    After debugging in log file to came to know my freetds is using 5.0 version which is not supported for MSSQL.
    In config file i have changed the tds default version to 7.0, but don’t know why it is using 5.0 version.
    Tried connecting using tsql in terminal it is working.
    It will be great if you can help me in it.

  26. Niks on May 17, 2012 at 2:58 am said:

    HI Robert
    Thanks for your support.
    After debugging in log file i came to know my freetds is using 5.0 version which is not supported for MSSQL.
    In config file i have changed the tds default version to 7.0, but don’t know why it is using 5.0 version always.
    Tried connecting using tsql in terminal by providing tds version 7.0 it is working fine.
    It will be great if you can help me to change the version.

  27. Does your configuration file look like the one I posted? If you’re still having trouble with it I’d highly recommend you hit up the FreeTDS mailing list. I posted the information in an earlier comment, but for convenience I’ll post it again.

    The mailing list address is freetds@lists.ibiblio.org

    Their site is http://www.freetds.org and they have a good amount of resources.

  28. Niks on May 22, 2012 at 5:36 am said:

    Thanks a lot Robert.
    I helped me.

  29. yinbiao on July 25, 2012 at 8:56 am said:

    ERROR:/usr/local/php/include/php/main/spprintf.h:40: note: expected 鈥榗har **鈥but argument is of type 鈥榰nsigned char **鈥
    make: *** [php_mssql.lo] Error 1

    Please help!!!
    i come from china

  30. yinbiao:
    I’m pretty sure this is a difference in versions of PHP. You’re gonna have to go into your PHP source where spprintf is throwing this error to see what other file might be included that contains the defs for what it is compiling. I ran into this when building the Sybase extension for various data type (LONG, CHAR)… basically whatever the compiler expects, you need to make match. Either make the source code that is showing char show an unsigned char or remove the unsigned part of the source. Either way should work, just keep in mind that since you are editing the source of PHP that your installation might not work exactly as expected.

  31. Pingback: Querying a MSSQL database from an Ubuntu server: | dingdangdoo.com

  32. Fantastic mssql for freetds walkthrough. Worked like a charm on debian squeeze too. Module appeared instantly after a full rebuild, but essential to make sure on squeeze dev tools are installed.

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>

Post Navigation