Last updated on 28 Sep 2023, 10:18:04.
Category: Cloud servers
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!
Were not all your questions answered?
Don't worry, we will be happy to help you via a support request!