Installing Moodle with MSSQL, Apache and PHP

Posted: June 19, 2012 in Uncategorized
Tags: , , , , , , ,

Recently I installed Moodle 2.2 with Sql Server 2008  and spent one week installing and uninstalling it till I got it working. I compiled my notes and have put it here in the hope that someone will benefit from it. 

Installing MSSQL for PHP – Modified  from MoodleDocs

 Introduction

This short manual is suitable if you are trying to run Moodle using the SQL*Server (MSSQL) RDBMS. Steps detailed below must be performed before installing Moodle itself.

Some of this may also apply if you wish to access an MSSQL server for external db authentication/enrollment.

First of all, minimum required version of MSSQL has been stabilised to MSSQL 2005 (v.9), although it might work with MSSQL 2000 (v.8) or newer. All the development process has been performed using MSSQL 2005 and there could be some unknown problems with previous releases.

While PHP comes with one, more or less, standard extension (mssql) that provides access to MSSQL databases, early we found some hard limits on it. Basically such default extension has some limits that prevent us to use it at all (you can find more info about these problems here).

So, in order to allow PHP (i.e. Moodle) to access to MSSQL DBs properly we have to install a mssql extension alternative to save us from the problems related above. See the sections below for details about the various options.

Installation overview

 

1. Get MSSQL Server installed and running.

Make sure that you choose mixed authentication (Windows and local accounts) to keep things simpler later. You’ll be asked to define the “sa” account password (it’s the default System Administrator account which has full access to all databases by default).

2. Make sure MS SQL Server can accept incoming TCP/IP connections on port 1433 (the standard one).

You might need to explicitly allow this in your Windows firewall (see the Control Panel). You may also need to edit options in the :SQL Server Configuration Manager -> Network Configuration -> Protocols -> TCP/IP enabled

3. Open the “SQL Server Management Studio” and create a new empty database. If you are using the “sa” account then you don’t need to do anything else here. Created a database id of moodleuser and give it all the permissions, otherwise it won’t be able to create tables in the moodle database.

4. Configure these settings in your created (and still empty) database:

  • ANSI NULLS Enabled = true (ALTER DATABASE xxxx SET ANSI_NULLS ON)
  • Quoted Identifiers Enabled = true (ALTER DATABASE xxxx SET QUOTED_IDENTIFIER ON)
  • Moodle 2.x only: Row Versioning Enabled (ALTER DATABASE xxxx SET READ_COMMITTED_SNAPSHOT ON) – To set

5. Get PHP installed with a web server. This is a multiple step process.

Download from http://httpd.apache.org/download.cgi. Find the Win32 Binary (MSI Installer) link and save the file to a temporary folder. We downloaded file httpd-2.2.22-win32-x86-openssl-0.9.8t in the downloads directory.

Installing Apache

The apache installer will create a sample configuration file (httpd.conf) as well as installing apache as a service.

  • Run the installer program and accept the license agreement.
  • At the Server Information box, enter the details as follows:
    • In the Network Domain box, enter the domain that the server will run on (e.g. mymoodle.com). If you are intending to run Moodle locally without internet access, leave this blank.
    • In the Server Name box, enter the Fully Qualified Domain Name of the server, e.g. www.mymoodle.com. If you are running Moodle locally, just enter the name of the server.
    • In the Administrator’s email address box, enter a valid email address.
    • Select port 80 or port 8080 (if you are installing a test). Selected 80
  • At the Setup Type, choose Typical Setup.
  • At the Destination Folder screen, choose the folder to install into (e.g. c:\apache2) or accept the default. Selected e:\server\apache
  • Click Install to start the installation.

Once the installation has finished, you should see the Apache monitor icon in the system tray.

Test the configuration file

Test the configuration as follows: Start -> All Programs -> Apache HTTP Server -> Configure Apache Server -> Test configuration. Follow the on-screen help to fix any configuration file problems.

Save a copy of VC9 x86 Thread Safe (2012-May-08 18:50:32) – php-5.3.13-Win32-VC9-x86.zip in downloads folder. Get the zipped file and not the installer as zipped folder has more files that you will need. Look for additional documentation in http://www.php.net/manual/en/install.windows.manual.php

Extract the files. Extracted in directory e:\server\php

Change the php.ini file. Copy the file from php.ini-production to php.ini.  

Following lines changed in php.ini file after php installation

display_startup_errors = On

extension_dir = “e:\server\php\ext” -changed after php installation

intl.error_level = E_WARNING

mssql.textlimit = 20971520

mssql.textsize = 20971520

Following were uncommented as suggestedby Moodle during Moodle install. You could uncomment them at this point.

extension=php_curl.dll

extension=php_gd2.dll

extension=php_intl.dll

extension=php_mbstring.dll

extension=php_openssl.dll

extension=php_soap.dll

extension=php_xmlrpc.dll

intl.default_locale = en_utf8

Following line was added after mssql extension was downloaded.

extension=php_dblib.dll

6. Choose one of the following specific sections from Microsoft Drivers for SQL Server for PHP for your server to install the mssql extension alternative installed and running properly on your PHP box.

Microsoft Drivers for SQL Server for PHP

In July 2008 Microsoft released a new SQL Server Driver for PHP. This is a PHP extension that allows PHP scripts to read and write data on Microsoft SQL Server databases and it overcomes the problems with the native SQL Server extension that was previously bundled with PHP.

When using IIS it is strongly recommended to use the official Microsoft PHP installer from http://php.iis.net/, it should include the latest version of necessary drivers and it also simplifies future upgrades and configuration.

For Windows servers with Apache see http://www.microsoft.com/en-us/download/details.aspx?id=20098.

Using FreeTDS on Windows

The recommended way to use FreeTDS under Windows is to use PHP 5.2.x following the following instructions:

  •  Download the appropriate copy of php_dblib.dll from the list below, and save it into your /PHP/ext directory.
PHP version Thread Safe FreeTDS version Download URL
PHP 5.2.x (vc6) Yes 0.82 + 20090302 patches Download!
  No 0.82 + 20090302 patches Download!
PHP 5.3.x (vc9) Yes 0.82 + 20090904 patches Download!
  No 0.82 + 20090904 patches Download!
       

(alternatively here you can find some instructions to build those freetds extensions under win32 yourself)

Downloaded dblib_ts.zip from the link for PHP 5.3.x thread safe and save it in downloads directory. Extract the files to a temp folder and then copy php_dblib.dll into your /PHP/ext directory.

  •  Edit your /PHP/php.ini file and add this line:

extension=php_dblib.dll

Make sure that any lines referring to the php_mssql.dll extension are DISABLED (commented out).

  • When the PHP engine loads the FreeTDS extension it needs to be passed certain infiormation in order to be able to connect to your Moodle database. To retrieve this information FreeTDS looks for a file called freetds.conf in the root folder of the server that PHP installed on (e.g. C:\).

freetds.conf should have the following structure:

[global]

host = xxx.xxx.xxx.xxx (host name or ip of the MSSQL server)

port = 1433

client charset = UTF-8

tds version = 8.0

text size = 20971520

If you want to connect to a particular instance of MSSQL you should specify the instance name:

[global]

host = xxx.xxx.xxx.xxx (host name or ip of the MSSQL server)

instance = xxx (instance name, e.g. INST2)

port = 1433

client charset = UTF-8

tds version = 8.0

text size = 20971520

We created a file with the above structure in notepad filling in the appropriate server name information and saved it on c:\ drive. Since our moodle/php/apache installation was on e:\ drive, we had placed the freetds.conf on e:\, but php couldn’t find the file and got errors during Moodle installation.

Notes:

  • You can configure FreeTDS to look for the freetds.conf file in any directory that you want – you don’t have to use C:\. To do this create a SYSTEM environment variable called FREETDS and point it to the directory where you have installed the freetds.conf file. If you do not set this environment variable FreeTDS will look for the freetds.conf file in the C:\ folder, which is the default. One possible benefit of setting the FREETDS environment variable and using a different installation directory for freetds.conf is that C:\ is very predictable to a hacker that knows anything about FreeTDS and that is the first place that he would look if he wanted to compromise your system. So, using a different installation directory would just make your system stronger. See the FreeTDS Setting the environment variables documentation for more information about this FREETDS environment variable.
  • Alternatively, you can recompile the FreeTDS extension yourself and change the default location to your preferred location at compile time. Then it is not necessary to create any environment variable. You must just ensure that freetds.conf is in the same folder that you specify when you compile php_dblib.dll.
  • MSSQL is usually installed with port 1433 as the default. However, if the port was changed on your server when you installed MSSQL then you need to specify the correct port number.
  •  Modify httpd.conf file located in apache/conf directory. The php5apache2_2.dll file was extracted when we unzipped php zip file.

Add following line

LoadModule php5_module “e:/server/php/php5apache2_2.dll”

AddHandler application/x-httpd-php .php

# configure the path to php.ini

PHPIniDir “e:/server/php”

Make sure following lines have correct values

ServerRoot “E:/server/apache”

ServerAdmin- your email@domain.com

DocumentRoot “E:/server/apache/htdocs” –Make a note of this directory as you would need to copy moodle files under this directory.

DirectoryIndex index.php index.html –add index.php here

7. Database conection test, try this PHP script, just put in a text file called test.php change (‘localhost’, ‘db_user’, ‘db_password’) to suite your setup, and load from local host (http://localhost/test.php)…

<?php

$link = mssql_connect(‘localhost’, ‘db_user’, ‘db_password’);

if(!$link) {

echo’Could not connect’;

die(‘Could not connect: ‘ . mssql_error());

}

echo’Successful connection’;

mssql_close($link);

?>

8. Moodledocs for PHP install mentions about modifying php.ini or .htaccess file( if you are using apache) in Moodledocs http://docs.moodle.org/22/en/PHP. We modified php.ini file. At some point we found htaccess.txt file in one directory and renamed it to .htacees, but not sure if .htaccess is being used. The file is attached below for your reference.

9. With all this properly configured, you can continue with a standard Moodle installation.

10. download moodle zip file for windows from http://download.moodle.org/ and save it in downloads directory. I downloaded the following build

Version Information    
Moodle 2.2.3+MOODLE_22_STABLEBuilt Weekly

13 hours 19 mins ago

This package is built every week with new fixes produced by our stable development process. It contains a number of fixes made since the 2.2.3 release and is usually a better choice for production than the actual 2.2.3 package below.

   

11. Extract moodle files in directory mentioned in document root of Apache’s httpd.conf file.

12. From the web brower type http://yourservername/moodle/intall.php. It should bring up Moodle installation screens and the information. In Database type select Sql Server Microsft free TDS . It should use phpdb_lib.dll installed earlier.

Troubleshooting

If you encounter some problems you can try:

  • check that you have DotNet framework 1.1 installed (later version are installed on Vista, but you could need this specific one)
  • enable TCP/IP for MSSQL: SQL Server 2005 Network Configuration -> Protocols for MSSQLSERVER -> TCP/IP (Enable) -> Properties -> Ip Addresses -> 127.0.0.1 (Active+Enable)
  • make sure the SQL Server Browser service is running SQL Server 2005 Network Configuration -> SQL Server Services
  • if you are using SQL Server 2005 and you have the error 4004: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier, try the ODBTP method (next chapter). The SQL Server complaining that it doesn’t support pure Unicode via TDS or older versions of ODBC. Microsoft has deprecated DB-Library a long ago, in favor of ODBC, OLE DB, or SQL Native Client. Many new features of SQL 2005 aren’t accessible via DB-Library so if you need them, you could have to switch away from tools based on TDS and DB-Library /.
Comments
  1. John Grobler says:

    Great guide, everything works perfectly for quickly setting up a Moodle site with MS SQL database. The only issue I’ve had was with the copy/pasting of punctuation from your guide: “ and ” should be replaced with “.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s