CodeIgniter MSSQL (SQLSRV)

After struggling for a day, manage to configure CodeIgniter with MSSQL using sqlsrv drivers. Please note that I’m not using mssql or odbc drivers. I have not tried with those two drivers yet.

Before I go forward following is my environment
CodeIgniter – 2.1.3
XAMPP – PHP (5.4.7), Apache (2.4)
MSSQL  – MS SQL Express edition 2008 R2
SQL Drivers :  php_pdo_sqlsrv_54_ts.dll,php_sqlsrv_54_ts.dll [Download it from http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=20098%5D Download SQLSRV30.EXE instead version 2 SQLSRV20.EXE

Following are the steps to configure

  •  Download the SQL drivers from the above link
  •  Copy  php_pdo_sqlsrv_54_ts.dll,php_sqlsrv_54_ts.dll and paste it in the php extension folder [php/ext/]
  •  Configure php.ini
    – Add the following lines into php.ini
    extension=php_pdo_sqlsrv_54_ts.dll
    extension=php_sqlsrv_54_ts.dll
  • Configure  – CodeIgniter
    – Open the file database.php [application/config/database.php]
    – Configure the Database settings accordingly. Following is example that suites my environment
    $active_group = 'default';
    $active_record = TRUE;
    $db['default']['hostname'] = 'WIN-VM2P2F1SHSN\MANJU';//Use the SQL Server instance instead with port
    $db['default']['username'] = 'sa'; //DB Username
    $db['default']['password'] = 'pass1234'; //DB Password
    $db['default']['database'] = 'db_radius'; //DB Name
    $db['default']['dbdriver'] = 'sqlsrv'; //Drivers
    $db['default']['dbprefix'] = '';
    $db['default']['pconnect'] = FALSE;
    $db['default']['db_debug'] = TRUE;
    $db['default']['cache_on'] = FALSE;
    $db['default']['cachedir'] = '';
    $db['default']['char_set'] = 'utf8';
    $db['default']['dbcollat'] = 'utf8_general_ci';
    $db['default']['swap_pre'] = '';
    $db['default']['autoinit'] = TRUE;
    $db['default']['stricton'] = FALSE;
  •  Restart the web server and test the following code with your database settings just to make sure connection is working
    <?php
    $serverName = "WIN-VM2P2F1SHSN\MANJU"; //serverName\instanceName
    $connectionInfo = array( "Database"=>"db_radius", "UID"=>"sa", "PWD"=>"pass1234");
    $conn = sqlsrv_connect( $serverName, $connectionInfo);
    if( $conn ) {
    echo "Connection established.<br />";
    }else{
    echo "Connection could not be established.<br />";
    echo "<pre>";
    print_r(sqlsrv_errors());
    echo "</pre>";
    }
    ?>
  • I have attached entire codeIgniter source code. Please configure and change the parameters accordingly to make it work Download
  • Note that for some operating systems you might need to install Microsoft SQL Server 2012 Native Client. Please check here for more info http://php.net/manual/en/sqlsrv.requirements.php

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: