PHP 5.3 not recognizing Native Client to connect to MS SQL

Question

We have a server running on

Windows Server 2003 32 Bit IIS6.0 (We have some Classic ASP apps that uses 32bit components so we can't upgrade to 2008 64bit)

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)

PHP works fine on static pages, but the issue is when trying to connect to MSSQL. We need to connect to MSSQL due to a lot of legacy code in ASP and an existing MSSQL DB.

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

This is the extensions I'm loading

[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 I run a phpinfo I can't see the sqlsrv driver in the list

I checked the PHP error log and have the following error

[22-Mar-2012 14:04:27 UTC] PHP Warning:  PHP Startup: sqlsrv: Unable to initialize module
Module compiled with build ID=API20090626,NTS,VC9
PHP    compiled with build ID=API20090626,TS,VC9
These options need to match
 in Unknown on line 0
[22-Mar-2012 14:04:27 UTC] PHP Warning:  PHP Startup: pdo_sqlsrv: Unable to initialize     module
Module compiled with build ID=API20090626,NTS,VC9
PHP    compiled with build ID=API20090626,TS,VC9
These options need to match
 in Unknown on line 0

If I change the extension to use Threaded Safe and recycle the application pool, still get this error.

I tried reinstalling Native Client and PHP a few times now but with no luck.

I was getting a different error before that sqlsrv couldn't recognize native client but now php doesn't recognize sqlsrv.

UPDATE

I managed to make it with the correct sqlsrv driver and no errors on the php errorlog

But when 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');
?>

And I'm getting this error

> 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" } }

UPDATE 2

I installed SQLCMD tools and it connects 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.

I tried with different permissions on the application pool, the native client dlls and php extension folder with no luck.

Any ideas would be great.

UPDATE 3

As it turned out it was a permission issue!

I downloaded Process Monitor and followed the instructions on this post

http://www.iislogs.com/articles/processmonitorw3wp/

Then I saw the process w3wp.exe was getting access denied on this registry key

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

I opened RegEdit and went to 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!

Cheers, Fede

Solution 1

It turned out to be a permission issue.

I changed in php.ini the option fastcgi.impersonate to 0 and try with different application pool identities. 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:

Configuring Application Pool Identity with IIS 6.0 (IIS 6.0) Service User Accounts Service Security and Access Rights

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/

Solution 2

I had the same compiler version issue, and solved it with PHP's ODBC driver:

//$pdo = new PDO("sqlsrv:Server=$hostname;Database=$dbname;", $username, $password);  // works with proper driver for PHP.
$pdo = new PDO("odbc:Driver={SQL Server};Server=$hostname;Database=$dbname;", $username, $password);  // works with proper driver for ODBC and PHP ODBC.

Comments

[Name is Nilay]

@Federico- How can v "Recycle the app pool" ??

[Federico Giust]

On IIS, right click on the Application Pool then on the new pop up menu click on Recycle. Or you can simply stop and start the application pool which is basically the same thing.

[schenker]

This worked for me. Thanks