(c)2004 Skippy, Romanian Web Developers, http://rowd.org/

1 Introduction

You may wonder why connecting to Microsoft SQL Server would be any different (or more complicated) than connecting to any other database supported by PHP.

First of all, in order to have MSSQL support in PHP you need to have said support compiled in. This requires the FreeTDS library to also be installed on the system, which in turn may require the UnixODBC package to be present. As you can see, it's not something a default PHP installation is likely to have.

Second, the manner of connecting to MSSQL is slightly different from other usual databases, such as Postgres or MySQL. For the latter, the programmer uses *_connect commands, which will contain all the credentials needed by the connection. With MSSQL the data needed for the connection is defined at system level, is allocated a code name and the mssql_connect() call will use that code name.

As you can see, we're dealing with installing and configuring three packages for this purpose. Many users, even if used to Linux and PHP, will lose time reading the appropriate documentation before doing this. This article is meant to be a quick and dirty step-by-step guide.

Note: technically, you don't need UnixODBC in order for MSSQL support to work. It's possible to rely on FreeTDS alone. But using UnixODBC is a very good idea, since it allows for an ODBC abstraction layer which can later be used to switch from MSSQL to any other database with ODBC support without changing any PHP code. According to reports, UnixODBC does not induce significant overhead even under heavy load. Please see the "Unified ODBC functions" in the PHP manual to see how you can make use of the ODBC abstraction layer in your PHP code.

2 Getting the packages

2.1 PHP

Obviously, the first requirement is a recent source package for PHP. You can always get the latest at www.php.net.

If you don't want to compile PHP from source but wish to use a binary package for your platform/distribution, you need to make sure that the package in question has MSSQL support included. Please read the testing section to find out how to determine if said support is active.

If the binary package has no MSSQL support you will still need to compile PHP. It's very likely you're going to have to do this.

2.2 FreeTDS

FreeTDS is a library used for mediating access to MSSQL servers for Linux applications. It's a good thing to have PHP use this library, because it is mantained by a third-party team, thus reducing effort for PHP developers.

Get FreeTDS here: www.freetds.org. Make sure you get the latest source version.

You can probably obtain FreeTDS as a binary package from your distribution's vendor channels. If you do this you can skip the "install FreeTDS" section and go directly to configuring it.

2.3 UnixODBC

UnixODBC, as the name suggests, offers ODBC connectivity for Linux applications. FreeTDS can use this for its ODBC needs. Please read the note in the introduction again if you're not clear on why you should use UnixODBC.

You can get UnixODBC here: www.unixodbc.org. Please make sure you get the latest source version.

You can probably obtain UnixODBC as a binary package from your distribution's vendor channels. If you do this you can skip the "install UnixODBC" section and go directly to configuring it.

3 Installation

I will present the package installation in reverse order, since PHP needs FreeTDS, which in turn needs UnixODBC, to be already installed.

I'm going to assume you're used to compiling applications from source under Linux. Sorry if you're not, but this article is not meant to elaborate in that direction.

3.1 UnixODBC

Unpack the source, enter the resulted directory and issue the usual configuration and compilation commands (configure, make, make install) or whatever is appropriate for your system.

The configure command should look like this:

./configure \
--prefix=/opt/unixodbc \
--sysconfdir=/etc

Explanations:

• --prefix: determine the location where the compiled binaries will be installed. It's up to you to determine an appropriate place.
• --sysconfdir: determine the place where the configuration files will be placed. It's recommended to use /etc here.

After installation remember to integrate the binary package with the rest of the system if needed, by adding executables to $PATH, libraries to /etc/ld.so.conf followed by running ldconfig, and finally adding manual pages to /etc/man.config.

3.2 FreeTDS

Installing FreeTDS from source is very similar to UnixODBC. Therefore I will only present the relevant configure command as well as the explanations:

./configure \
--prefix=/opt/freetds \
--sysconfdir=/etc \
--with-unixodbc=/opt/unixodbc \
--enable-msdblib \
--with-tdsver=8.0

Explanations:

• --prefix: determine the location where the compiled binaries will be installed. It's up to you to determine an appropriate place.
• --sysconfdir: determine the place where the configuration files will be placed. It's recommended to use /etc here.
• --with-unixodbc: indicate the root of the place where you installed UnixODBC. This is needed so that FreeTDS can find the source headers and libraries from UnixODBC so it can link to them.
• --enable-msdblib: enable MSSQL support.
• --with-tdsver=8.0: determine the library version. At the time of writing, 8.0 was the latest; it allows you to connect to all MSSQL versions up to and including SQL Server 2000.

It's rather redundant to include the last two options because they're most likely the default anyway. It good however to make sure. You can also examine other options before the configuration, using hints provided by ./configure --help.

3.3 PHP

Activation of MSSQL support in PHP is done using the following configure option: --with-mssql=/opt/freetds. Remember to replace the FreeTDS location with the one appropriate for your setup.

I will not elaborate on compiling PHP. It can be an elaborate issue. If you've done this before than the option mentioned above is enough. If not then I'm afraid that I do not intend to turn this into a PHP compilation article. I can offer a hint nevertheless: examine the phpinfo() or php -i output for your currently installed PHP and you'll see the configure options used before. They can be a good starting point.

4 Configuration

4.1 Evironment variables

FreeTDS+UnixODBC+PHP need certain environment variables defined in order to work well together:

export SYBASE=/opt/freetds
export TDSVER=8.0
export FREETDSCONF=/etc/freetds.conf

Probably the best place to put the above is /etc/profile.

4.2 FreeTDS

Edit /etc/freetds.conf (create it if it doesn't exist) and add the following:

[MsSqlServer]
host = MS.SQL.SERVER.IP
port = 1433
tds version = 8.0
try server login = yes

This is the place where you define the parameters for the MSSQL server. As you can see, you need to assign a name to it (MsSqlServer), specify an address and port as well as other parameters needed by FreeTDS.

Remember the name you've assigned to the server! You will need it later!

4.3 UnixODBC

UnixODBC needs a driver and a data source defined.

4.3.1 Driver configuration

Edit /etc/tds/tds.driver.template (create it and the directories if they don't exist) and add the following:

[TDS]
Description = Free TDS
Driver      = /opt/freetds/lib/libtdsodbc.so
FileUsage   = 2

Remember to replace the driver location with the one appropriate for your system.

In order to activate this driver run the following as root:

odbcinst -i -d -f /etc/tds/tds.driver.template

4.3.2 Data source configuration

Edit /etc/tds/tds.datasource.template (create it and the directories if they don't exist) and add the following:

[MyConnection]
Driver        = TDS
Description   = Some MSSQL database
Trace         = No
Servername    = MsSqlServer
Database      = DBNAME
UID           = USERNAME

You'll have to enter the name of the server chosen before (MsSqlServer), the name of the database you want to connect to (DBNAME), as well as an MSSQL user (USERNAME) which is allowed to connect to that server and database.

Please remember the name you've chosen for the connection (MyConnection) since you'll need it later!

In order to activate this data source run the following as root:

odbcinst -i -s -f /etc/tds/tds.datasource.template

4.4 PHP

There are various configuration directives for MSSQL which can be placed in php.ini. Please see the "Microsoft SQL Server functions" section in the PHP manual for further details.

5 Testing

5.1 FreeTDS and UnixODBC

You can test the FreeTDS+UnixODBC installation from the Linux console using the following command:

tsql -S MS.SQL.SERVER.IP -U USERNAME

The numeric address and the username should be the ones you've used previously in the configuration files. If all works well you should be asked for a password to access the MSSQL server, followed by an SQL prompt for that server. To exit from the prompt press Control+D.

If there's any problem, you should see a self-explanatory error message.

5.2 PHP

5.2.1 Checking for MSSQL support

There are several methods you can use to check whether PHP has MSSQL support:

1. Find the PHP binary (php if you use the CLI version, or libphp4.so if you use the Apache module). Issue the following command: ldd libphp4.so. Check that the output has libfreetds.so listed.
2. Examine the phpinfo() output. You should see --with-mssql mentioned among the configuration options. Somewhere in the output you should see a section dedicated to MSSQL.

5.2.2 Connecting to MSSQL with PHP

Code such as the following can be used to test an actual connection to the MSSQL server from within PHP. The code connects to the server, reads a table and displays the contents.

$ident=mssql_connect('MsSqlServer','USERNAME');
mssql_select_db('DBNAME');
$result=mssql_query('select top 20 * from TableName');
echo '<TABLE border=1>';
while ($row=mssql_fetch_row($result)) {  
  echo '<TR>';
  for ($i=0;$i<count($row);$i++) echo '<TD>'.$row[$i];
}
echo '</TABLE>';

Remember to replace the emphasized portions with your own data! If all goes well you should see a page of data extracted from the server database. From this moment on you have MSSQL support at your convenience.

Homework: see the "Unified ODBC functions" section in the PHP manual to find out how to create a database abstraction layer via ODBC. It's worth it.