PHP 5.3.10 Error When trying to connect to remote SQL Server

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.

Answer 1

Hi slfede,

Where did you install the SQL Server Native Access Client from?

Cheers,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 2

Hi Jonathan,

I downloaded SQL Native Client 2008 R2 x86 from the Microsoft SQL Server 2008 feature pack on the Microsoft Downloads website.

I installed it on the same server I have PHP installed. SQL Server 2008 is running on a different server.

If I run this script to get the SQL Native Client info.

<?php
$serverName = 'DB';
$connParams = array('UID'=>'UID', 'PWD'=>'PWD', 'Database'=>'Database', 'ReturnDatesAsStrings'=> true);
$conn = sqlsrv_connect($serverName, $connParams);


if( $conn === false ) {
    die( print_r( sqlsrv_errors(), true));
}

if( $client_info = sqlsrv_client_info( $conn)) {
    foreach( $client_info as $key => $value) {
        echo $key.": ".$value."<br />";
    }
} else {
    echo "Error in retrieving client info.<br />";
    die( print_r( sqlsrv_errors(), true));
}
?>

I also get the same errors as before.

I installed SQLCMD tools and it connects correctly to the server.

I'm completely lost and can't find any solution online yet.

Thanks,

Federico

Answer 3

Hi again,

I've added this code to the first script

if (function_exists('sqlsrv_connect')) {
echo "Connection functions are available.<br />\n";
} else {
echo "Connection functions are not available.<br />\n";
}

So it tells me if the function exists, and it does so the sqlsrv driver is being loaded correctly. But it wont talk to Native Client.

Cheers,

Federico

Answer 4

Are you using version 3.0 of the driver (the new one) or 2.0?

Officially, your web server cannot use the php driver 3.0, because MSFT won't support it - or maybe they want to coerce us away from XP/2003.  In reality, this is premature because in big production systems it sometimes takes years of planning and money to upgrade safely, and it's usually beyond the control of us developers.

You can run PHP on a 64 bit server very well - install the usual 32-bit PHP, with the 64-bit Native Client, and it will work.

To summarise - in your current configuration you need one of these versions of the driver:

• Official version 2.0 from MSFT: http://www.microsoft.com/download/en/details.aspx?id=20098
• Unofficial version 3.0 custom build: [Deleted] New link Sept 24 2012: http://robsphp.blogspot.co.uk/2012/09/how-to-install-microsofts-sql-server.html

---

Rob

Answer 5

I tried with the unofficial driver but still get the exact same error.

Upgrading to 64 bit is not an option at the moment, we have a number of clients using reports on screen and for that we are currently using asp + crystal reports 8 (this last one doesn't run on 64 bit).

To use the new driver I need SQL Native Client 2012 I need to upgrade to windows server 2008 and I'm using 2003. This is the production server so it's a not that easy to upgrade as we have a number of clients from different countries at all times.

Federico

Answer 6

Federico,

The 2.0 driver is still available for download. Use the 2.0 driver for now, and the 2008 R2 Native Access Client. You should still install the 64-bit client even if you are using a 32-bit OS, as it will install both versions for you.

So, to clarify - do you have both of these installed:

• Microsoft Drivers 2.0 for PHP for SQL Server: http://download.microsoft.com/download/C/D/B/CDB0A3BB-600E-42ED-8D5E-E4630C905371/SQLSRV20.EXE
• Microsoft SQL Server 2008R2 Native Access Client X64: http://go.microsoft.com/fwlink/?LinkID=188401&clcid=0x409

Thanks,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 7

Hi Jonathan -

Frederico's web server is "Windows Server 2003 32 Bit IIS6.0".

A direct link to the 2008 R2 Native Client x86 version is here: http://go.microsoft.com/fwlink/?LinkID=188400

(Found on page: http://www.microsoft.com/download/en/details.aspx?id=16978 under "Microsoft® SQL Server® 2008 R2 Native Client" x86 Package.)

You *cannot* install the 64 bit client on a 32 bit OS.  I think that's why the 32-bit client is there.

---

Rob

Answer 8

My bad, I misread that. Thanks for the correction.

Cheers,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 9

Frederico

Can you load the 2008 R2 x86 Native Client from the post above, then try again...?

If the problem persists, let's get back to basics.

1. Are you using the ISAPI PHP interface to IIS 6.0?
       If Yes, then you need the 'ts' thread safe version of PHP, and the MSFT PHP driver.
       If No, then you must be using fastCGI (recommended), and you need the 'nts' version of PHP and the MSFT driver.
2. If you dropped my dll into your extension folder, did you change php.ini to load the new file name?
3. If you did update php.ini, did you stop and start your web server to load the new dll?

If none of these questions help, please take a look at this thread, and then post your phpinfo() in the same way: http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/thread/0e18b438-09c8-43c9-93fd-b3401545bd8c

then we can tell you exactly how to set up your server.

---

Rob

Answer 10

Hi Guys,

As mentioned on the first post.

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

SQLNCLI10.DLL 2009.100.1600.1

Regarding your comments Robert

1 - No. I'm using CGI and NTS version of PHP. Also have version 2 and your version of MSFT driver.

2 - Yes I did

3 - Yes I did. I even did an iisreset.

And still get the same error

array(2) { [0]=> array(6) { [0]=> string(5) "IMSSP" ["SQLSTATE"]=> string(5) "IMSSP" [1]=> int(-49) ["code"]=> int(-49) [2]=> string(282) "This extension requires the Microsoft SQL Server 2012 Native Client, or SQL Server 2008 Native Client, SQL Server 2005 Native Client. Access the following URL to download the Microsoft SQL Server 2012 Native Client ODBC driver for x86: http://go.microsoft.com/fwlink/?LinkId=163712" ["message"]=> string(282) "This extension requires the Microsoft SQL Server 2012 Native Client, or SQL Server 2008 Native Client, SQL Server 2005 Native Client. Access the following URL to download the Microsoft SQL Server 2012 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" } }

That post is on an apache server I'm using IIS 6.

This is my PHP Info

PHP Version 5.3.10

System	Windows NT WWW52003 5.2 build 3790 (Windows Server 2003 R2 Enterprise Edition Service Pack 2) i586
Build Date	Feb 2 2012 20:10:58
Compiler	MSVC9 (Visual C++ 2008)
Architecture	x86
Configure Command	cscript /nologo configure.js "--enable-snapshot-build" "--enable-debug-pack" "--disable-zts" "--disable-isapi" "--disable-nsapi" "--without-mssql" "--without-pdo-mssql" "--without-pi3web" "--with-pdo-oci=C:\php-sdk\oracle\instantclient10\sdk,shared" "--with-oci8=C:\php-sdk\oracle\instantclient10\sdk,shared" "--with-oci8-11g=C:\php-sdk\oracle\instantclient11\sdk,shared" "--with-enchant=shared" "--enable-object-out-dir=../obj/" "--enable-com-dotnet" "--with-mcrypt=static" "--disable-static-analyze"
Server API	CGI/FastCGI
Virtual Directory Support	disabled
Configuration File (php.ini) Path	C:\WINDOWS
Loaded Configuration File	C:\PHP\php.ini
Scan this dir for additional .ini files	(none)
Additional .ini files parsed	(none)
PHP API	20090626
PHP Extension	20090626
Zend Extension	220090626
Zend Extension Build	API220090626,NTS,VC9
PHP Extension Build	API20090626,NTS,VC9
Debug Build	no
Thread Safety	disabled
Zend Memory Manager	enabled
Zend Multibyte Support	disabled
IPv6 Support	enabled
Registered PHP Streams	php, file, glob, data, http, ftp, zip, compress.zlib, phar, sqlsrv
Registered Stream Socket Transports	tcp, udp
Registered Stream Filters	convert.iconv.*, mcrypt.*, mdecrypt.*, string.rot13, string.toupper, string.tolower, string.strip_tags, convert.*, consumed, dechunk, zlib.*

PDO

PDO support	enabled
PDO drivers	sqlsrv

pdo_sqlsrv

pdo_sqlsrv support	enabled
Version	3.0.0.0 (Unofficial)
Unofficial Changes	Works with Native Clients 11/10/9

Directive	Local Value	Master Value
pdo_sqlsrv.client_buffer_max_kb_size	10240	10240
pdo_sqlsrv.log_severity	0	0

sqlsrv

sqlsrv support	enabled
Version	3.0.0.0 (Unofficial)
Unofficial Changes	Works with Native Clients 11/10/9

Directive	Local Value	Master Value
sqlsrv.ClientBufferMaxKBSize	10240	10240
sqlsrv.LogSeverity	0	0
sqlsrv.LogSubsystems	0	0
sqlsrv.WarningsReturnAsErrors	On	On

Environment

Variable	Value
_FCGI_X_PIPE_	\\.\pipe\IISFCGI-9e354aa8-cce8-4980-9230-b10c01106dbe
PHP_FCGI_MAX_REQUESTS	10000
ALLUSERSPROFILE	C:\Documents and Settings\All Users
APP_POOL_ID	beta.shocklogic.com
CI_HOLOS_CLI	C:\Program Files\Seagate Software\Open Olap\
ClusterLog	C:\WINDOWS\Cluster\cluster.log
CommonProgramFiles	C:\Program Files\Common Files
COMPUTERNAME	WWW52003
ComSpec	C:\WINDOWS\system32\cmd.exe
FP_NO_HOST_CHECK	NO
NUMBER_OF_PROCESSORS	2
OS	Windows_NT
Path	C:\PHP;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\Program Files\ICW\Bin;C:\Program Files\TortoiseSVN\bin;C:\WINDOWS\system32\WindowsPowerShell\v1.0\
PATHEXT	.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.PSC1
PHPRC	C:\PHP
PROCESSOR_ARCHITECTURE	x86
PROCESSOR_IDENTIFIER	x86 Family 6 Model 26 Stepping 5, GenuineIntel
PROCESSOR_LEVEL	6
PROCESSOR_REVISION	1a05
ProgramFiles	C:\Program Files
SystemDrive	C:
SystemRoot	C:\WINDOWS
TEMP	C:\WINDOWS\TEMP
TMP	C:\WINDOWS\TEMP
USERPROFILE	C:\Documents and Settings\Default User
windir	C:\WINDOWS

Is it possible to use PHP 5.4 with your version 3 of the driver on Windows Server 2003 32bit?

Also as mentioned before, static PHP works. It's just the connection to MS SQL.

Thanks,

Federico

Answer 11

Thanks Frederico.

Thanks for the information, sorry - I notice that you stated you had installed the 2008 R2 Native Client in your previous posts.

The reason we keep going on about it is because the error you published in your first post specifically means that the ODBC driver is not installed!  In 'Control Panel' \ 'Administrative Tools', you should be able to open the "Data Sources" ODBC driver manager, and check that this driver is on the list: "SQL Server Native Client 10.0" when you click the 'Add' button.

Your first post also says : "PHP compiled with build ID=API20090626,TS,VC9", but now your phpinfo shows you have reinstalled PHP as CGI, non-thread-safe...

Based on this, you need these extensions from MSFT's SQLSRV20.exe in php.ini:

• extension=php_sqlsrv_53_nts_vc9.dll
• extension=php_pdo_sqlsrv_53_nts_vc9.dll

*Or* from my build for version 3.0:

• extension=php_sqlsrv_30_53_nts_vc9x86.dll
• extension=php_pdo_sqlsrv_30_53_nts_vc9x86.dll

I don't want to clutter this answer, but if you install PHP 5.4, these are the required dll's, available from my build:

• extension=php_sqlsrv_30_54_nts_vc9x86.dll
• extension=php_pdo_sqlsrv_30_54_nts_vc9x86.dll

---

Rob

Answer 12

Hi Robert,

Yes my current PHP is CGI and non-threaded-safe.  The phpinfo i posted is the latest one I've got.

The strange thing is one of the guys in our team tried to replicate this by having the same environment and it works for him.

I've tried both with version 2.0 and your version 3.0 of the driver without any success.

If I go to ODBC I see the driver there

The dlls are in system32

Fast cgi settings, is allowed

The application pool identity is set to Network Service (I've tried on all possible users and still nothing)

The permissions on the PHP Folder, SQLNCLI10.dll and SQLSRV32.dll are the default

Administrator

Creator Owner

System

Users

Thanks,

Federico

Answer 13

Hi Frederico

It might be permissions, but probably isn't, especially if you have already tried 'Local System' in the Application Pool.

You have successfully loaded my driver in phpinfo() - sorry to repeat things again, but could you run the code sample supplied by Jonathan again, using my build of the driver?

The error message (if the dll is dated 2012/03/23) goes something like this: "This extension requires the Microsoft SQL Server 2012 Native Client, or SQL Server 2008 Native Client, SQL Server 2005 Native Client..."

Please post exactly the output you see, and also check your php error log for messages which might be related.

---

Rob

Answer 14

Hi Robert,

I'm not sure which code you mean by Jonathan's I don't see any code from him in this post.

This is the exact message using your driver

array(2) { [0]=> array(6) { [0]=> string(5) "IMSSP" ["SQLSTATE"]=> string(5) "IMSSP" [1]=> int(-49) ["code"]=> int(-49) [2]=> string(282) "This extension requires the Microsoft SQL Server 2012 Native Client, or SQL Server 2008 Native Client, SQL Server 2005 Native Client. Access the following URL to download the Microsoft SQL Server 2012 Native Client ODBC driver for x86: http://go.microsoft.com/fwlink/?LinkId=163712" ["message"]=> string(282) "This extension requires the Microsoft SQL Server 2012 Native Client, or SQL Server 2008 Native Client, SQL Server 2005 Native Client. Access the following URL to download the Microsoft SQL Server 2012 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" } }

Cheers,

Federico

Answer 15

Thanks Frederico

I'm running out of ideas.

Was anything in your php error log?

From the ODBC settings in Control Panel, can you test that you can make a connection to SQL Server using driver "SQL Server Native Client 10.0"?

To eliminate permissions as the cause:

1. set your web site's app pool identity to "Local System"
2. in php.ini set "fastcgi.impersonate=0"
3. restart your web server and try again

---

Rob

Answer 16

Hi Rob,

There was nothing on the error log.

When making a connection from ODBC settings work fine.

This is the result from ODBC

Microsoft SQL Server Native Client Version 10.50.1600

Running connectivity tests...

Attempting connection
Connection established
Verifying option settings
INFO: A network alias was found for the DSN server. 'Protocol: DBMSSOCN; Address: DB1' was used to establish the connection.
Disconnecting from server

TESTS COMPLETED SUCCESSFULLY!

After following your instructions with App Pool as Local System it works! So it looks like it is something related to permissions!

Is it safe to leave the production server as local system? I'm not so sure about that.

Thanks

Federico

Answer 17

Wow! That's an interesting find Frederico.

When you create your connection from Control Panel, it's done as your logged-in identity. 

PHP will be using the 'Network Service' group account from your app pool, or possibly a domain account if Windows Authentication is switched on...   I wonder what extra permissions are needed for ODBC?  Anyone from MSFT welcome to help!

I usually run my PHP sites as Local System, because WinCache (PHP byte-code cache for Windows) does not work when running as Network Service.  I gave up trying to work out why.

If you still have any energy left, you could try connecting as Network Service using the PHP odbc interface, e.g. odbc_connect(...), and see if it still doesn't work.  That, at least, would eliminate the PHP driver as the problem.

Glad it's fixed.

---

Rob

Answer 18

Hi Rob!

I just made it work as Network Service!!!!!

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

Answer 19

That was a very strange set of circumstances for this error. Glad you got it sorted out!

Rob - thanks for your valuable help, as always!

Cheers,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 20

From my experience with Microsoft lately.... everything is a very strange set of circumstances, and I'm not talking about just this case.

Anyways, forgot to thank you and specially Rob for his patience, driver and help !

Thanks again guys.

Fede

Answer 21

Are you using version 3.0 of the driver (the new one) or 2.0?

Officially, your web server cannot use the php driver 3.0, because MSFT won't support it - or maybe they want to coerce us away from XP/2003.  In reality, this is premature because in big production systems it sometimes takes years of planning and money to upgrade safely, and it's usually beyond the control of us developers.

You can run PHP on a 64 bit server very well - install the usual 32-bit PHP, with the 64-bit Native Client, and it will work.

To summarise - in your current configuration you need one of these versions of the driver:

• Official version 2.0 from MSFT: http://www.microsoft.com/download/en/details.aspx?id=20098
• Unofficial version 3.0 custom build: http://www.rmjohnson.demon.co.uk/PHP/sqlsrv/
  

---

Rob

Hi Rob,

Your site appears to be down, and your webmaster email address is incorrect. :)

Cheers,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 22

Thanks Jonathan, I cancelled my demon account.

Here's the link to my blog, with all the links on it: http://robsphp.blogspot.co.uk/2012/09/how-to-install-microsofts-sql-server.html

I've updated the post above also.

---

Rob