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: FreeTDS
FreeTDS 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

mac

 hine 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:


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:


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:


chmod that file to executable like this:

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

mac

 hine.

To test whether the install was successful, you'll need access to a MS SQL server ie., can you ping one from the

mac

 hine you just installed? The first way to test this is to te

lnet

  to the box. do this: te

lnet

  [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:


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 Description
If 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:

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

PHP

 
You'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

php

 info() page. You'll be looking for this:

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:

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

php

 info() via a webpage and you should see an MSSQL block in the middle of the

php

 info() response.

Connecting to the MS SQL Server
Finally it is time. Connect to your server with the base

PHP

  function mssql_connect:

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.

php

 

You 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

I will definitely come back to this in the coming months.

A few months back, I had to get

PHP

  and MS SQL to play nice. It was not a fun job but man was I happy when it finally worked.

I am reminded of the last $job where I had to build

Apache

 +

PHP

  on a RedHat box to replicate a pimped-out (read crazy developer edition) of

Apache

 +

PHP

  on Gentoo, right down to the directory structure. 

I will leave the logic of how the highly paid sys admin became the build geek as an exercise for the reader.  It was, however, more engaging than dealing with my so-called "team lead."  I'll stop before this becomes a long-winded rant about the endless dysfunction of IT orgs.

=RT=

LOL... square peg meets ________