Connecting PHP on Linux to MSSQL on Windows

So last week I completed a task that involved setting up our PHP installation (which happens to be on a Red Hat Application Stack) to be able to talk to a MS SQL Server living on a Windows Server 2008 box while keeping my ability for PHP to talk to a Sybase Server living on a Solaris machine. Oh yeah, the Sybase extensions also needs to handle multiple result sets.

About 18 months ago I was in need of being able to talk to Sybase from PHP in a way that allowed Sybase to return multiple result sets and have PHP be able to use them.

This was not a huge problem, I thought, since just about all the beefy database servers can return multiple results and PHP can talk to just about any database server.

Boy, was I wrong.

After developing several high level headaches over the course of a few weeks trying to get multiple result sets to work, we found a solution that worked. Mostly.

It is a PHP4 hack to a PHP Sybase CT extensions that someone put together that allows the multiple result returns from Sybase to be captured. Unfortunately it also totally redefines the mssql_* family of functions at the same time. And I didn’t find out about that until after we had built the MSSQL extension and tried to run Sybase on the same PHP installation.

Anyway, if there is anyone else in the same boat as me, here is what I did to, first, get PHP to talk to MSSQL then to get PHP to talk to Sybase using multiple result sets. These instructions are for a Linux environment (RHAS, Fedora Core 6 and Fedora 7) using both PHP 5.1.6 AND 5.2.4.


Instructions:

  • Step 1: FreeTDS
    1. Download FreeTDS
    2. Unpack the tarball
    3. $]cd freetds-VERSION (where version is the FreeTDS version number)
    4. $]./configure --prefix=/usr/local/freetds --enable-msdblib
    5. $]make
    6. $]su root
    7. $]make install
    8. $]make clean
  • Step 2: SQL Server PHP extension
    1. Download the source for the right version of PHP (http://php.net/downloads)
    2. Unpack the tarball
    3. $]cd php-VERSION/ext (where VERSION is the PHP version number)
    4. Copy the mssql/ directory to someplace where you can compile it from
    5. CD to that directory
    6. $]phpize (this requires the php-devel package)
    7. $]./configure --with-mssql=/usr/local/freetds
    8. $]make
    9. $]su root
    10. $]make install
    11. $]make clean
  • Step 3: FreeTDS configuration
    1. $]vi /etc/freetds.conf (any editor available can used for this)
    2. Go to the end of the file
    3. Add the following lines
      ;--- 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
    4. Save the file and exit
    5. $]vi /etc/ld.so.conf (Again, any editor should be able to do this)
    6. Go to the end of the file
    7. Add the following line:
      /usr/local/freetds/lib
    8. Save the file and exit
  • Step 4: Enabling MSSQL PHP extension
    1. $]cd /etc/php.d
    2. If there is no file named mssql.ini create one
    3. Edit the file and enter the following lines if not already there
      ; Enable mssql extension module
      extension=mssql.so
    4. Save the file and exit
  • Step 5: Apache (This should be done any time PHP changes)
    1. $]/etc/rc.d/init.d/httpd stop
    2. $]/etc/rc.d/init.d/httpd start
    3. NOTE: $]apachectl -k graceful might work as well, though httpd start offers a status flag
  • Step 6: Test a file at the CLI and at the Web Server

If anyone wants the steps to setup the hacked version of the Sybase CT extension to handle multiple result sets, comment. I can post the files and instructions if anyone wants them.

About Robert Gonzalez

I am a geek. Actually, a professional geek as of September 2006, when I became a full time professional web developer. I am a husband, father, all around great guy and I am cute and cuddly. And I like pizza.

5 Thoughts on “Connecting PHP on Linux to MSSQL on Windows

  1. Pingback: One man’s voice » Blog Archive » Building the PHP MS SQL Server extension from source on Ubuntu 8.10

  2. Janet on March 19, 2009 at 8:17 am said:

    Thanks for detailed steps. I am not having success with my steps and wondered if you might have an idea on where my problem lies.

    Using: RH Linux release 4, Oracle 10g2, unixODBC-2.2.14, freetds-0.82 to connect to MS Sqlserver

    Did same steps as you except:
    ./configure –with-unixodbc=/opt/unixODBC-2.2.14 –prefix=/usr/local/freetds –enable-msdblib

    tsql connection fails whether I use freetds.conf or bypass with same error.

    ” tsql -S mssqltds -UORA_SPS”
    Msg 18456, Level 14, State 1, Server USDATLPC64MSD03, Line 1
    Login failed for user ‘ORA_SPS’.
    Msg 20002, Level 9, State -1, Server OpenClient, Line -1
    Adaptive Server connection failed
    There was a problem connecting to the server

    [root@atl1ld02 freetds-0.82]# tsql -H130.140.XXX.XXX -p1433 -UORA_SPS -PXXXXXXX
    locale is “en_US.UTF-8″
    locale charset is “UTF-8″
    Msg 20017, Level 9, State -1, Server OpenClient, Line -1
    Unexpected EOF from the server
    Msg 20002, Level 9, State -1, Server OpenClient, Line -1
    Adaptive Server connection failed
    There was a problem connecting to the server

    If I use TDSDUMP=freetds.log, I can see it connects but then is cancelled (log details below)
    iconv.c:516:tds_iconv_info_init: converting “UTF-8″->”UCS-2LE”
    iconv.c:516:tds_iconv_info_init: converting “ISO-8859-1″->”UCS-2LE”
    net.c:210:Connecting to 130.140.112.165 port 1433 (TDS version 8.0)
    net.c:264:tds_open_socket: connect(2) returned “Operation now in progress”
    net.c:303:tds_open_socket() succeeded
    util.c:162:Changed query state from DEAD to IDLE
    login.c:735:quietly sending TDS 7+ login packet
    token.c:312:tds_process_login_tokens()

    token.c:316:looking for login token, got aa(ERROR)
    token.c:108:tds_process_default_tokens() marker is aa(ERROR)
    token.c:2451:tds_process_msg() reading message from server
    token.c:2516:tds_process_msg() calling client msg handler
    token.c:2529:tds_process_msg() returning TDS_SUCCEED
    token.c:316:looking for login token, got fd(DONE)
    token.c:108:tds_process_default_tokens() marker is fd(DONE)
    token.c:2201:tds_process_end: more_results = 0
    was_cancelled = 0
    error = 1
    done_count_valid = 0
    token.c:2217:tds_process_end() state set to TDS_IDLE
    token.c:2232: rows_affected = 0
    token.c:393:leaving tds_process_login_tokens() returning 0
    util.c:162:Changed query state from IDLE to DEAD
    util.c:334:tdserror(0×537120, 0x5382f0, 20002, 0)
    util.c:368:tdserror: client library returned TDS_INT_CANCEL(2)
    util.c:389:tdserror: returning TDS_INT_CANCEL(2)
    mem.c:563:tds_free_all_results()

    /usr/local/etc/odbc.ini includes:
    [mssqltds]
    Driver = /usr/local/lib/libtdsodbc.so
    Description = TDS SQL Server ODBC driver
    Server = 130.140.XXX.XXX
    Port = 1433
    User = ORA_SPS
    Password = XXXXXX

    I can telnet 130.140.XXX.XXX 1433

    and I have installed Easysoft ODBC and can connect using isql (to same server using uid/passwd) with easysoft driver. However, i can not get my dblink to work with this driver, and i saw on many newsgroup freetds worked, so i am trying freetds for odbc driver. Any tips would be appreciated. Thanks.

  3. Hi Janet,

    Thanks for the comment. It is appreciated.

    Have you tried the instructions I wrote up in this article using FreeTDS 0.82? FreeTDS 0.82, I have found, is different than previous versions and required a few additional steps to get it working properly for me.

    If those instructions don’t work for you perhaps the FreeTDS mailing list can help. The list is pretty active and it is reviewed pretty much every day.

    I hope I was able to help at least a little bit.

  4. Pingback: Building the PHP MS SQL Server extension from source on Ubuntu 8.10 | coolbf

  5. Thank you so much for this. I kept getting an error even when having the TDS Version set to “auto”. Once I changed it to 8.0 I was able to connect.

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