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

slfedeDeveloper
Published:
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
6,196 Views

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.