Connexion avec Microsoft SQL Serveur à partir de PHP sur Debian via FreeTDS

Dernière mise à jour: 25/01/2016

Introduction

This article explains the steps required to connect from PHP on a Debian linux server to a Microsoft SQL Server database.

We will use FreeTDS and unixODBC for this purpose.

Installation of the necessary Debian packages with apt-get

The following Debian packages are needed:

freetds-bin - FreeTDS command-line utilities
freetds-common - configuration files for FreeTDS SQL client libraries
unixodbc - Basic ODBC tools
php5-sybase - Sybase / MS SQL Server module for php5

As you can see we require php5-sybase! This is no error :-)

Installation of the packages can easily be done through a single line command, with apt-get install

sudo apt-get install freetds-common freetds-bin unixodbc php5-sybase

After the installation, restart the webservice (apache2):

sudo service apache2 restart

Testing the FreeTDS connection to Microsoft SQL Server with tsql

Verifying if the installation of all components (freetds, unixodbc) was successful can be done with the tsql command:

tsql -H yourserverhostname -p 1433 -U username -P password -D database

If you get a connection prompt, the components were installed correctly. You can test a query with:

USE database
GO
SELECT * FROM Table;
GO
EXIT

Attention: if you select a nvarchar or ntext field you can get an error:  "Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier".
This is normal, it can be fixed by setting the freetds.conf (the FreeTDS configuration file) with a correct version parameter.

sudo vi /etc/freetds/freetds.conf

You can add a section to the config file for your own database server or instance:

# Kinamo SQL Server
[yoursqlserver]
  host = yoursqlserver.poweredby.kinamo.be
  port = 1433
  tds version = 8.0

By adding TDS version "8.0" you will remove the Unicode error.

Test the FreeTDS connection by using the server or instance name

You can now retry the tsql command, this time with the -S parameter instead of -H.
This creates a connection on server name or instance name instead of the complete FQDN host name, and FreeTDS will use the parameters you provided including the valid TDS version.

tsql -S yoursqlserver -U username -P password -D database

Testing a connection from PHP to Microsoft SQL Server

The final step is to test your connection from PHP. This is possible in several ways: through the PDO extensions, through mssql...

The example below shows a simple PDO connection:

<?php
 
$db = new \PDO('dblib:host=yoursqlserver.poweredby.kinamo.be;dbname=database', 'username', 'password');
return $db;

?>

Attention, if you get an error: PDOException "could not find driver" you may be connecting as PDO('mssql...); instead of PDO('dblib...);

It depends on your OS which driver you may need to use, on Debian this is dblib!