MS server 2008 R2 PHP connecting to SQL2000

chenks · August 12, 2018, 1:56pm

i’m migrating a windows server 2003 (32-bit) with php 5.3.8 to windows server 2008 R2 (64-bit).
we have an SQL Server 2000 (32-bit).

we have some intranet sites that connect to the SQL server via php. this all works fine on server 2003 IIS.
having set up the 2008 R2 server with the same version of PHP and the same extensions enabled we get an error.

(PHP 5.3.8 installed (php-5.3.8-nts-Win32-VC9-x86.msi)
“extension=php_pdo_sqlsrv_53_nts_vc9.dll” and “extension=php_sqlsrv_53_nts_vc9.dll” enabled in php.ini (and the dll files placed in in the “ext” folder).
php.php loads find and shows php is running

the method we use to connect to the SQL server is
$conn = sqlsrv_connect( $serverName, $connectionInfo );

the error we get is
Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -49 [code] => -49 [2] => 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] => 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 ( [0] => IM002 [SQLSTATE] => IM002 [1] => 0 [code] => 0 [2] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified [message] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified ) )

The error suggests i need to install the ODBC driver, so i downloaded and installed both “Microsoft ODBC Driver 11 for SQL Server” and “Microsoft ODBC Driver 13 for SQL Server”. I could only install the 64-bit versions of both of those. Rebooted, tried again and still the same error.

I’m at a loss as to what else is required to make this work.

Can anyone suggest what I need to do to get this to work?

ErnieAlex · August 12, 2018, 3:47pm

I do not use MS Server 2008 anymore. Just seemed to not need it or use it. The link in your post shows tons of driver levels that need to be checked, but, did not see anything jump out on it. i did some research and found a note on StackOverflow about setting permissions that might help. It was posted in different places and from other people on other sites. They all used process-monitor to find out what the error was and for them, several different people, it appeared that the permissions for the “Network Service” was not correct. Perhaps you should run process-monitor and see what it shows for your app. Here is what they said:

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!

Not sure if it will help, but might…

chenks · August 12, 2018, 4:01pm

i’m not sure that will be the issue, but willing to check.

i have read that it might be down to having to use a specific version of PHP and a specific version of a DLL extension, but as of yet i’ve not worked out which or seen it documented which specific combination.

even if i just copy over the php folder from the 2003 Server to the 2008 server and configure IIS the same it doesn’t work.

ErnieAlex · August 12, 2018, 6:10pm

Well, does a simple PHP file just displaying something work? I mean is it just your connection string causing the problem? Or does ALL PHP fail?

Are you using “Windows Authentication”? That is the connection string for W.A. If you are using that it should work. But, if you are using permissions using passwords you need to do it this way:
NOT:
$serverName = “serverName\sqlexpress”; //serverName\instanceName
$connectionInfo = array( “Database”=>“dbName”);
$conn = sqlsrv_connect( $serverName, $connectionInfo);
BUT:
$serverName = “serverName\sqlexpress”; //serverName\instanceName
$connectionInfo = array( “Database”=>“dbName”, “UID”=>“userName”, “PWD”=>“password”);
$conn = sqlsrv_connect( $serverName, $connectionInfo);

Not sure if that helps. Hope so.

chenks · August 12, 2018, 6:24pm

yes PHP works fine, it’s just the SQL connection that isn’t working.

this is the test page that i am using (works on the 2003 server, doesn’t work on the 2008 server)

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

PHP Test <?php $ctsref = $_GET['callref']; ?>

<h1>Job Documents for <?php echo $ctsref;?></h1>

<?php	
$serverName = "SQLSERVERNAME";
$connectionInfo = array( "Database"=>"DBNAME", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo );
if( $conn === false ) {
    die( print_r( sqlsrv_errors(), true));
}

$sql = "select * from linked_documents where ld_link_reference = '$ctsref'";
$stmt = sqlsrv_query( $conn, $sql );
if( $stmt === false) {
    die( print_r( sqlsrv_errors(), true) );
}

$fileCounter = 0;
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
    $fileCounter++;
?>
    	
<p>
	
<?php
	$dt = $row['LD_DateTime'];
	$ldcat = $row['LD_Category'];
	$ldembed = $row['LD_Embedded'];
	echo $dt->format('d/m/Y H:i:s');
	echo " | ";
	echo $row['LD_Category'];
	echo " | ";
?>

<?php
if ($ldembed == "1" && $ldcat == "Photo") {
?>
    <a target="_blank" href="view_photo.asp?docid=<?php echo $row['LD_ID'];?>">VIEW PHOTO</a>

<?php    
} elseif ($ldembed == "0" && $ldcat == "Photo") {
?>
    <a href="view_docs.php?docid=<?php echo $row['LD_ID'];?>">VIEW PHOTO</a>

<?php    
} else {
?>
    <a href="view_docs.php?docid=<?php echo $row['LD_ID'];?>">VIEW DOCUMENT</a>
<?php   		    
}
?>

</p>

<?php
}
if ($fileCounter == 0) {
?><p>No documents have been uploaded.</p><?php
}
sqlsrv_free_stmt( $stmt);
?>

ErnieAlex · August 12, 2018, 6:49pm

Are these set in your live version? Also, where does it fail?
Did any of the “DIE” commands display an error?
Try adding in error reporting like this at the top of the test file:
error_reporting(E_ALL);
ini_set(“display_errors”, 1);
And, see if it shows any other errors. If PHP works, then this code should work if you entered the correct DBNAME, username and password. Let us know the errors you are getting.

chenks · August 12, 2018, 7:04pm

what do you mean by “live version”? i put in dummy data for the purposes of posting to this site - those aren’t the real database names and user/pass.

i posted the full error i get when trying to load a page connectin the database in the original post.

as i said, i belivee i have read somewhere that when using MS Server 2008 you need to use a very specific version of PHP as the others don’t work and MS ceased support for connected to SQL 2000.

you can see it working (or not working) here - https://ctspalmtop.uk/test.php
and the results of php.php is https://ctspalmtop.uk/php.php

ErnieAlex · August 14, 2018, 6:26pm

Okay, you did not tell us you altered the code to display it for us.

The error is:
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

I found this posted on Microsoft’s site. It is supposed to fix this error message:

Go to this page from Microsoft: http://www.microsoft.com/download/en/details.aspx?id=16978

Search the page for “Native Client”

The file you download should be called SQLNCLI.MSI

If you have PHP running on a 32-bit version of Windows , download the “x86 Package”, if you have 64-bit Windows, download the “x64 Package”

Install it on the computer running PHP, then the driver will work.

Not 100% sure, but, this might be the fix for you.

chenks · August 14, 2018, 6:52pm

i believe that’s for SQL 2008, not SQL 2000.
the confusion might be thtat i’m using Windows Server 2008 R2 and connecting to a SQL 2000 server.

i’m not sure why the error says SQL 2008 as that’s not what we have.

to clarify
php 5.3.8 on windows server 2008 R2 (64-bit).
SQL Server 2000 (32-bit).

ErnieAlex · August 14, 2018, 7:14pm

Well, you said you were upgrading to 2008, so I assumed… Me-Bad!
The bad news is: (From a note on StackExchange)

SQL Server 2000 is not supported on Windows Server 2008 . If you simply must run it, I suggest building a VM with Windows Server 2003 and installing SQL 2000 on that instead. If you mess up your Windows Server 2008 , don’t go crying to Microsoft. They will not be able to help you .Sep 15, 2012

Now, with that said. If you need a FULL SQL server, you would have to buy it. But, MS has an Express version that handles most small tasks. Per MS:

Microsoft SQL Server Express is a version of Microsoft’s SQL Server relational database management system that is free to download, distribute and use. It comprises a database specifically targeted for embedded and smaller-scale applications.

It is free and easy to download from their site. I guess your choices are move back to Windows Server 2003 or either install the 2008 Express version for free or purchase a newer SQL Server version.

Hope that helps…

chenks · August 14, 2018, 7:28pm

we defintely have confused wires here.

the SQL2000 server is not on the same server as 2008 R2 server - two separate boxes.
i did clarify this in my last post.

windows server 2008 r2 64 bit - this is the IIS server.
SQL server 2000 running on its own server - windows 2003 server 32-bit.

IIS running on the 2008 R2 server with PHP installed, with PHP connecting to the SQL 2000 database.

ErnieAlex · August 14, 2018, 7:55pm

Well, all that takes us full circle back to the top again.
You do not have the correct Microsoft SQL Server 2008 Native Client (SP1 or later) or the Microsoft SQL Server 2008 R2 Native Client ODBC Driver installed. Looking at your original post, I went to the link provided by MS and it says you need at least PHP 5.4 minimum.
And, it says for 5.4, you need PHP-SQLsrv version 3.2 (I think you have 5.3?)
And, for version 3.2, you need at least ODBC Driver version 11

Not sure! Their link is hard to follow. I think it appears to be the ODBC driver, or the driver for PHP under Windows. Looking a few clicks into that link in the error message, it clearly states that driver 5.4 will run under Windows 2008 v2 and with PHP 5.4, BUT NOT with PHP 5.3.8… So, you might have to upgrade to PHP 5.4 at least. Here is that link: (Read system requirements.) PHP Connection to SQL2000

chenks · August 14, 2018, 8:13pm

Not really full circle as I’m NOT using SQL 2008. the error may say that but that’s not the version of SQL in use.

The only 2008 version of anything in using is the IIS server.

SQL is on its own server and that is SQL 2000 running on Windows server 2003 32 bit.

ErnieAlex · August 14, 2018, 8:21pm

What I meant is you are using a SERVER 2008. IIS is a function on top of that.
Next, you are using PHP 5.38.

That is 100% okay from what I know.

BUT, to use that setup and call-out to a SQL 2000, you can NOT do it!
You need at lease PHP 5.4…

Hope that helps explain it better…

chenks · August 15, 2018, 7:14am

which one of the numerous 5.4.x builds though i wonder…

i downloaded php-5.4.0-nts-Win32-VC9-x86.zip, but for the life of me i can’t get it to work at all.
there isn’t an MSI installer like there was with v5.3.x.

i unzipped it to C:\php, amended the php.ini as per (https://docs.microsoft.com/en-us/iis/application-frameworks/install-and-configure-php-on-iis/install-and-configure-php), configured IIS.

ran php -info from the command prompt but i just get “could not open input file: o”
so i’ve gone from a working PHP with no SQL connection to no working PHP at all.

chenks · August 15, 2018, 10:09am

update

i couldn’t get 5.4.0 to work at all, so have reverted to 5.3.29 (via MSI installer).
i am now getting a different error message, which suggests i maybe further forward in getting this working

Array ( [0] => Array ( [0] => 28000 [SQLSTATE] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'sa'. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'sa'. ) [1] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 4060 [code] => 4060 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open database requested in login 'Siclops_CTS_Live'. Login fails. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open database requested in login 'Siclops_CTS_Live'. Login fails. ) [2] => Array ( [0] => 28000 [SQLSTATE] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'sa'. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'sa'. ) [3] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 4060 [code] => 4060 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open database requested in login 'Siclops_CTS_Live'. Login fails. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open database requested in login 'Siclops_CTS_Live'. Login fails. ) )

https://ctspalmtop.uk/php.php
https://ctspalmtop.uk/test.php

now it looks like it’s an authentication problem, but the user and password are defintely correct (when i put a wrong password in i get different error message saying the password is incorrect).

ErnieAlex · August 15, 2018, 1:06pm

Well, I looking at php.net, it appears you can only get 5.6 and up from their Windows download area.
It is here: PHP.net Windows Download
Then, run the php-win file for Windows. BUT, it appears it is connecting now, so don’t think this is needed!
Just appears to be a username or password issue now. It is saying user “SA” and your user is

Microsoft says with the SQLSTATE of 28000 and Error of 18456 you need to read this:
SA connection error 28000-18456 Not sure if this pertains…

chenks · August 15, 2018, 1:27pm

it doesn’t pertain in this instance, as the SQL server is already set what that link suggests.

ErnieAlex · August 15, 2018, 1:40pm

Well, not sure what to suggest. Seems you need to upgrade to 5.6? Hmmmm…

chenks · August 15, 2018, 1:51pm

but then that won’t support connecting to SQL 2000?
aren’t i limited to what version of PHP i can use when connecting to SQL 2000 ?