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