CodeIgniter MSSQL connection

Question

I'm working on a web project that is to be deployed on a 64bit Windows 2008 Server machine running IIS 7.5 and PHP 5.3.8. The database on the system is Microsoft SQL Server 2008 R2. I'm developing the application on the CodeIgniter 2.1.0 framework, and I'm a bit stumped on getting it to connect to the SQL Server.

I've tried using both the MSSQL, ODBC and SQLSRV database drivers, and have encountered three separate errors, for each different driver.

Here is my configuration for ODBC:

$db['default']['hostname'] = 'SA';
$db['default']['username'] = 'petre';
$db['default']['password'] = 'start';
$db['default']['database'] = 'petre';
$db['default']['dbdriver'] = 'odbc';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$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;

I have the DNS for SA defined under Data Sources, and I'm sure that the username and password are both valid, and that the server accepts Mixed authentication(Windows + SQL authentication).

I'm auto-loading the DB library and for any page I access I get:

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

If I try to connect via MSSQL, I just get a blank page, no matter what.

If I try using SQLSRV using the following params:

$db['default']['hostname'] = '127.0.0.1';
$db['default']['username'] = 'petre';
$db['default']['password'] = 'start';
$db['default']['database'] = 'petre';
$db['default']['dbdriver'] = 'sqlsrv';

Even though I'm linking to the sqlsrv DLL file in php.ini, I get the following message:

PHP Fatal error:  Call to undefined function sqlsrv_connect() in C:\inetpub\wwwroot\system\database\drivers\sqlsrv\sqlsrv_driver.php on line 76

I've been reading up on old posts and different views but I've yet to solve the problem.

I'm only looking for a solution for a single DB Driver - anything that gets it to connect is fine. Does anyone have any advice on solving this?

Solution 1

The error you are showing for the SQLSRV driver implies the DLL is never loading. What does your php_info() say?

Also, do you have the SQL Server Native Access Client (SNAC) 10 installed? The SQLSRV driver requires it.

Solution 2

For x86 win download http://www.microsoft.com/en-us/download/details.aspx?id=20098 for using with:

$db['default']['dbdriver'] = 'sqlsrv';
use the php_sqlsrv_XX_ts_vcX.dll in php.ini extension

if your webserver and your database server are not on same server with your app, install this:

http://go.microsoft.com/fwlink/?LinkID=188400&clcid=0x409

and for linux as webserver:
$db['default']['dbdriver'] = 'mssql';

download & install = php5-sybase + freetds
<br/>edit /usr/local/etc/freetds.conf
<br/>create pdo_dblib.so & php_mssql.so (if not allready exist on your extension dir), and apply to php.ini

sudo /etc/init.d/apache2 restart

and just dont forget to allow connection to your DB (default port for mssql is 1433)

Comments

[Petre Pătraşc]

Thanks, that was the stepping-stone to solving the problem. I found out that the problem was CodeIgniter's built-in ODBC driver, which adds apostrophes around the table names.