This weekened I had to setup another box that needed to talk to a MS SQL instance and as usual, since I don't do this often, I went to the web to try to find the details. Virtually all of the sites I went to are a few years old, and none of them got right to it... and it seems like everyone leaves out just a the little niggle that fishes you up. I've done it before so I knew where to look, but had forgotten a couple little details.
So here is the soup-to-nuts version of setting up a MS SQL connection on a Linux Box. But first, a word from our sponsors:
Spam for the spiders:
Setting up Linux with MS SQL
Using SQL Server with Linux Unix OS-X
FreeTDS MS SQL SQL Server and Linux
FREETDSCONF freetds.conf interfaces interface configuration
Configuring FreeTDS for MS SQL on Linux
And now back to our regularly scheduled program...
There are 3 essential steps when setting up PHP to talk to MS SQL:
* Acquire, configure and build FreeTDS
* Create the interface description record and test
* Recompile PHP with MSSQL support
Step 1: FreeTDSFreeTDS is a library written for the express purpose of talking to MSSQL from *n[i|u]x. The base site is here:
http://www.freetds.org, but you'll want to get the most recent stable release, which can be downloaded here:
ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz Once you've gotten it, you'll need to un-tar it, config make and install (which I'll do in a moment).
But first, a quick rant: I really try to do everything in as standard a way as possible, so that I'll know that (I) was the one that did something on a machine and so that I can follow my tracks months or years later. To this end, I always create a directory /usr/local/
install which is where I bring all of my downloaded tarballs. I unpack them there, which creates a directory for <the package that I am downloading> and then move the tarball I downloaded into /usr/local/install/tarballs. So I very often have an install directory that looks something like this:
drwxr-xr-x 8 perk perk 4096 Oct 8 09:01 freetds-0.64
drwxr-xr-x 12 perk perk 4096 Oct 7 12:57 httpd-2.2.6
drwxr-xr-x 40 perk perk 4096 Jul 4 06:49 mysql-5.0.45
drwxr-xr-x 18 perk perk 4096 Oct 7 23:39 php-5.2.4
drwxr-xr-x 2 perk perk 4096 Oct 7 14:11 tarballs
As I said, we need to configure the FreeTDS package before we compile it. Here again I have a standardized procedure. I will always create a configure.p file which is executable and includes the options for the compile. This makes it so that I can quickly go back and see how I configured something on another box. For example, here is a configure.p file for PHP on one of my LAMP boxes:
./configure \
--with-apxs2=/usr/local/apache2/bin/apxs \
--with-mysql=/usr/local/mysql \
--with-mssql=/usr/local \
--with-gd \
--with-openssl \
--with-mcrypt \
--with-mhash \
--with-pear \
--disable-cgi \
--with-gd \
--with-jpeg-dir \
--with-zlib-dir \
--with-freetype-dir \
--with-curl=/usr/local/include/curl/ \
--enable-soap \
--enable-sockets \
--enable-sysvsem \
--enable-memcache
With all of that in mind, create a configure.[your initials] file in the freetds directory that was created when you unpacked the FreeTDS tarball. It should look like this:
./configure \
--enable-msdblib \
--with-tdsver=8.0 \
--enable-dbmfix \
--with-gnu-ld \
--enable-shared \
--enable-static
chmod that file to executable like this:
chmod 755 configure.p
then execute it. It will grind around for a bit and then stop. Then type "make" and it will grind around for longer and then type "make install" and it will complete the installation on your machine.
To test whether the install was successful, you'll need access to a MS SQL server ie., can you ping one from the machine you just installed? The first way to test this is to telnet to the box. do this: telnet [address of MS SQL box] 1433 - if you connect you're good to go. (There won't be any big hurrah or messages, if it connects that's good enough). Next, use tsql to test the FreeTDS connection:
tsql -S [address of mssql box] -U [valid user name] -P [valid password]
If you get a prompt 1> then you're in! If not, go grab a beer, come back and walk these steps again. Make sure you the configure/make/make install process went without any major complaints or errors.
Step 2: Interface DescriptionIf you google FreeTDS, you'll see discussion of both an interfaces and freetds.conf file. Interfaces is a now-defunct but still workable file from legacy versions of FreeTDS - freetds.conf is the way to go - and you must only use one of these files, not both. If the installation went correctly, then you should have a file called freetds.conf in /usr/local/etc. This is the default place that FreeTDS will look for this config file. If you need to, you can change this with a configuration directive - check the website for details.
There are several examples of configurations in that file, commented out by semi-colons. I needed a SQL Server 2K connection, so I added this config to the bottom of the file:
[penrad]
host = server-01
port = 1433
tds version = 8.0
IMPORTANT: Note that I had already added server-01 to the /etc/hosts file on this box. You may simply put a hard address here also. Also, note that tsd version 8.0 is for SQL server 2K and above. For versions earlier than that you'll want to try 7.0 or even 4.2.
Step 3: Configuring PHPYou'll need to recompile PHP with the --with-mssql flag. I use --with-mssql=/usr/local because that tells the compiler where the FreeTDS stuff is. So, assuming you have a configure.[your initials] like me, you'll ./configure.p, then make then make install. Provided there's no errors, that's it! (See the above example of a PHP configuration to see it in action).
Now we want to see if PHP has the MSSQL functions available. From the shell, you can type "php -i" and it will spit back a text version of the phpinfo() page. You'll be looking for this:
mssql
MSSQL Support => enabled
Active Persistent Links => 0
Active Links => 0
Library version => FreeTDS
Directive => Local Value => Master Value
mssql.allow_persistent => On => On
mssql.batchsize => 0 => 0
mssql.charset => no value => no value
mssql.compatability_mode => Off => Off
mssql.connect_timeout => 5 => 5
mssql.datetimeconvert => On => On
mssql.max_links => Unlimited => Unlimited
mssql.max_persistent => Unlimited => Unlimited
mssql.max_procs => Unlimited => Unlimited
mssql.min_error_severity => 10 => 10
mssql.min_message_severity => 10 => 10
mssql.secure_connection => Off => Off
mssql.textlimit => Server default => Server default
mssql.textsize => Server default => Server default
mssql.timeout => 60 => 60
The configuration values might be different, but if you have a block something equivalent to this then the core PHP module has been updated. Next, you'll want to bump your apache to make sure that it is using the NEW php instance and not the old one. The best way to do this is:
/usr/local/apache2/bin/apachectl -k graceful
which will make all instances slip away gracefully and be replaced by new ones with no hiccup to end users.
Now, you should be able to write a quick script to show phpinfo() via a webpage and you should see an MSSQL block in the middle of the phpinfo() response.
Connecting to the MS SQL ServerFinally it is time. Connect to your server with the base PHP function mssql_connect:
mssql_connect('servername', 'username', 'password')
THIS IS IMPORTANT: the "Servername" parameter must be what you called it int the freetds.conf file... so in my example, I'd have put "penrad" in that parameter. If everything is going fine, then you can run a simple script with that function and ... nothing will happen. That's good. You'll know it's a problem if you get a
Cant connect to server warning.
From there, you'll want to use the mssql function that come with PHP and can be seen here:
http://us2.php.net/manual/en/ref.mssql.phpYou can use ODBC as well, but that adds another layer and I don't like it. You may also use the PEAR DB class to talk to the database, but in this case I wanted to demonstrate as vanilla as possible.
Good luck!
/p
