Getting PHP 5x, IIS, and MSSQL 2005 to play nice

Getting PHP 5x, IIS, and MSSQL 2005 to play nice

by Ed Russell -
Number of replies: 17

I have been trying to get Moodle installed on my server to evaluate it. I am not very familiar with PHP as I am from the MS .Net realm.

After searching through countless posts and creating a few posts of my own on this forum (thank you to all responders), I was never able to find the precise solution to get past the PHP/DataBase connecting portion of the Moodle install.php.

However, I finally got this to work and I will now document what my particular problems were and how I solved them, in the hope that this may help someone out there.

My Environment: Windows 2003 Enterprise Server, IIS, PHP 5.2.5, MSSQL 2005

The entire crux of the problem is getting PHP to run properly on the IIS server and to get PHP to communicate with MSSQL.

Getting PHP Configured and Running Properly

The biggest problem is that when I installed PHP 5.2.5 I installed it in the default directory of C:\Program Files\PHP. It went through the install ok and IIS recognized the extension just fine giving the impression that all went well with the install. Of course this was not the case. I made changes to the php.ini file as per instructions, but the problem is that the PHP installer configures PHP to look for the php.ini file in the C:\WINDOWS directory, not in the installed directory where it actually is. So I needed to copy the php.ini file to the C:\Windows directory. What’s more (and this is important) is that when you use the PHP Installer to add extensions, it will always modify the php.ini file that is in the install directory and not the one in the Windows directory. So if you make manual changes to the php.ini file in the Windows directory, make sure that you copy the new file back to the PHP install directory and vice versa.

I’m pretty confident that there must be some type of environment variable that can be set to have PHP look for the php.ini file in the PHP install directory, but at this point I’m just trying to get it working to conduct an eval. So if anyone out there knows how to set this, it would probably help out a lot of newbies like myself and make the installation process a little less painful.

I highly recommend installing PHP to a simple directory off of the root such as C:\PHP and NOT in the Program Files directory. It is worth the effort to uninstall PHP from the Program Files directory and reinstalling it in a C:\PHP directory.

I also had to make sure that the extension_dir parameter in the php.ini file was set correctly. Assuming now that PHP has been installed in the C:\PHP directory, if you install any extensions using the PHP Installer, it will create an ext directory (C:\PHP\ext) and place the extension dlls in there. You must make certain that the extension_dir parameter in the php.ini file reflects the correct location (i.e. extension_dir=c:\PHP\ext). Otherwise, PHP will not be able to find the dll files and the extension will not load.

Lastly, when I ran the install.php file to attempt to install Moodle, the page loaded fine and asked me what language I wanted to use. When I clicked Next, I got an error that read “The Website cannot display the page” (HTTP 500). What had happened was the PHP Installer configured the upload_tmp_dir and the session.save_path parameters in the php.ini file to my user specific temp directory. PHP does not have access to this directory and when it needs to use them this error occurs. I fixed this by creating system accessible shared directories and then set these parameters to the appropriate directory (e.g. D:\Share\Temp\php\upload and D:\Share\Temp\php\session respectively). Again, there is probably a better prescribed way of setting up these directories and anyone out there who has a better suggestion, please do so.

Don’t forget that you must restart the web server every time you make a change to the php.ini file in order for the changes to take effect.

As a final note to this post, if you run into similar troubles with PHP, I recommend temporarily turning on the display_errors and the display_startup_errors parameters (i.e. display_errors=On and display_startup_errors=On respectively). Once you’ve solved your problems you should turn these parameters back off.

Average of ratings: -
In reply to Ed Russell

Re: Getting PHP 5x, IIS, and MSSQL 2005 to play nice

by Ed Russell -

P.S. I forgot to mention that I used FreeTDS. Once it was installed properly in PHP, it worked like a champ.

I see a lot of people suggesting ODBC or the new MSSQL/PHP driver from Microsoft, but neither of these methods are officially sanctioned yet (as indicated when you attempt to install).

Average of ratings: -
In reply to Ed Russell

Re: Getting PHP 5x, IIS, and MSSQL 2005 to play nice

by Navjot Singh -
What about with SQL 2000??.....its not working with it..someone please help me out!!
Average of ratings: -
In reply to Navjot Singh

Re: Getting PHP 5x, IIS, and MSSQL 2005 to play nice

by Mark Hilliard -

Navjot,

I have NOT tried to use MSSQL 2000 so I am ONLY passing on info I have found here... 

Please look on your own, but for what its worth I have seen several posts (at least 4-5) in this forum from experienced people I would trust in Windows-Moodle forum who specifically said that MSSQL 2000 was NOT supported.

They consistently advised people NOT to use it.

Mark

Average of ratings: -
In reply to Mark Hilliard

Moodle Installation on Vista

by Aranganathan B -

Hi,

We have installed PHP on Vista sucessfully. We have to know the procedure for installing Moodle on MSSQL using IIS 7.0 in Vista. Could you please give me the steps?


Thanks,

Aranganathan

arangu_mca@yahoo.co.in

Average of ratings: -
In reply to Ed Russell

A server error that affects your login session was detected. Please login again or restart your browser

by Sankar R -

Hi Ed Russell

     We are new to moodle. we have to install the moodle using IIS, MSSQL and PHP. First we downloaded the moodle 1.9 version and installed in windows Xp using freeTDS method and it created a config file and its sucessfully created a database in SQL Server after we browse the localhost we got a main page and there we cant able to login as a admin and also a guest if we are going to index.php file the error message saying "A server error that affects your login session was detected. Please login again or restart your browser." and also login page also one message highlighting "Your session has timed out. Please login again." like this we serach this error in forum but there i got a permission rights and enable cookies and session. But its enabled the data dictionary having session data. but we tried all posibilities But it didnt worked. Can u help me what will be the next step?

Thanks, By Sankar.R

 

Average of ratings: -
In reply to Ed Russell

Re: Getting PHP 5x, IIS, and MSSQL 2005 to play nice

by Mark Hilliard -
Ed Russel,
Can you tell me if you use CGI or FastCGI with your Windows Moodle setup on IIS? We are trying to setup a large new Moodle site on 100% windows (MSSQL 2005, IIS 7, Moodle 1.9 and FreeTDS.

We are getting corrupted data sent from the HTMLarea editor into the MSSQL database. Anytime data with # and Hex number (like Text color) is used the color data string in the SQL server seems to be corrpted.

Since your setup seems stable, just wondered what you used with IIS the CGI or the FastCGI? Thanks for anything you can share on your win-Moodle.

Mark Hilliard
Mpls, MN
Average of ratings: -
In reply to Mark Hilliard

Re: Getting PHP 5x, IIS, and MSSQL 2005 to play nice

by Jon Ofstad -

Mark,

We have narrowed the issue to the FreeTDS conversion and the escape function prior to any double quote (").  The html editor is not the offending party.  It is the conversion action between the Moodle GUI and the SQL box.  I'm thinking you may want to look at the stripslashes function in the various modules that are being affected.  Gordan Bateson and Alastair Hole had a rather interesting forum exchange on this issue (their's being being centered around the HotPot module).

The hang of the server (eating 50% of the processor and not releasing) within the GUI on the admin of the html editor oversight may or may not be related.  This, as is our assumption, is most like the fore-discussed FastCGI issue.

Keep us posted on your explorations!

Jon (and Tom, Quinn, Andrew...and various others)

Average of ratings: -
In reply to Jon Ofstad

Re: Getting PHP 5x, IIS, and MSSQL 2005 to play nice

by Gordon Bateson -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers
Hi everyone,
the thread on the HotPot forum was called quote escaping corrupts crossword

As mentioned on the above thread, I discovered that the bahavior of PHP's stripslashes function was modified when I installed MSSQL (+FreeTDS). To workaround the problem, I created a new function "hotpot_stripslashes()", in "mod/hotpot/lib.php", which always strips slashes on single-quotes, double-quotes and backslashes.

The standard Moodle function "stripslashes_safe()", in "lib/weblib.php", does something similar, but it also detects if PHP's "magic-quotes-sybase" setting is enabled, and in that case will do the appropriate conversion on single-quotes only.

In short, I concur with the idea that PHP's stripslashes may not be doing the right thing, particularly when MSSQL is installed, and you may have more success using one of the functions described above.

By the way, I now have Windows 2003, IIS 6.0, PHP 5.2.6 (via FastCGI), MSSQL 2005 (+FreeTDS) Moodle 1.8, 1.9 and 2.0 (=HEAD). I have not noticed any data corruption connected with color in the HTML editor.

For example, the following HTML was not corrupted when I entered it into a Web-page resource:
<span style="color: #0000ff; background-color: #ffff00">blue text on yellow background</span><br /><br /><span style="color: #ffffff; background-color: #000000">white text on black background</span>
regards
Gordon
Average of ratings: -
In reply to Gordon Bateson

Re: Getting PHP 5x, IIS, and MSSQL 2005 to play nice

by Jon Ofstad -

Gordan,

Do you have an example of the function code (hotpot_stripslashes) that you are using or could you point me to where you have posted the edited function?

Thanks, in advance, for your assistance!

Jon

Average of ratings: -
In reply to Jon Ofstad

Re: Getting PHP 5x, IIS, and MSSQL 2005 to play nice

by Gordon Bateson -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers
Hi Jon,
the hotpot_stripslashes function is available in any standard Moodle installation since Moodle 1.6 (in "mod/hotpot/lib.php").

For your reference, here it is pasted below:
function hotpot_stripslashes($str) { // strip slashes from double quotes, single quotes and back slashes static $escapedchars = array('\\\\', '\\"', "\\'");
 static $unescapedchars = array('\\', '"', "'");
 return str_replace($escapedchars, $unescapedchars, $str);
}
hope that helps
Gordon
Average of ratings: -
In reply to Gordon Bateson

Re: Getting PHP 5x, IIS, and MSSQL 2005 to play nice

by S C -

Gordon,

Would it be possible for you to list the steps that were used during your install process for the environment you have listed in your response. 

We would like to use WinXP or Win2K3, IIS, PHP & MSSQL 2005 and would like to setup moodle. Most links we have read so far within moodle documentation are going in circles. I will give it few more tries, but it would be a great help to other users using this environment if you would list the steps and configuration tweaks.

Thank you in advance for your response and help,

Sudhindra

Average of ratings: -
In reply to Ed Russell

Re: Getting PHP 5x, IIS, and MSSQL 2005 to play nice

by atakan kesler -

i just installed moodle on sql server 2005 , vista , IIS 7.0

My steps :

1 . download and Install PHP 5.2.6 with ISAPI choice. you should load extensions which are curl, gd2,mssql,xml-rpc.

2. IIS , Handler Mappings : should set ISAPI dll(module mapping), and php (script mapping)

3. download freetds and move the file to the php/ext folder.

4. add the "php_dblib.dll" at the dynamic section of php.ini file

5. comment the mssql.dll in php.ini file.

6.[global]
     host = 127.0.0.1 ( for same machine)
     port = 1433
     client charset = UTF-8
     tds version = 7.0
     text size = 20971520

write these file called freetds.conf on the "c:\" folder

7. Create database on SqlServer 2005 SP2 and options ansi nulls=enabled and quoted = true

8. give the one user owner rights on the database.

9 . change the mssql.textsize and mssql.textlimit to 20971520 in php.ini

10. copy the php.ini to the windows folder and they should be same.

i made these steps and it worked.

Average of ratings: -
In reply to atakan kesler

Re: Getting PHP 5x, IIS, and MSSQL 2005 to play nice

by Jeff Hague -

Hello! I have followed the instructions here; http://docs.moodle.org/en/Installing_MSSQL_for_PHP

to try to get Moodle 1.9.x, PHP5.2.6, IIS7 and SQL2005 working on a Windows Server 2008 64bit box. I have not yet installed Moodle because I get the following error;

500 - Internal server error.

There is a problem with the resource you are looking for, and it cannot be displayed.

When I try to connect to the test.php file mentioned in the instructions for testing connection to the database. PHPinfo displays correctly so I am confident that IIS and PHP are working properly and it indicates that I am using the FreeTDS library to connect to MSSQL.

I have a few questions about your steps;

In step 1, what do you mean by  Install PHP 5.2.6 with ISAPI choice." I didnt see ISAPI choice as an option. You also say to "load extensions which are curl, gd2,mssql,xml-rpc" but then in step 5 you say "comment the mssql.dll in php.ini file." What then do you mean by "load extensions which are curl, gd2,mssql,xml-rpc"? 

What about step 2? IIS Manager shows ISAPI as disabled and there doesnt appear to be a way to enable it.

Any help you can offer would be gratefully appreciated!

Jeff

Average of ratings: -
In reply to Jeff Hague

Re: Getting PHP 5x, xampp, and MSSQL 2005 to play nice

by Quy Nguyen -

Hey, guys! I have followed the instructions here; http://docs.moodle.org/en/Installing_MSSQL_for_PHP

to try to get Moodle 1.8.x, PHP5.2.9, Xampp and SQL2005 working on a Windows Vista. I have not yet installed Moodle because I get the following error:


Error: Database connection failed.

It is possible that the database is overloaded or otherwise not running properly.

The site administrator should also check that the database details have been correctly specified in config.php


how can i fix? Any one can help!

my email : quynt.hut@gmail.com

Thanks in advance

Average of ratings: -
In reply to atakan kesler

Re: Getting PHP 5x, IIS, and MSSQL 2005 to play nice

by Luis de Vasconcelos -

Step 10 isn't necessary. Leave the php.ini file in your PHP folder and create the following Registry Key:

[HKEY_LOCAL_MACHINE\SOFTWARE\PHP]
"IniFilePath"="C:\\PHP"

This key will tell Windows where to find your php.ini file when it starts up.

You should only have ONE php.ini file on your computer/server.

Average of ratings: -
In reply to Ed Russell

Re: Getting PHP 5x, IIS, and MSSQL 2005 to play nice

by moon ali -

If the Environment: xp, IIS, PHP 5.2.5, MSSQL 2005

There will be any difference

Average of ratings: -