Connect Php code in Linux to Ms Sql Server using Freetds

Using Freetds we can connect php code in Linux to Microsoft Sql Server.

We start from a fresh installation of Ubuntu 11.04.

# apt-get install dpkg-dev
# apt-get install apache2
# apt-get install php5
# /etc/init.d/apache2 restart

To check that php5 works fine

# php -v
PHP 5.3.5-1ubuntu7.10 with Suhosin-Patch (cli) (built: Jun 19 2012 00:54:05)
Copyright (c) 1997-2009 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2010 Zend Technologies

Now if all works fine you can continue.

# cd /usr/src
# apt-get source php5
# cd php5-5.3.5/ext/mssql/
# apt-get install freetds-dev
# apt-get install php5-dev
# phpize
# ./configure --with-mssql
# make
# cd modules
# cp mssql.so /usr/lib/php5/20090626+lfs/
# 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

 

Now you can configure the file  /etc/freetds/freetds.conf and add this on 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

Alternatively you can use the command line command from freetds.

apt-get install freetds-bin

And then.

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 have to fix the the used ip port and modify the config file/etc/freetds/freetds.conf accordingly.

Linkografia
http://www.freetds.org/
http://www.boedblog.blogspot.it/2012/03/ubuntu-apache-php-freetds-mssql.html