Configuring MSSQL Server to talk to PHP in Ubuntu

Configuring MSSQL Server to talk to PHP in Ubuntu

Here I have tried to describe how to configure MSSQL Server with PHP in Unbuntu for (cakePHP and codeIgniter). To configure it we need the following 4 items installed and configured. 1) The FreeTDS library (This libary allows to communicate with Microsoft SQL Server and Sybase databases). 2) MSSQL extension (php_mssql.so) 3) PDO for MSSQL (pdo_dblib) 4) Enviromental variable configuration Lets move forward and see how we can configure it. A. Install FreeTDS library 1) Download FreeTDS library $ wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz 2) Extract the compressed folder $ tar -zxvf freetds-stable.tgz 3) Change directory to the extracted folder $ cd freetds-0.91 [This was my folder name, it may change with the latest version] 4) Build the FreeTDS library $ ./configure –prefix=/usr/local/freetds –enable-msdblib $ make $ sudo make install 5) Before we do anything else we need to copy over all the necessary files from FreeTDS. Inside the downloaded source directory of               freetds, where you built it, copy the following $ sudo cp include/tds.h /usr/local/freetds/include $ sudo cp src/tds/.libs/libtds.a /usr/local/freetds/lib We are done with FreeTDS installation.

B. Creating MSSQL extension 1) We get the PHP source code $ apt-get source php5 2) Extract the compressed folder $ tar -zxvf php5_5.3.6.orig.tar.gz 3) Change directory to the extracted folder and then to the MSSQL directory inside ext folder $ cd php5-5.3.6/ext/mssql/ 4) Create and install the MSSQL extension. Make sure you have php5-dev package installed on your system so that you can build PHP             extensions from source. Check if phpize command works $ phpize      If yes then it we know php5-dev package is installed on your system. If not then we need to install, use the below command $ sudo apt-get php5-dev     After it gets install, use the command from inside the directory where your mssql extension source code is present $ phpize $ ./configure –with-mssql=/usr/local/freetds $ make $ make install Check if php_mssql.so file residing inside the PHP extension directory. In my case it is in /usr/lib/php5/20090626+lfs/ C. Install PDO for MSSQL sudo apt-get install php5-sybase D. Enviromental variable configuration We need to configure our environment to load the environment variable 1)    $ sudo vim /etc/profile At the end of the file we add export FREETDSCONF=/etc/freetds/freetds.conf 2)    $ sudo vim /etc/apache2/envvars At the end of the file we add export FREETDSCONF=/etc/freetds/freetds.conf 3)    Configuring FreeTDS $ sudo vim /etc/freetds/freetds.conf At the end of the file we add [SERVERNAME] ; This is the host name of the MSSQL server host=HOSTNAME ; This is the port number to use port=PORTNUMBER ; This is the TDS version to use for anything over Server 2000 tds version=8.0 4)     $ sudo vi /etc/ld.so.conf At the end of the file we add /usr/local/freetds/lib Then restart your apache server $ sudo /etc/init.d/apache2 restart Check the PHP info page if “mssql” and “pdo_dblib” is listed. We can also check the same by using the below command $ php -m We are done with the configuration. Lets configure our application i. CakePHP Configuration Inside Config/database.php we check the following public $default = array( ‘datasource’ => ‘Database/Sqlserver’, ‘driver’ => ‘mssql’, ‘persistent’ => false, ‘host’ => ‘xxx.xxx.xxx.xxx’, ‘login’ => ‘xxxxx’, ‘password’ => ‘******’, ‘database’ => ‘xxxx’, ‘port’ => ‘1433’, ‘prefix’ => ”, ); We should make sure that the datasource file is present inside “app/Model/Datasource/Database/Sqlserver.php” with Sqlserver class or Dblib class. ii. CodeIgniter Configuration Inside Config/database.php we change the following db[‘default’][‘dbdriver’] = ‘mssql’; Hope it comes handy to someone