Codeigniter MSSQL/SQLSRV Connection

Question

I'm working on a web app that connects to a MSSQL database. I previously managed to connect to the database without any problems, however I made a minor change to the sqlsrv_driver file. I encountered the below error, rolled my changes back to the original file, but there are still issues.

I have ensured that I have relevant MS drivers installed, correct port number used, etc however its returning this error:

Unable to connect to your database server using the provided settings.
Filename: D:...\system\database\DB_driver.php
Line Number: 124  

Further to this I have made a connection out side of codeigniter via the sqlsrv_connect function. Which a successful connection is made with no errors.

Why am I getting this error?

Answer 1

For connection from PHP->SQLSrv you need install these drivers :

a. Microsoft Drivers 3.0 for PHP for SQL Server (SQLSRV30.EXE)

b. Microsoft Visual C++ 2010 Redistributable Package (32bits/64bits)

c. Microsoft® SQL Server® 2012 Native Client (sqlncli.msi)

And add this row in php.ini file:

extension=php_sqlsrv_54_ts.dll

Answer 2

The Driver sqlsrv is buggy. Open /system/database/drivers/sqlsrv/sqlsrv_driver.php

To allow pconnect in your configuration, change line 89 from

$this->db_connect(TRUE);

to

return $this->db_connect(TRUE);

If you want to use affected_rows correctly, then change line 274 from

return @sqlrv_rows_affected($this->conn_id);

to

return @sqlsrv_num_rows($this->result_id);

I saw multiple suggestions of how to fix affected_rows posted elsewhere, but changing _execute to not use Scrollable will break stored sessions if you're also using sqlsrv for session validation.

Answer 3

I just got this problem solved. And I was connecting to MSSQL hosted with microsoft Azure

Steps I followed after several research work on internet are as follows :

database.cfg :

$db['default']['hostname'] = 'XXXXXXX.database.windows.net';
$db['default']['username'] = 'username';
$db['default']['password'] = 'password';
$db['default']['database'] = 'databasename';
$db['default']['dbdriver'] = 'sqlsrv';
$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;

Mainly dbdriver,pcconnect and the hostname you should configure properly. Rest are common. Get hostname from your azure database details.

And I also modified couple of system files as I heard there were issues with DB driver.

system/database/drivers/sqlsrv/sqlsrv_driver.php

function db_pconnect()
    {
        //$this->db_connect(TRUE);
        return $this->db_connect(TRUE);
    }

and

function affected_rows()
{
    //return @sqlrv_rows_affected($this->conn_id);
    return @sqlsrv_num_rows($this->result_id);
}

I was able to connect to database and create database application.

Hope it will help someone in need :)