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