picture of Alberto
In this article, I will explain how it is possible to connect and query an MS SQL server (running under a Windows operating system) from php installed on a unix box. The same problem, in the case of php installed under Windows is not covered here because it is well explained in php FAQ; sadly I cannot say the same thing for the unix case. In fact, there is a multitude of incorrect or outdated sources of information which will cause new people trying to solve this problem to drop it after some bad tries. This article can also be of help to people who don't want to use php as a client.

Two paths

There are fundamentally two techniques to remotely access from unix to an MS SQL server. Both technics are supported under php. It is possible to use a library that implements the TDS (Tabular Data Stream) protocol, which is the application-level protocol used by Sybase databases and by MS SQL. It is also possible to establish a way of communication by using ODBC (Open Database Connectivity). This is a database independent communication API which allows applications to communicate, via standard-based function calls to a back end database management system.

TDS

There are two implementations of the TDS protocol. The first implementation is Sybase Open Client Library, which Sybase released as publicly available in binary form. (You must register to Sybase site, for free). The second is FreeTDS, an opensource project to document and implement the Tabular Data Stream protocol.
We are interested in the 4.2 , 5.0 and 7.0 versions of tds ; freetds covers all of them, while sybase ct library supports up to 5.0. (This is the default one and the one used by the latest sybase dbms).
Microsoft officially supports tds 4.2 and tds7.0 (in SQL Server 7 only). TDS 7.0 supports some new features introduced in SQL Server 7 as Unicode data types.
In MS SQL versions prior to 7, tds5.0 was unofficially supported. However, since SQL Server version 7, the client requests in tds5.0 are refused by the server, but Microsoft has reopened access to tds5.0 in SQL Server 7, Service Pack 2. I'm explaining all of this because I noticed that sybase ct library didn't succeed to connect to MS SQL 7 through protocol tds4.2, while freetds with tds4.2 behaved perfectly. So, if you have MS SQL 7 and want to use sybase libraries (and you experience the same problem that happened to me) you must apply the patch and connect through protocol tds5.0 ! MS SQL 7 Service Pack 2 can be downloaded from http://www.microsoft.com/sql/downloads/sp2.htm
In order to connect to MS SQL from a remote machine through tds, it is necessary to activate tcp/ip connectivity in MS SQL, using "Server Network Utility".

Sybase Client Library

Now, forget about the outdated libraries or the ones linked in php faq, unless you still have a libc5 system. (If you have a libc5 system you can download the libraries at http://www.php.net/extra).
The client library is distributed with Sybase Adaptive Server Enterprise, which can be downloaded for free from http://linux.sybase.com. Currently, Sybase Adaptive Server Enterprise for Linux version 11.9.2 is offered as a free release for development; support can be purchased separately. Download at least the file named sybase-common-11.9.2-1.i386.rpm, which contains the only files you need to compile php. It is just 5 Mb of download.
Next, unpack the files. I will assume the installation directory is /opt/sybase. Go to the php source directory and do a ./configure adding "--with-sybase-ct=/opt/sybase" to the usual configure options.
After having compiled and installed php, you need to add the location of the libraries to your library path. You can copy the shared libraries to /usr/local/lib; add the path to /etc/ld.so.conf; or simplyadd the line below to apachectl:
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/sybase/lib
The next thing you need to do is to declare the environment variable SYBASE to point to the sybase installation directory. If Sybase Open Client Library is loaded without this variable correctly set, it segfaults (apache segfaults) !! So you can add to apachectl:
export SYBASE=/opt/sybase
Now you must edit the "interfaces" file, which must be located in the installation dir. The interfaces file contains information about remote servers. A typical configuration to access a Windows machine with MS SQL installed with ip 192.168.1.4 and listening on port 1433 connected to the local lan (eth0) is this:
	relaxsql
	query tcp eth0 192.168.1.4 1433
	master tcp eth0 192.168.1.4 1433
The name "relaxsql" is the hostname which will be used by php functions to establish a connection to MS SQL on that machine. Be sure to set the correct permissions to the sybase installation directory and to this file; apache must have read access to this file.
Here is a sample php code to test everything. It connects as user "utente" with password "parola" and makes two queries to the default database for that user, as defined in MS SQL Server.

<?php

$numero
mssql_connect('relaxsql' 'utente' 'parola' );
echo (
"ConnectID: $numero<br>\n");

$result=mssql_query('select * from tavoladiprova',$numero );
while (
$row=mssql_fetch_array($result)) {
    
$counter++;
    
$c1=$row['colonna1'];
    
$c2=$row['colonna2'];
    echo (
"$counter c1: $c1 c2: $c2<br>\n");
}

$result=mssql_query('INSERT into tavoladiprova values('kooooo','ooooook')',$numero);

mssql_close($numero);

?>
The default TDS protocol version used by sybase client library is TDS 5.0. In order to convince php to use TDS4.2 I've made a quick and dirty patch to the php code. This patch is against php-4.0.1pl2; you can use it if you want TDS 4.2.

FREETDS

FreeTDS can be downloaded from www.freetds.org. I suggest downloading the latest snapshot, which gave me better results and seems to have less bugs. (At the time of this article, the latest snapshot is dated Jul 6 2000). The direct link is:
ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/current/freetds-current.tgz. Alternatively, you can download the latest CVS. The information needed to download is in the FAQ on the main site.
As previously stated, freeTDS supports TDS 7.0 and will let you take advantage of its features. Therefore, it could be a better choice if you need to connect to MS SQL Server 7. Also, freetds is an open source project under constant development, with all the advantages this implies. Personally, it is the solution I prefer among all.
Unpack the files and do a configure:
./configure --with-tdsver=4.2 --enable-msdblib --enable-dbmfix --with-gnu-ld --enable-shared --enable-static
(The tdsver options simply sets the default protocol, this can be changed anytime at runtime)
The default installation directory is /usr/local/freetds.
After you have patched a clean php-4.0.1pl2 package ("patch -p0 < patch.freetds" from the parent dir of the source package), you can build php with the configure option "--with-sybase-ct=/usr/local/freetds"
As for the sybase library, the environment variable SYBASE pointing to the freetds installation directory needs to be declared. If the variable isn't declared, apache starts correctly and only the php sybase/mssql functions won't work. You can do this in apachectl or directly in your php code. It is also possible to set the tds protocol version from an environment variable and to set a debug logfile (warning: it grows rapidly):

<?php

//putenv('TDSDUMP=/tmp/tds.log');
putenv('SYBASE=/usr/local/freetds');
putenv('TDSVER=70');

?>
Finally, you must correctly edit the file /usr/local/freetds/interfaces, as explained in the previous section regarding sybase libraries. One interesting difference is that the third field of a configuration row can be used to specify a default protocol for that specific host:
	myserver
	query tcp tds4.2 127.0.0.1 1433
	master tcp tds4.2 127.0.0.1 1433
You can test your installation/configuration with the simple php code reported above.

ODBC

ODBC introduces an overhead to connection time because the layers of communication are more. The client machine needs to have an ODBC driver manager which is directly interrogated by the application. The driver manager establishes a connection with a remote ODBC server by using a specific ODBC driver resident on the client machine; the ODBC server on the server machine translates ODBC calls and speaks to the database. The driver manager and the driver itself are independent. Therefore, it is to the driver manager that we must link php if we want odbc connectivity.
ODBC has the advantage of allowing applications to be written which theoretically are independent from the dbms. If you need to change the dbms, you just have to change the driver, not the code.
ODBC is a programming API and doesn't cover a client/server protocol. Therefore, if you want to communicate from a client machine (unix) to a remote machine (windows) using odbc you need a driver which handles the client/server connection. In other words, a bridged driver which resides both on the client machine and on the server machine. The client side of the driver will keep the requests from the local odbc driver manager, and will handle the connection to the server side driver. Depending on its implementation, the server side driver could speak directly to the database or use a local odbc driver.
An alternative implementation to establish a client/server connection is that the local ODBC driver will translate ODBC calls in to the language of the destination database. This will connect to the dbms directly. (This is the case of Inline's driver which you will read about later).
Here are listed the packages that allow an odbc solution. It is strange that php FAQ report only the openlink's driver..
ODBC driver managers for linux:
- iODBC (www.iodbc.org) is an opensource odbc driver manager and SDK mantained by Openlink Software (www.openlinksw.com) which also has a similar odbc driver manager distributed only in binary form.
- unixODBC (www.unixodbc.org) The unixODBC Project goals are to develop and promote unixODBC to be the definitive standard for ODBC on the Linux platform. It is an opensource project. This project is supported by Easysoft (www.easysoft.com)

Each of these driver managers are supported under php. You must choose one odbc driver manager and build php linking against its libraries:

To link against iodbc:--with-iodbc=
openlink:--with-openlink=
unixodbc:--with-unixodbc=
Warning: I had a problem building php as a Dynamic Shared Object for apache (--with-apxs option) and linking it to a driver manager. This occurs if you build apache with DSO option. Apache doesn't build with libpthreads, while driver managers do this by default. This conflict leads to a segmentation fault when apache starts. The solution is to rebuild apache with libpthreads (rm config.cache and do "LDFLAGS=lpthreads ./configure --prefix=/www --with-apxs etc..") or build the driver manager without libpthreads ("./configure --enable-threads=no etc.."). Thanks to Nick Gorham of Easysoft for the help.
The driver manager reads a configuration file called DSN (usually /usr/local/etc/odbc.ini) which lists Data Sources. This is a combination of several options as ipaddress, tcp port, default db, user pass, but the most important is the path to the specific odbc driver which the connection relies upon.

ODBC drivers for linux which connect to MS SQL:

Openlink Universal Data Access Drivers Multi-Tier Edition
This is a commericial bridged solution; the software is split in pieces. On the client side (php+linux), you need to have installed php with openlink's driver manager (l3kozzzz.taz) and the odbc driver (l3brzzzz.taz). On the server machine (the one with ms sql) you must install a "request broker" and a "database agent". The request broker will accept tcp connections and will forward the requests to the database agent which will query directly into the SQLServer database without going through the native SQLServer driver. This will speed things up a little. However, an ODBC bridged solutions is constituted of more layers of communication. There is also the disadvantage in installing and configuring part of the software on the Windows machine, which is not always desiderable. Finally the most important thing is that this is a commercial solution! The binaries are downloadable with a free, not expiring license, but with a limit of 10 concurrent connections and two different concurrent client hosts.
Easysoft ODBC-ODBC Bridge 2000
This is another commercial bridged solution. The features are somewhat similar to that of Openlink. It is suggested to use this driver with unixODBC as driver manager. The software can be downloaded for free, with a not expiring license, with a limit of only 1 connection !
Inline TDS ODBC driver
Inline TDS ODBC driver is developed by Inline Internet Systems (www.inline.net) and can be downloaded from http://library.freeodbc.org download page. This odbc driver is different from the others because it communicates directly with the dbms engine, and is not a bridge of any sort.
This is a faster and lighter solution compared to the other two, and it is totally free. The driver is currently released only in binary form, but Inline has the intention to release the source too. The freetds library will be updated then.
I've tested the driver with unixODBC and it works perfectly, it even lets you choose what tds version to use in the DSN file. Installation instructions are covered in a text distributed with the driver.

An alternative partially-ODBC solution:

Finally, I should mention another software which will make you able to access a remote MS SQL server from php: ODBC Socket Server by Team FXML downloadable from http://odbc.linuxave.net. This solution is not totally odbc because it doesn't require installation of any odbc driver or driver manager on the machine running php.
ODBC Socket Server is constituted of server-side software installed under Windows NT (or Win98, but it is not considered the optimal operating environment) and client-side software for COM, C++, Perl, Python and PHP.
The server-side software is a Windows NT service which uses an XML protocol to transfer data from the Windows NT ODBC Socket Service to TCP/IP clients. It opens a tcp/ip socket and listens for incoming connections on a specific port. The queries and the results will be exchanged using the XML protocol. The client-side software in the case of PHP is a very small php class to connect to the socket server, which basically defines a function ExecSQL() to establish a connection, send an SQL query and get the result. However, you will need to parse the output result, which is XML encoded. To parse the output result, you could use the PHP XML Parser module included in the latest versions of PHP. ODBC Socket Server is an open source project licensed under the GPL. The software is well documented and the installation very easy, including example code.

The End...

Several solutions have been introduced. The ones implementing the tds protocol have been shown in more detail. In fact, these solutions are faster and easier to set up. Also, all ODBC solutions are considered well documented, once you know the simple things explained here. ODBC offers some advantages as code portability, but it shouldn't be the first choice for someone who has to solve this problem rapidly and has no experience of odbc (especially under unix).
--Alberto