sql server

How to connect PHP to Microsoft SQL Server on Linux Debian

Here is how to get PHP 5.6 on Linux (specifically Debian) talking to a Microsoft SQL Server database:

1. Install FreeTDS and the PHP MSSQL extension

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

Note: That is correct, the MS SQL extension is in the “php5-sybase” package.

2. Restart Apache

/etc/init.d/apache2 restart

3. Test FreeTDS

tsql -H your.server.name -p 1433 -U yourusername -P yourpassword -D yourdatabasename

If it connects, it’s working. Note: If you try to SELECT an NTEXT or NVARCHAR column you may get an error saying “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”. That is expected and will be fixed in the next step.

4. Configure FreeTDS

nano /etc/freetds/freetds.conf

Add this at the end of the file:

[yourserver]
host = your.server.name
port = 1433
tds version = 8.0

5. Test FreeTDS using server name

tsql -S yourserver -U yourusername -P yourpassword -D yourdatabasename

If you try to select something, you shouldn’t get the Unicode error now – because you specified “tds version = 8.0”.

6. Test in PHP

$link = mssql_connect('yourserver', 'yourusername', 'yourpassword');

if (!$link)
die('Unable to connect!');

if (!mssql_select_db('yourdatabasename', $link))
die('Unable to select database!');

$result = mssql_query('SELECT * FROM yourtable');

while ($row = mssql_fetch_array($result)) {
var_dump($row);
}

mssql_free_result($result);