Connect PHP in Debian Jessie to Ms Sql Server using FreeTDS

In this post we will connect Php code in Linux env (Debian 8 Jessie) to M$ Sql Server, using FreeTDS (latest vers.).

We start from a fresh installation of Debian 8 Jessie.

# apt-get update && sudo apt-get upgrade
# apt-get install apache2
# apt-get install php5
# /etc/init.d/apache2 restart
# apt-get install dpkg-dev
# apt-get install devscripts

To check that php5 works fine

# php -v
PHP 5.6.13-0+deb8u1 (cli) (built: Sep 7 2015 13:38:37)
Copyright (c) 1997-2015 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2015 Zend Technologies
with Zend OPcache v7.0.6-dev, Copyright (c) 1999-2015, by Zend Technologies

Now if all works fine you can continue.

# cd /usr/src
# apt-get source php5
# apt-get build-dep php5
# apt-get install php5-dev
# wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-patched.tar.gz
# tar zxvf freetds-patched.tar.gz
# cd freetds-0.95.21/
# ./configure --sysconfdir=/etc/freetds
# make; make install; make clean
# cd /usr/src/php5-5.6.13+dfsg/ext/mssql/
# phpize
# ./configure --with-mssql
# make
# cd modules
# cp mssql.so /usr/lib/php5/20131226/
# nano /etc/php5/apache2/php.ini

Add on a new line the following and then save.

extension = mssql.so

Now for the best check you can restart the server and read the boot log to detect eventually error and warning.

To check that Php5 and Apache2 “speak each others” and to verify the current running version create in /var/www/ a file named test.php, and insert this single line.

<?php
phpinfo();
?>

Open your web browser and type the url: http://<ip server>/test.php.

If you will see a table like in then next fig all works fine !

php mssql image

 

To check freetds by command line

# tsql -C

Now you can configure the file /etc/freetds/freetds.conf and add the next lines at the end of the file

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

Now you can verify a real code that submit a query e return a result.

<?php
$conn = mssql_connect("servername", "<user>", "<password>");
mssql_select_db( "Database1", $conn );
$query_result = mssql_query( "SELECT field1 FROM Table1", $conn );
echo "The field number one is: ";
echo mssql_result ($query_result, 0, 0);
mssql_close($conn); // close connection
?>

If something goes wrong you can verify if the server can connect to Sql Server.

# telnet <your.server.name> 1433

You can check sql server connection using command line.

tsql -S servername -p 1433 -U <user> -P <password>

tsql command must be retun the 1> prompt, where you can submit query.

Att: Some notes related to the instance of Sql Server that we want to use with freetds.
– The instance must be accept Sql Server authentication.
– The instance must be configured to use TCP/IP connection library
– The instance must be a default instance, otherwise (in case of named instance you need to fix the the used ip port by Sql Server modifing the config file/etc/freetds/freetds.conf accordingly).

In this post I preferred to use the latest version of freetds and therefore I had to recompile everything. You can use a more old version of freetds and in this case the procedure is more simple.

# apt-get install php5-sybase

Att.: Sysbase library for PHP also includes FreeTDS

# nano /etc/freetds/freetds.conf

Change this line under the [global] section in next

tds version = 8.0

At the end restart Apache

/etc/init.d/apache2 restart

That’all
You can check freetds version using tsql.

# tsql -C 

Linkografia
http://www.freetds.org/