Tags pour la question fréquent:
Debian PHP

Cet article a-t-il répondu à votre question?

Score de 0, basé sur 1 votes

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

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

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.

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

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.

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

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!