Using PHP with FreeTDS to query an MS SQL Server

NOTE: There's a very nice and detailed FreeTDS Users Guide on installing, configuring and running FreeTDS provided on the FreeTDS Homepage.

This tutorial was written for a combination PHP 4.0.1pl2 and a pre-0.5.1 CVS version of FreeTDS. There are more recent versions of both PHP and FreeTDS - as far as I know, the steps in this tutorial should work for those, too.

Please send any comments, updates, suggestions, errata etc. to this page to, I would be glad to hear about them.

What is FreeTDS?

The native communications protocol of Sybase and Microsoft database servers is TDS (Tabular Data Stream) - for example, the MS SQL Server ODBC driver uses this protocol to communicate with the database server. FreeTDS is a free implementation of TDS, with the project's homepage being

Using the FreeTDS libraries, PHP's mssql_xxx() functions can be used to directly access an MS SQL Server or Sybase database server from a Linux machine.

If you use Windows as your PHP platform, you will not need FreeTDS. PHP 4.0.1pl2 for Windows already comes with out-of-the-box working mssql_xxx() functions. Read the annotations to the PHP manual section on Microsoft SQL Server functions to find out how to enable it.

Please note that FreeTDS is a project under development, and as such still has its fair share of bugs and problems. It should be stable enough for use in quite many production/application environments already, though. An alternative might be using the Sybase libraries from / (which I haven't tried).

Important: The following step-by-step instructions describe how I got PHP w/ FreeTDS to work for me. You might run into problems that I did not encounter (and vice versa, I could have experienced trouble where none might show up for you). A good resource for knowledge is the FreeTDS mailing list (see the FreeTDS Homepage Contact section on how to subscribe).

How to install FreeTDS for use with PHP

Start position

I assume that PHP has been already been installed & configured and runs fine. The following step-by-step howto describes how to add FreeTDS support to PHP.

Setup for the example installation (many other versions, especially newer ones, should work fine, too):

  SuSE Linux 6.3 (glibc2.1/libc6.1, Kernel 2.2.13 or was that 2.2.9?)
  Apache 1.3.9
  PHP 4.0.1pl2 (loaded as mod_php4 by the Apache)

Step-by-step installation

  1. Get the newest sources for PHP and FreeTDS
  2. Compile and install FreeTDS
  3. Set environment variables

    The SYBASE environment variable will need to be set as well as LD_LIBRARY_PATH extended.

    For a bash shell, this looks as follows:
    > SYBASE=/usr/local/freetds

  4. Compile and install PHP
  5. Restart Apache

    On a SuSE system, this will look like this:
    root@mymachine > rcapache restart

    Note: The environment of the Apache server needs the variables SYBASE and LD_LIBRARY_PATH set as above for FreeTDS to function. If you startup Apache automatically at system bootup you will have to do so in some appropriate place. On a SuSE distribution, for example, you could do so in /sbin/init.d/apache (which is, btw, also the script executed by the above Apache restart command).

You will now be able to use PHP's mssql_xxx() functions (which actually are just aliases to the sybase_xxx() functions, so those work as well) to access an MS SQL Server.

Accessing an MS SQL or Sybase Server

To access an MS SQL Server (or a Sybase server), you will have to enter its basic connection parameters in /usr/local/freetds/interfaces. For example, we shall assume a database server at IP listening on port 1433 (which is the default port used by MS SQL Servers, so if in doubt, use that one) which we will call MYSERVER:

        query tcp ether 1433
        master tcp ether 1433

Note: MS SQL Server 2000 seems to use port 1067, at least if there is an MS SQL Server 7 running simultaneously on the same machine. So, if you seem to be unable to connect to MS SQL Server 2000, you might want to check the port [snatched from the FreeTDS mailing list].

The following PHP code now can be used to query MYSERVER (also, a more complete example can be found in an annotation by from 16-Jul-2000 to the PHP Manual section on Microsoft SQL Server functions):

  // connect to database server
  $db_conn = mssql_connect("MYSERVER","<user>","<password>")
    or die( "<strong>ERROR: Connection to MYSERVER failed</strong>" );

  // select database - only if we want to query another database than the default one
  mssql_select_db( "mydatabase", $db_conn )
    or die( "<strong>ERROR: Selecting database failed</strong>" );

  // query the database
  $query_result = mssql_query( "SELECT * FROM foo", $db_conn )
    or die( "<strong>ERROR: Query failed</strong>" );

   * You can now access the result data by using the appropriate mssql_xxx() functions
   * on $query_result. See the PHP manual section on Microsoft SQL Server functions
   * for documentation on how to do this.

  mssql_free_result( $query_result ); // unnecessary in PHP 4

  mssql_close( $db_conn );            // unnecessary in PHP 4

You might also consider using some wrapper around the database access code (example MS SQL wrapper, see here for motivation and usage instructions.

Further Reading

Michael Peceny
Apr 27, 2002