Install PHP 5.4.6 on IIS 7 to work with MS SQL 2008

Instal PHP 5.4.6 on IIS 7 to work with MS SQL 2008

I decided to write this post because I have not found a simple and clear description for installing PHP on Windows, and setting this not very common combination to work with MS SQL 2008.

We are currently working on porting the largest system ever created in Deep Shift Labs from Windows 2000 and MS SQL 2000 to Windows 2008 and MS SQL 2008. I want to speak briefly about the history of this system and why we actually needed to install PHP on Windows. The system is about 800K lines of PHP code and it has been helping people to solve their business needs for the last 10 years. This system is designed for one of our customers. The first few years, the system worked on FreeBSD, Apache and PostgreSQL. Then the client’s company went public, and they needed to switch to commercial software. Sometime in 2004, we have been able, thanks to the fact that we originally used ADODB, to move, with a little effort, from FreeBSD/Apache to Windows 2000/IIS 5 (PHP runs as an ISAPI module). Finally, in 2006 we moved the then small database, both in structure and in size, to MS SQL 2000 from PostgreSQL.

The most difficult part in the distant 2006 was to find a driver which is bug free with MS SQL. We had tried everything supported by ADODB back then, and, in the end, we decided in favor of php_dblib.dll suggested and maintained by Frank Kromann. Frank was maintaining MS SQL extensions in PHP project and helped us immensely. The php_mssql.dll driver was using ntwdblib.dll from Microsoft, that does not work with dates and long texts properly. php_dblib.dll used dblib.dll from FreeTDS and we have not found any problems in it and started using it.

And now came the moment when the customer wants to move to the operating system and database that have Microsoft support (Windows 2000 and MS SQL 2000 are not supported anymore) and such a move, in general, is a common requirement in large enterprises.

Naturally, in the ADODB 4.93 we have been using since 2006, there is no mssqlnative driver support and so the challenge was set as follows:

- Move the database from MS SQL 2000 to MS SQL 2008
- Move the web server from Windows 2000 and IIS 5 (ISAPI) on Windows 2008 and IIS 7 (FastCGI)
- Upgrade PHP 5.1.6 to PHP 5.4.6
- Upgrade ADODB from 4.93 to 5.17 with transition to the new driver ‘mssqlnative’
- To test the system, including the two replications processes to our database from servers running MS SQL 2000 and MS SQL 2005.

This is one of the installation methods (obviously the one we used).

1. Create C:\PHP and C:\tmp folders
2. Load non thread safe PHP version recommended for use here and here. The file will contain _nts_ in the name, for example – php-5.4.6-nts-Win32-VC9-x86.zip (latest version available at the time of writing this post) from the site http://windows.php.net/download/.
3. Unpack it in C:\PHP
4. Load latest drivers from MS SQL Microsoft (file SQLSRV30) and extract it to C:\PHP\ext
5. Download the PHP web installer from Microsoft. They offer two versions and I used the latter one available with PHP 5.3.13. Install it.

You can ask a reasonable question why we install 5.3.13 to switch to 5.4.6. There are two reasons. First – I did not manage to set PHP 5.4.6 and manually configure IIS. Despite my attempts to let PHP see its php.ini file inside the C:\PHP I failed to do so. According to all the manuals you need to create a variable PHPRC assigning the path to php.ini, but in my case, someone has done something wrong.

PHPRC did not work for me

Since PHP couldn’t see the configuration file, MS SQL driver was not loaded. Being stuck at this point for a couple of hours I asked for help in the forum and started looking for alternatives, waiting until America wakes up and sees my ‘SOS’ message in the forum. It was then that I accidentally came across a site where I found the alternative I was looking for. Maybe I would not have started using the installer if it had not offered to set this magical and wonderful PHP Manager I could not resist at the thought of trying. In the end I found that the PHP Manager allows adding PHP 5.4.6, making its php.ini file perfectly visible.

Microsoft PHP web installer

It installs all the necessary components as well. It took some 10 years for Microsoft to add some really cool things to run PHP on Windows – here you have MS SQL driver and the installer which adds a special PHP Manager inside IIS, and even Windows Cache PHP accelerator for IIS.

PHP Manager

After installation, we have PHP 5.3.13 installed in the Program Files and PHP Manager inside IIS.

PHP Manager dashboard

As you can see from the PHP Manager, you can control the configuration parameters from php.ini, and the extensions are shown in a separate page. Also here we see what php.ini file is being used and where the error logs are written. There is quick access to the FastCGI handler created by the installer. It is possible to add other versions of PHP and switch between them. We have not tested if it is possible to use PHP Manager to set different PHP versions on different virtual hosts. I think this and much more, such as to use different php.ini files for different virtual hosts can be done manually using the excellent article by Ruslan Yakushev from Microsoft. We just needed to add PHP version 5.4.6 to “live happily ever after”.

Adding PHP 5.4.6

Now we can add PHP 5.4.6, which we put into in C:\PHP. After adding, we see that IIS now uses a new version of PHP, and that PHP Manager created a new handler, which is used (we do not see it here), but we can check with phpinfo(), which PHP manager can dynamically generate for us (how-cool-it-is!).

PHP 5.4.6 can see php.ini now

Now it is time to look at PHP extensions (modules). I removed mysql and added both versions of the MS SQL driver sqlsrv and pdo_sqlsrv. I forgot to check if IIS sees our changes immediately. I am used to executing “iisreset” after changing configuration.

PHP Manager extensions UI

PHP Manager offers two pre-defined configuration options for error handling for development and production.

PHP Manager error reporting UI

When I changed the path to the error log, PHP Manager did not see my changes, and I had to restart the IIS. I really got used to the old fashioned way for a folder for uploaded files, session and logs and store them in PHP C:\tmp. No wonder our periodic scripts are in C:\etc. Options ‘Configure error reporting’ and ‘Set runtime limits’ are kept separately in PHP Manager for convenience only, as they all (except for the extension modules) are available from the ‘Manage all settings’ panel.

PHP Manager points to a wrong location after PHP version switch

If you need to parse HTML files with PHP – create one more mapping by looking at parameters of existing PHP mapping you already have.

Setting PHP to handle HTML files (if needed)

Now we can try to connect to MS SQL with the driver only and via ADODB. First you need to make sure the server is configured to allow TCP/IP as well as port 1433 is open on the Windows firewall.

Checking SQL Server networking

In my case it was not so. If you cannot connect from PHP, it is recommended to install SQLCMD and use it for testing. For example, here we will find the required version of SQLCMD.

I want to draw your attention to the fact that the new Microsoft driver returns datetime as PHP DateTime:: object. But we use ADODB, which does this little trick converting this object into a string on line 794 of adodb-mssqlnative.inc.php file:

$this->fields[$key] = $value->format("Y-m-d\TH:i:s\Z");

Thus, returning us the string ’2012-08-22T23:42:22Z ‘instead of the usual ’2012-08-22 23:42:22′ and so for backward compatibility, you can correct adodb-mssqlnative.inc.php this way:

if(is_array($this->fields)) {
    foreach($this->fields as $key=>$value) {
        if (is_object($value) && method_exists($value, 'format')) {//is DateTime object
	    //$this->fields[$key] = $value->format("Y-m-d\TH:i:s\Z");
            //DSL DateTime output format fix
            $this->fields[$key] = $value->format("Y-m-d H:i:s");
	}
    }
}

Here is an example, where you can find connection and query snippets which use Microsoft driver directly and via ADODB.

<?php
echo "<B>Direct driver connection</b><BR>";
$connectionInfo = array(
      "UID"=>'aws_user',
      "PWD"=>'Q1w2e3r4t5',
      "Database"=>"AWS_Database"
);
 
/* Connect using SQL Server Authentication. */
$conn = sqlsrv_connect('192.168.3.86, 1433', $connectionInfo);
if( $conn === false )
{
   echo "Unable to connect.</br>";
   die( print_r( sqlsrv_errors(), true));
}
 
 
$tsql = "SELECT GETDATE() AS today";
$stmt = sqlsrv_query( $conn, $tsql);
if( $stmt === false )
{
   echo "Error in executing query.</br>";
   die( print_r( sqlsrv_errors(), true));
}
 
$row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
echo "Today is ".$row['today']->format('Y-m-d H:i:s')."</br>";
 
 
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
 
ini_set( "include_path", ini_get( "include_path" ) . ";" . $_SERVER["DOCUMENT_ROOT"] . "/include" );
require_once("adodb5\adodb.inc.php");
$user     = "aws_user";
$password = "Q1w2e3r4t5";
$server="192.168.3.86, 1433";
$database = "AWS_Database";
 
echo "<B>ADODB MSSQL Native connection</b><BR>";
$adoConnection = ADONewConnection('mssqlnative');
//$adoConnection->debug = true;
$adoConnection->Connect($server, $user, $password, $database);
 
 
$rs = $adoConnection->Execute($tsql);
 
echo "Today is ".$rs->fields['today']."</br>";
$adoConnection->Close();
?>

In conclusion, we want to say that the Microsoft site provides a lot of useful information for running PHP on IIS and using Microsoft drivers to connect to MS SQL. For example, here you will find the entry to the forum PHP/IIS, and other useful links. Forums dedicated to PHP driver for MS SQL databases are located here. I used them more-than-once. They are active and guys helped me a lot.

Finally a couple of handy articles on how to configure PHP on IIS:
- Enable Per-Site PHP Configuration on IIS 7 and IIS 6.0
- Using FastCGI to Host PHP Applications on IIS
- Using FastCGI to Host PHP Applications on IIS 6.0

Igor

Print this post | Home

12 comments

  1. Sylvia says:

    Я так понимаю что для этого нужно иметь полный доступ к серверу, так? Просто у меня именно windows-хостинг и эта проблема надоела уже)))

  2. Igor Kryltsov says:

    Что именно надоело? :)

  3. ivaseg says:

    Как у тебя получилось установить Microsoft Drivers 3.0 for PHP v5.4 for SQL Server in IIS через Web Platform Installer? У меня на Windows 2008 Server и MS SQL 2008 и IIS7 через Web Platform Installer не захотело установиться, выдало ошибку:
    Невозможно установить этот продукт, поскольку продукт, от которого он зависит, не был успешно установлен.

    И вот ошибки зависимостей:
    Product: Microsoft SQL Server 2012 Native Client — Installation of this product failed because it is not supported on this operating system. For information on supported configurations, see the product documentation.

    Так как у тебя Microsoft SQL Server 2012 Native Client установился, если установщик пишет, что он не поддерживается Windows 2008 Server?

  4. Igor Kryltsov says:

    Я не устанавливал “Microsoft Drivers 3.0 for PHP v5.4 for SQL Server in IIS через Web Platform Installer”

    Я загрузил с сайта Microsoft инсталлятор PHP 5.3.6 и установил его. Далее я просто добавил 5.4. В посте параграф начинается со слов “Теперь мы можем добавить PHP 5.4.6, …..”.
    Подготовительная работа для 5.4.6 описана выше в посте:

    1. Создаём папки C:\PHP и C:\tmp
    2. Загружаем non thread safe PHP версию, рекомендуемую к использованию тут и тут. Файл будет содержать _nts_ в имени, например – php-5.4.6-nts-Win32-VC9-x86.zip (последняя доступная версия на момент написания поста) с сайта http://windows.php.net/download/.
    3. Распаковываем её в C:\PHP
    4. Загружаем последнюю версию драйверов MS SQL от Microsoft (файл SQLSRV30) и распаковываем в C:\PHP\ext

    Видите пункт 4? Просто распаковали и положили в папку. Нет никакого Web Platform Installer-а

  5. ivaseg says:

    Просто у вас на скриншоте как раз и показан Web Platform Installer http://www.deepshiftlabs.com/dev_blog/wp-content/uploads/2012/09/php_installer.png

    ЗЫ: проблема решена, нужно было установить SP2 для Windows 2008 Server, т.к. на SP1 или вообще без сервис пака, не поставить. Кстати, так и не понял, какой резон было ставить 5.3.6, чтобы потом поставить 5.4.6? Объяснение я то понял, но вопрос в том, почему именно 5.3.6, почему не 5.4.0 (а потом уже 5.4.6 поставить)?

  6. Igor Kryltsov says:

    Про 5.3.6 в посте ни слова.

    Web Platform Installer устанавливает всё в этих двух версиях (это прямо сейчас на сайте Майкрософт)- http://www.microsoft.com/web/platform/phponwindows.aspx PHP 5.2.17 и 5.3.5.
    Когда я устанавливал последняя доступная версия Web Platform Installer была 5.3.13, а мне нужна была 5.4. Вот я и описал процесс установки 5.3.13 из Web Platform Installer и добавления 5.4 постфактум.

  7. Igor Kryltsov says:

    As for: “Product: Microsoft SQL Server 2012 Native Client — Installation of this product failed because it is not supported on this operating system. For information on supported configurations, see the product documentation.”

    If you Google it you will see – http://msdn.microsoft.com/en-us/library/cc280356.aspx

    However as usual with Microsoft you will not find SP2 as a requirement but below in comments:
    ——————
    “SQL Server Native Client 2012 can be installed with Windows Server 2008 SP2″

    - Windows Server 2008 – Failed
    - Windows Server 2008 SP1 – Not tested yet
    - Windows Server 2008 SP2 – Success!
    ——————-

    Basically I updated my Win 2008 R2 before installation and I did not hit this problem and as a result did not describe it in my post.

  8. Igor Kryltsov says:

    “Про 5.3.6 в посте ни слова.” – беру свои слова обратно. Если бы вы не написали – ошибка бы осталась. Спасибо. Исправил пост. Устанавливал не 5.3.6, а 5.3.13, которая была последней доступной из Web Installer во время написания поста.

  9. ivaseg says:

    кстати, весь процесс можно чуток ускорить, если ставить все через Web Platform Installer, в конечном счете нам нужно просто отдельно потом добавить себе версию ПХП посвежее, и не нужно ничего скачивать вручную не нужно, все почти на автомате делается.

  10. Igor Kryltsov says:

    кстати, весь процесс можно чуток ускорить, если ставить все через Web Platform Installer,

    Так так и было сделано. Что вы видите в моём посте лишним?

  11. micaz says:

    best of best blog
    step by step and detail picture
    thank you

  12. Todd says:

    Thanks for finally writing ɑbout Install PHP 5.4.6
    оn IIS 7 tօ ѡork with MS SQL 2008