The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 22, 2019, 05:24:20 PM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: LAMP Setup: When the M stands for MS SQL  (Read 3080 times)
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« on: October 08, 2007, 10:37:14 AM »

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 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:
Code:
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:
Code:
./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:
Code:
./configure \
        --enable-msdblib \
        --with-tdsver=8.0 \
        --enable-dbmfix \
        --with-gnu-ld \
        --enable-shared \
        --enable-static

chmod that file to executable like this:
Code:
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:
Code:
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 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:
Code:
[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 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 phpinfo() page. You'll be looking for this:
Code:
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:
Code:
/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 Server
Finally it is time. Connect to your server with the base PHP function mssql_connect:
Code:
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.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
« Last Edit: October 08, 2007, 10:39:20 AM by perkiset » Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
vsloathe
vim ftw!
Global Moderator
Lifer
*****
Offline Offline

Posts: 1669



View Profile
« Reply #1 on: October 09, 2007, 01:47:59 PM »

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

hai
ratthing
Journeyman
***
Offline Offline

Posts: 75


View Profile
« Reply #2 on: October 27, 2007, 10:56:42 AM »

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

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=
Logged
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #3 on: October 27, 2007, 11:01:26 AM »

I will leave the logic of how the highly paid sys admin became the build geek as an exercise for the reader.

LOL... square peg meets ________
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!