Last updated: 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:
SELECT * FROM Table;
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
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:
$db = new \PDO('dblib:host=yoursqlserver.poweredby.kinamo.be;dbname=database', 'username', 'password');
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!