Kitz Forum

Internet => Web Hosting & Web Design => Topic started by: chenks on January 18, 2018, 04:16:35 PM

Title: MS server 2008 R2 PHP connecting to SQL2000
Post by: chenks on January 18, 2018, 04:16:35 PM
anyone here got any experience with windows server 2008 R2 and php connecting to an MS SQL 2000 database ?

migrating from a windows server 2003 php to 2008 R2 and the mssql connector for the current version of php are depreciated and now uses different ones.

it's been a bit hit and miss finding out exactly what php needs and installing that.
the 2008 R2 server has php 7.1.11 installed
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: kitz on January 19, 2018, 10:14:04 AM
Not me sorry:/
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: d2d4j on January 19, 2018, 10:39:34 AM
Hi

I would be surprised if php/ms packages were available for php 7

Mssql 2000 is considered too old and weak

You could try

GitHub - to see if packages are listed

Downgrade php 7 to php 5.6 if connectors still available (php 5.6 is still valid but coming up to EOL)

Upgrade mssql 2000 to free version of latest mssql (sorry the actual name escapes me at moment or purchase a standard license)

The connectors are available if it were an old php/ms os to newer mssql server but not the other way round.

I could be wrong though, so apologies in advance if I am

Many thanks

John
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: jelv on January 19, 2018, 12:24:17 PM
Why does it have to be MS SQL?
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: chenks on January 19, 2018, 03:19:59 PM
Hi

I would be surprised if php/ms packages were available for php 7

Mssql 2000 is considered too old and weak

there are, it's still very much supported
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: chenks on January 19, 2018, 03:20:58 PM
Why does it have to be MS SQL?

because that's what the production server is running.
it's not been upgraded because it works.
as they say "don't fix what isn't broken".
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: d2d4j on January 20, 2018, 09:54:53 AM
Hi chenks

Many thanks

Sorry, what is your question your asking then

Many thanks

John
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: jelv on January 20, 2018, 06:18:21 PM
as they say "don't fix what isn't broken".

On that basis, why change from windows server 2000?
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: chenks on January 20, 2018, 09:54:24 PM
The web server and the SQL server are not the same server.
IIS on 2000 has become limited in functions now required.

With regards to what question I was asking, thought it was quite clear?

Getting current version of PHP to connect to SQL 2000
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: watcher on January 20, 2018, 10:30:27 PM
Microsoft control the PHP drivers for SQL Server and they have decided that version 4.0 for PHP 7.x will not support SQL Server 2000. So it looks like as has already been suggested that you need to downgrade from PHP 7.1.1 to a version of PHP that will support SQL Server 2000. This would seem to be PHP 5.6.33 released on 4 January 2018. The only other alternative I can find is this one (https://blogs.msdn.microsoft.com/dbrowne/2015/05/15/how-to-create-a-linked-server-to-sql-2000-from-sql-2012-or-later/).
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: d2d4j on January 20, 2018, 10:33:29 PM
Hi

@watcher - thank you

@chenks - I thought that was the question until you stated they were supported

Many thanks

John
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: chenks on January 21, 2018, 12:16:20 PM
Microsoft control the PHP drivers for SQL Server and they have decided that version 4.0 for PHP 7.x will not support SQL Server 2000. So it looks like as has already been suggested that you need to downgrade from PHP 7.1.1 to a version of PHP that will support SQL Server 2000. This would seem to be PHP 5.6.33 released on 4 January 2018. The only other alternative I can find is this one (https://blogs.msdn.microsoft.com/dbrowne/2015/05/15/how-to-create-a-linked-server-to-sql-2000-from-sql-2012-or-later/).

is this not a solution?
https://github.com/Microsoft/msphpsql
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: watcher on January 21, 2018, 02:26:54 PM
No, this is another link to the Microsoft PHP drivers which use versions of the ODBC driver which do not support SQL2000. The earliest ODBC driver currently available is version 11 and according to Microsoft "...ODBC Driver 11 for SQL Server is a single dynamic-link library (DLL) containing run-time support for applications using native-code APIs to connect to Microsoft SQL Server 2005, 2008, 2008 R2, SQL Server 2012, SQL Server 2014 and Windows Azure SQL Database....". The definitive guide as to which versions of MS Server work with which PHP versions/drivers  appears on this Microsoft page (https://docs.microsoft.com/en-us/sql/connect/php/system-requirements-for-the-php-sql-driver).
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: chenks on January 22, 2018, 11:06:20 AM
it seems then the solution will be to use PHP 5.6.33 alongisde 2008 R2 server.

the current connection method on the 2003 server is

Code: [Select]
<?php
$serverName "server_name";
$connectionInfo = array( "Database"=>"dbname""UID"=>"user""PWD"=>"password");
$conn sqlsrv_connect$serverName$connectionInfo );
if( $conn === false ) {
    die( print_rsqlsrv_errors(), true));
}

$sql "select RIGHT(ld_filename, (CHARINDEX('\',REVERSE(ld_filename),0))-1) as filename, * from linked_documents where ld_id = '$ctsref'";
$stmt sqlsrv_query$conn$sql );
if( $stmt === false) {
    die( print_rsqlsrv_errors(), true) );
}

while( $row sqlsrv_fetch_array$stmtSQLSRV_FETCH_ASSOC) ) {
   
?>

Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: watcher on January 22, 2018, 01:29:44 PM
That code is using sqlsrv which is the ODBC based driver. The only issue potentially is whether or not 5.6.33 will work with SQL2000 because that version of PHP is still using the later Microsoft ODBC drivers. This link (https://stackoverflow.com/questions/19783613/how-to-connect-to-mssql-2000-from-php-5-3-and-up) says that it will not and that the latest version of PHP that will work with SQL2000 is 5.3.29, although there is a potential workaround for PHP 5.4.x. A Windows download of PHP 5.3.29 or the 5.4.x versions is available from here (http://windows.php.net/downloads/releases/archives/).
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: chenks on January 23, 2018, 07:50:08 AM
the version of php currently running on the old windows server is 5.3.8.
the version of php being used isn't important as long as a connection to the SQL server can still be made once migrated to the 2008 R2 server.
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: jelv on January 23, 2018, 09:04:09 AM
Support for php 5.x ends 31 December 2018 (http://php.net/supported-versions.php) (active support has already ended and it is now on security support only).  You look to be spending a lot of effort coming up with a solution which is not sustainable going forward.

There comes a point where you have to take a pragmatic decision as to whether the long term best solution is to do a full upgrade to current versions.

You said "don't fix what isn't broken" - I'd suggest it is broken.
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: watcher on January 23, 2018, 11:32:06 AM
Updating PHP from 5.3.8 to 5.3.29 to sustain this particular database software/server software combination is not really an issue unless the server is connected to the internet and is serving up web pages. Then there might then be a concern about security, depending on the risks that might or might not remain in PHP 5.3.29 which dates from August 2014. However the announcement of PHP 5.3.29 (http://php.net/releases/5_3_29.php) does say that it contains about 25 potentially security related fixes backported from PHP 5.4 and 5.5.

The need to sustain older combinations of software is I suspect a reality of life in many commercial and public organisations. Updating is not as straight forward as it might seem. Microsoft's updating process for SQL Server, also known as Discontinued Database Engine Functionality in SQL Server 2xxx, does include the renaming and/or removal of features as well as the addition of them, creating the risk of the need to substantially rewrite any existing code base.
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: chenks on January 23, 2018, 03:15:30 PM
Support for php 5.x ends 31 December 2018 (http://php.net/supported-versions.php) (active support has already ended and it is now on security support only).  You look to be spending a lot of effort coming up with a solution which is not sustainable going forward.

There comes a point where you have to take a pragmatic decision as to whether the long term best solution is to do a full upgrade to current versions.

You said "don't fix what isn't broken" - I'd suggest it is broken.

it's not broken though.
the SQL 2000 is rock solid and there is solely to be the backend for a specific application. that isn't broken and doesn't need touched.

the virtual web server is running on a piece of hardware that is being replaced and as such windows 2008 R2 will be the OS for the virtual servers running on that hardware - that is the sole reason for the change of the webserver.

Updating PHP from 5.3.8 to 5.3.29 to sustain this particular database software/server software combination is not really an issue unless the server is connected to the internet and is serving up web pages. Then there might then be a concern about security, depending on the risks that might or might not remain in PHP 5.3.29 which dates from August 2014. However the announcement of PHP 5.3.29 (http://php.net/releases/5_3_29.php) does say that it contains about 25 potentially security related fixes backported from PHP 5.4 and 5.5.

The need to sustain older combinations of software is I suspect a reality of life in many commercial and public organisations. Updating is not as straight forward as it might seem. Microsoft's updating process for SQL Server, also known as Discontinued Database Engine Functionality in SQL Server 2xxx, does include the renaming and/or removal of features as well as the addition of them, creating the risk of the need to substantially rewrite any existing code base.

almost all of the web stuff uses ASP and there is no issue with changing the OS there. only a small part uses PHP.
the web server is public facing and on the "internet". the SQL server is not public facing.

indeed, the need for touching the SQL server simply isn't there.

too many peope think that upgrading is a simple process, but it really isn't when you have to consider business continuity and the risks involved.
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: jelv on January 23, 2018, 04:06:24 PM
the virtual web server is running on a piece of hardware that is being replaced and as such windows 2008 R2 will be the OS for the virtual servers running on that hardware - that is the sole reason for the change of the webserver.

Words fail me! A piece of hardware that is being replaced by a virtual server running a version of windows server that is even further past end of life than php!
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: chenks on January 24, 2018, 07:46:42 AM
Words fail me! A piece of hardware that is being replaced by a virtual server running a version of windows server that is even further past end of life than php!

if words are failing you then feel free to not particpate any further in the thread.
you don't seem to be offering much anyway.
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: chenks on August 09, 2018, 04:05:43 PM
I'm back looking at this again after a period of being distracted with other things.

I have a clean install of Windows 2008 R2 64-bit.
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.

I have a small script that attempts to connect to the SQL server (SQL2000 running on windows 2003 32-bit server), and display a few rows of data.

Code: [Select]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html lang="EN">

<head>
<title>PHP Test</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>

<body>

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


<div class="popup-content">

<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_rsqlsrv_errors(), true));
}

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

$fileCounter 0;
while( $row sqlsrv_fetch_array$stmtSQLSRV_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);
    
?>


</div>

</body>
</html>

When i load the page (with the appropriate query string) i get the following error:

Code: [Select]
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.

Looking back on the thread, it seems I may need to change the PHP version?
We have the potential to change the Windows 2008 R2 to Windows 2012, but the SQL server will always remain SQL2000 32-bit for the long-term foreesable future.
Title: Re: MS server 2008 R2 PHP connecting to SQL2000
Post by: chenks on August 19, 2018, 04:49:53 PM
well i finally got this working

php 5.3.29 got it working along with an IIS extension called PHP manager which checks the php.ini for any errors or config issues. it has a "fix" option which corrects any potential issues.

the combination of those two resulted in it working.