Connection properties for sqlsrv

[eluser]Joa[/eluser]
Hello. I'm stuck trying to connect to a MS Sql 2008 R2 server using CI 2.1.0, running WAMP on a windows machine (same as SQL server).

I successfully installed the php extension php_sqlsrv_53_ts_vc9.dll and verified it works (using standard php syntax) but I'm unable to get the CI driver connecting. Maybe I'm writing the connection wrong? Any help much appreciated. My error is:

A Database Error Occurred
Unable to connect to your database server using the provided settings.

Filename: C:\wamp\www\system\database\DB_driver.php

Line Number: 124

My connection script:

Code:

$db['default']['hostname'] = 'HOST/SQLINSTANCE';
$db['default']['username'] = 'domain\user'; // uses windows authentication
$db['default']['password'] = 'password';
$db['default']['database'] = 'database';
$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;

Please, anyone????

/ Joa

[eluser]Tybion[/eluser]
Joa,

There is a patch in the article below, and I have given an example config that works.
Suggest you get it working for a SQL account before you try with an Active Directory account.

http://ellislab.com/forums/viewthread/205186/

[eluser]Joa[/eluser]
Thanks. With the patch It fixed my problem if I remove "username" and "password" information ie uses no authentication information. The settings in the SQL server is that it uses "Windows authentication" to authenticate. But how is this controlled? What account is used for PHP to connect? Is it secure for a public web to have CI running without username/password??? Or does PHP use some kind of magic account. Sorry I'm a web developer not a Windows guy.

Thanks for suggestions...

[eluser]Tybion[/eluser]
Suggest that you ask the SQL Server administrator to monitor the connections on the SQL Server. They will be able to see what your PHP code is connecting as.
Might be good if they can set up authentication as 'mixed' - ie. SQL Server accounts and AD accounts.

[eluser]Tybion[/eluser]
A thought - it might be authenticating as the account that the wampapache service is running under.

[eluser]Joa[/eluser]
Good idea, I'll look into it tomorrow!!! Thanks.

[eluser]Joa[/eluser]
wampapache (httpd.exe) is running as SYSTEM, and there is no option for setting Sql authentication to "mixed", either I use a predefined account (currently NETWORK SERVICE) or i select another AD account.

So I'm stuck here anyway. Would be useful to know about the security issues for putting the CI solution public when the database connection has no user/pass? Anyone?

Kind regards Joa

[eluser]mah0001[/eluser]
I have been doing some development using SQLSRV with CI 2.x recently, here are few things you should try:

1) database config properties:

Code:

//use forward slash (\) instead of backslash(/) for instance names
$db['sqlsrv']['hostname'] = "localhost\sqlexpress";

//if need to use port number
$db['sqlsrv']['hostname'] = "localhost\sqlexpress,1433";

2) Is SQL Server running on the same machine? If not, you need to install SQL Server 2008 R2 client on the web server.

3) I see you can connect using the windows authentication. Check if you have enabled MIXED Mode authentication for SQL Server. If not, it is easy, open SQL Management Studio and edit the sql server instance properties and make the change.

4) Test the database username/password: Open SQL Management Studio and try to login using the database username/password and see if you can access the database.

5) Test the database connectivity using ODBC: Create a new connection and use the database username/password to connect to your database.

6) Check if TCP/IP setting is enabled in the SQL Server Configuration Manager.

I hope it helps.

[eluser]mah0001[/eluser]
Here are the steps to enable MIXED MODE authentication.

1) Open the SQL Management Studio and login using your windows authentication as usual.
2) on the left side bar(object explorer), right click on the instance name (e.g. localhost/sqlexpress) and click on Properties.
3) On the server properties dialog, click on the Security link on the left sidebar.
4) Change the server authenication to: SQL Server and Windows Authentication Mode.
5) Done

[eluser]Joa[/eluser]
Thanks a lot mah0001. I am running SQL Server 2008 R2 on the same machine as wampserver. But I don't have a program called SQL Management studio. I only have 'SQL Server Configuration Manager' and 'SQL Server Installation Center' in the Start menu. The database server is installed as part of another software install, a crm-solution for a small sized company. Therefore I'm also a bit anxious to alter authentication method. I am developing a web front end for some of it's information and need to access data reasonably safe.

Thanks for suggestions.

Kind regards Joa