How to connect CodeIgniter to MS SQL Server 2008

On this post I will try to explain step by step how to connect CodeIgniter to a MS SQL Server Database, I recently had to do it and had a few troubles to make it work, and no clear explanation, For these instructions I used XAMPP, but it can be applied to any other stack, just considering which PHP version they may have.

FOR WINDOWS XP

For Windows XP we have to use the 5.3.x PHP version, because the native SQL Client version and the PHP SQL driver compatible with windows XP wont work with higher versions, If you are using XAMPP, version 1.7.3 works perfectly. You can download old XAMPP versions from Here.

1st Step - Install the SQL Native Client from Microsoft:

For Windows XP the SQL Native Cliente 2008 version is sufficient for the version of the driver we will install on the next step, you can install 2012 version additionally, if you want to. The Client version for 2008 can be downloaded from  Here.

2nd Step - Install The Microsoft Driver for PHP for SQL Server :

To use the Microsoft Drivers for PHP for SQL Server, you most have the SQL Server Native Client from step One installed on the same PC that PHP is running.

We will download version 2 , that needs the Native Client for 2008 that we downloaded on step One , Download it from Microsoft Drivers 3.0 for PHP for SQL Server (Download Version 2 ).

Why Version 2 ?, because its the one that works with PHP versions 5.3.x, It has the following files included:

php_sqlsrv_53_ts_vc6.dll

php_pdo_sqlsrv_53_ts_vc6.dll

I used this ones, for you meay be differente, to know wich version to use you must look at the Phpinfo() in you server:
- Wich version of PHP you have (for example 5.3.x -> 53)
- Compiled with what version of C ( for example VC 6-> vc6)
- And if Thread Safety is enabled (enabled ->ts, not enabled -> nts)

You must install this driver on the extensions folder of PHP, in my case using XAMPP its in c:\xampp\php\ext

3rd Step - Changes on PHP.ini :

On the PHP configuration, especifically on the php.ini file, we must add the following, in order to activate the sqlsrv extensions:

extension=php_sqlsrv_53_ts_vc6.dll

extension=php_pdo_sqlsrv_53_ts_vc6.dll

then we must restart the web server.

4th Step - CodeIgniter Configuration:

Finally on the database.php file on the configuration folder of Codeigniter we must have the following configuration (pay attention to details):

$db['default']['hostname'] = '192.168.XX.XX'; //Ip or name of your SQL server 

$db['default']['username'] = 'sa';

$db['default']['password'] = 'yourpassword';

$db['default']['database'] = 'your_DB';

$db['default']['dbdriver'] = 'sqlsrv'; //yes, must use sqlsrv

$db['default']['dbprefix'] = '';

$db['default']['pconnect'] = FALSE; //put it on 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;

And that's it, you can now use MS SQL Server as a database server for your    CodeIgniter proyect.

FOR WINDOWS 7 OR NEWER:

For Windows 7 we can use the latest version of XAMPP which comes with PHP 5.4.x so you can download it from  Here

1st Step - Install the SQL Native Client de Microsoft:

Now we do need the 2012 version of this client, you can download it from  Here

2nd Step - Install The Microsoft Driver for PHP for SQL Server :

To use the Microsoft Drivers for PHP for SQL Server, you most have the SQL Server Native Client from step One installed on the same Pc that PHP is running.

If we are using Windows 7 we can download the version 3 so that it works with our PHP version 5.4.x, download it from Microsoft Drivers 3.0 for PHP for SQL Server (Download Version 3).

It contains, among others, the files:

php_sqlsrv_54_ts.dll

php_pdo_sqlsrv_54_ts.dll

php_sqlsrv_54_nts.dll

php_pdo_sqlsrv_54_nts.dll

Acoording to wether your PHP is configured with Thread Safety(ts) or without (nts).

You must install this driver on the extensions folder of PHP, on XAMPP this is in c:\xampp\php\ext.

3rd Step - Changes on PHP.ini :

On the PHP configuration, especifically on the php.ini file, we must add the following, in order to activate the sqlsrv extensions:

extension=php_sqlsrv_54_ts.dll

extension=php_pdo_sqlsrv_54_ts.dll

And we restart the web server.

4th Step - CodeIgniter Configuration:

Finally on the database.php file on the configuration folder of Codeigniter we must have the following configuration (pay attention to details):

$db['default']['hostname'] = '192.168.XX.XX'; //Ip or name of your SQL server 

$db['default']['username'] = 'sa';

$db['default']['password'] = 'yourpassword';

$db['default']['database'] = 'your_DB';

$db['default']['dbdriver'] = 'sqlsrv'; //yes, must use sqlsrv

$db['default']['dbprefix'] = '';

$db['default']['pconnect'] = FALSE; //put it on 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;

And that's it, you can now use MS SQL Server as a database server for your    CodeIgniter project.

And that, my Friend, It's all!