<

Connecting PHP on IIS 6 (32 Bit) to a remote MS SQL 2008

Published on
11,382 Points
5,382 Views
Last Modified:
Approved
I know there are lots of articles out there about how to connect PHP to MS SQL. But I was having issues with it connecting to a remote MS SQL. After a week of research and trial and error I got the answer I couldn't find anywhere, so I decided to share my experience with everyone.

This is the setup we have

SERVER A - Windows Server 2003 32 Bit IIS6.0

SERVER B - Our DB Server is on a separate computer running Windows Server 2008 64 Bit MS SQL 2008 R2 64 Bit

I have installed the following version of PHP:

PHP 5.3.10 Build Date Feb 2 2012 20:26:31 Compiler MSVC9 (Visual C++ 2008) Fast CGI (Non Threaded Safe)

PHP worked fine on static pages, but the issue was when trying to connect to MSSQL.

I had SQL Native Client 2008 installed, I've installed it more than once. I checked and the DLLs were in the system32 folder.

This is the extensions I'm loading. Official Microsoft Driver for PHP version 2.0.

[PHP_SQLSRV_53_NTS_VC9]
extension=php_sqlsrv_53_nts_vc9.dll
[PHP_PDO_SQLSRV_53_NTS_VC9]
extension=php_pdo_sqlsrv_53_nts_vc9.dll

When running a phpinfo I could see the sql extension being loaded correctly, so all good so far. I was using this script to connect or give me error to sql:

<?php
$serverName = 'DBSERVER';
$connParams = array('UID'=>'UID', 'PWD'=>'PASSWORD', 'Database'=>'DATABASENAME','ReturnDatesAsStrings'=> true);
$conn = sqlsrv_connect($serverName, $connParams);
if(!$conn){
    $errors = sqlsrv_errors();
    die(var_dump($errors));
}
sqlsrv_connect($conn);
die('connected');
?>

Open in new window


But I was getting this error constantly, like PHP couldn't recognize the Native Client.

> array(2) { [0]=> array(6) { [0]=> string(5) "IMSSP" ["SQLSTATE"]=>
> string(5) "IMSSP" [1]=> int(-49) ["code"]=> int(-49) [2]=> string(390)
> "This extension requires either the Microsoft SQL Server 2008 Native
> Client (SP1 or later) or the Microsoft SQL Server 2008 R2 Native
> Client ODBC Driver to communicate with SQL Server. Neither of those
> ODBC Drivers are currently installed. Access the following URL to
> download the Microsoft SQL Server 2008 R2 Native Client ODBC driver
> for x86: http://go.microsoft.com/fwlink/?LinkId=163712" ["message"]=>
> string(390) "This extension requires either the Microsoft SQL Server
> 2008 Native Client (SP1 or later) or the Microsoft SQL Server 2008 R2
> Native Client ODBC Driver to communicate with SQL Server. Neither of
> those ODBC Drivers are currently installed. Access the following URL
> to download the Microsoft SQL Server 2008 R2 Native Client ODBC driver
> for x86: http://go.microsoft.com/fwlink/?LinkId=163712" } [1]=>
> array(6) { [0]=> string(5) "IM002" ["SQLSTATE"]=> string(5) "IM002"
> [1]=> int(0) ["code"]=> int(0) [2]=> string(91) "[Microsoft][ODBC
> Driver Manager] Data source name not found and no default driver
> specified" ["message"]=> string(91) "[Microsoft][ODBC Driver Manager]
> Data source name not found and no default driver specified" } }

Open in new window


Then I thought it could be a connection issue, so I installed SQLCMD tools and tested but it connected correctly to the server. The DLL versions are"

SQLNCLI10.DLL 2009.100.1600.1
SQLSRV32.DLL 2000.85.1117.0 (I Updated this one with the DLL from another server but still didn't help) It is now Version 6.1.7600.16385

As stated above, Native Client 2008 R2 is installed (the dlls are in windows\system32) and the DB is up and running. If I run the same script from another server it works.

Then yesterday a guy gave me an idea, to change in php.ini the option fastcgi.impersonate to 0 and try with different application pool identities. So I followed this tip and it worked as Local System. This is an account with more user rights than the Network Service or Local Service account. However, be mindful that running an application pool under an account with increased user rights presents a high security risk. For further references on the accounts and how to configure check out this articles:


I decided to set it back to Network Service and downloaded Process Monitor *. I then used it to monitor the process w3wp, which showed me this was getting access denied on a registry key where the path to sqlncli.dll is stored.

HKLM\Software\ODBC\ODBCINST.INI\SQL Native Client 10.0

So I opened RegEdit and located that key

I did right click - > Permissions and added Network Service to the list and gave it Read permissions.

Recycled the app pool and it is now working!

Hope this helps!
Federico

*There's a very good step by step article on how to use process monitor here.

http://www.iislogs.com/articles/processmonitorw3wp/
0
Comment
Author:slfede
0 Comments

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Join & Write a Comment

See the Basics of Office 365's Note Taking app, OneNote
If you, like me, have a dislike for using Online Subscription anti-spam services, then this video series is for you. I have an inherent dislike of leaving decisions such as what is and what isn't spamming to other people or services for me and insis…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month