(Note this guide was originally published 2010-02-11 and has not been updated)
This article was developed using an Ubuntu 8.10 box, but since it uses mostly compiled from source applications, it should be easily portable to the distribution of your choice.
So, you want to offer some web services, but your company is standardized on MS-SQL for their backend? I can already hear the Linux die-hards yelling about Postgres and MySQL already, but fact of life is, a lot of companies use MS-SQL. But there’s no reason to not use a nice lightweight, fast, open-source front end for your web apps against this database.
Three major applications we’ll use to provide access in this guide are: FreeTDS, an interface application that allows direct SQL access, Apache 2.2 for web server duties, and PHP 5.2.6 for scripting/DB access/dynamic HTML generation.
First off, you’ll need to load up your Linux box with the regular tools used for building applications (gcc, etc). This can be done under Ubuntu by getting the ‘build-essential’ package.
Now, in your home directory, you’ll want to pull down the source packages for the applications we are going to build. You can pull down Apache and PHP source packages in Ubuntu with ‘apt-get source packagename‘. For FreeTDS, you can get the latest package from the FreeTDS website (link here). Use either FTP or wget to download the tar.gz file and extract it.
First off, lets build Apache. It’s a relatively simple configure, since we really only need the dynamic module loading capability compiled in. Switch the the source directory for Apache, and execute the following configure script:
./configure –prefix=/usr/local/apache2_2 –enable-so
You’ll see a bunch of text fly by. Congratulations, you’re compiling code! Once that is done, it will have created what is called the ‘makefile’. Guess which command you run next… yup.
make
More text flies by! Once it’s done, run ‘make install‘. This will put the compiled libraries in their spots.
Alright, so Apache is now installed under /usr/local/apache2_2.
Now lets build and install FreeTDS. This needs to be built before we can compile PHP.
Change to the extracted source directory of FreeTDS (you did unpack the tar.gz archive right?). Again, we’ll go thru the same steps as Apache for building.
./configure –prefix=/usr/local/freetds –disable-odbc
make
make install
In this setup, we’ve disabled building the ODBC driver for FreeTDS, as we are not using a Linux ODBC driver manager, so we don’t need it. We use FreeTDS as a direct interface to MS-SQL.
Now we can compile PHP. I used version 5.2.6 and we’ll be enabling some common features used by PHP scripts. Switch to the source directory for PHP and execute:
./configure –prefix=/usr/local/php5 –with-apxs2=/usr/local/apache2_2/bin/apxs –with-gd –with-mssql=/usr/local/freetds –enable-calendar
What this does is sets the build location to /usr/local/php, the ‘with-apxs2‘ is the apache module support for dynamic loading, ‘–with-gd‘ enables graphic support, ‘–with-mssql‘ enables mssql functions within PHP, and ‘–enable-calendar‘ builds the calendar manipulation features.
Note: the ‘–with-gd‘ option usually requires PNG support. You’ll need the development libraries for it. Under Ubunutu, you can get these with ‘apt-get install libpng12-dev‘.
Once you’ve got a successful configure, execute the make and make install commands.
Congratulations, you’re almost done.
Check the /usr/local/apache2_2/conf/httpd.conf and make sure there is a line in the LoadModule section as follows:
LoadModule php5_module modules/libphp5.so
This enables PHP5 support for Apache. You will need to restart Apache if you modify the httpd.conf (or recompile PHP for additional options later).
You’ll also need to edit the freetds.conf file to put in your SQL server settings. This is a dynamic file that is checked on each call, so you don’t need to recompile or restart anything if you make changes. The TDS version line value is generally set at 8 for MS-SQL (works on SQL 2000 and above). Check the FreeTDS site for the value mapping if you are unsure.
Note: Make sure your SQL server is listening on the port you assign (default is 1433). On SQL 2005 and above, you may need to turn off dynamic ports.
You can test accessing the SQL server with the ‘tsql‘ executable in the freetds/bin directory. This is basically a telnet test to the SQL server. Once you have tested this successfully, you should be able to use the PHP mssql_* commands in your scripts!
Now, one final thing. If you reboot the system, you’ll notice Apache doesn’t auto start. That’s because compiling this way didn’t add any startup scripts. You’ll need to create one to fit your distribution as necessary.
Hope this helps someone out there! I know it’s been extremely useful for me.