In July 2008 Microsoft [http://social.msdn.microsoft.com/forums/en-US/sqldriverforphp/thread/a10e5202-9e41-4ff8-a33e-fbcc7b951be2/ released] their new SQL Server 2005 Driver for PHP. It is a PHP extension that allows for the reading and writing of SQL Server data from within PHP scripts. However there are some limitations with this driver that make it incompatible with Moodle, e.g.:

+

In July 2008 Microsoft [http://social.msdn.microsoft.com/forums/en-US/sqldriverforphp/thread/a10e5202-9e41-4ff8-a33e-fbcc7b951be2/ released] a new SQL Server 2005 Driver for PHP. This is a PHP extension that allows for the reading and writing of SQL Server data from within PHP scripts and it overcomes the problems with the native SQL Server extension that was previously included with PHP.

−

* limitations with how it handles UTF-8 strings and

+

This driver is the standard database layer for running Moodle 2 under Microsoft SQL Server databases. See [[Using the Microsoft SQL Server Driver for PHP]] the installation and configuration details.

−

* it does not support the legacy mssql php driver function names

−

For more info see MDL-16497 and MDL-15093.

+

This driver is only supported in Moodle 2.0 and up. You should use FreeTDS if you are installing an older version of Moodle.

Introduction

This short manual is suitable if you are trying to run Moodle 1.7 (and upwards) 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

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).

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.

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

Thanks to Remote-Learner] (Moodle Partner) and specially to Bryan Williams, donating one Visual C++ 6.0 Pro license to Moodle. Thanks to Trevor Johnson and his builds of the dblib extensions. Thanks to Daniele, Doug, Luis, Sean and many others by their collaboration in MDL-14725. Thanks to Frediano Ziglio and James K. Lowden from freetds.org by their support. Thanks to Alastair Hole by providing the PHP 5.3 builds of the libraries. Thanks!

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

4. 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:\).

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.

5. Your Moodle config.php should include lines like these:

$CFG->dbtype='mssql_n';// Required$CFG->dbhost='localhost';// assuming MS SQL is on the same server, otherwise use an IP$CFG->dbname='moodle';// or whatever you called the database you created$CFG->dbuser='yourusername';// I usually use the 'sa' account (dbowner perms are enough)$CFG->dbpass='yourpassword';$CFG->dbpersist=false;$CFG->prefix='mdl_';//Prefix, you can change it, but NEVER leave it blank.

If you don't have a config.php file yet, it can be generated as normal from the Moodle installer. Alternatively you can use the config-dist.php file that comes with the Moodle package to create your own config.php file.

6. Restart or start your web server. If Moodle still cannot communicate with the database server, please turn display_startup_errors to "On" in your /PHP/php.ini file, then restart the web server and check for any errors that may indicate incorrect DLL versions or missing dependencies. These error reports, turned off by default in PHP, can be vital in locating a problem with new extension installations.

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)...

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 :(

Using ODBTP on Unix or Windows

The downloaded package includes both the source code and some binaries to be installed in the server and some ready-to-use mssql extension alternatives for some platforms/PHP versions (so you won't need to compile it if your PHP server/version binary package is present).

First of all, we have to install the Win32 service that comes with the package. Let's assume that it's going to run in the same Win32 machine where your MSSQL server is running (although it can run in any other Win32 server in your network).

Create a directory on the Windows host where the service program files will reside, i.e., md odbtp.

Copy the files odbtpctl.exe, odbtpsrv.exe and odbtpsrv.ini files from the winservice directory into the directory created in step 1.

Edit the file odbtpsrv.ini of the previous step and this line:

MaxRequestSize=20971520

Open a command prompt (cmd) window on the Windows host.

Change to the directory to which the service program files were copied, i.e., cd odbtp.

Run the following commands to install and start the service:

odbtpctl install

odbtpctl start

With these steps you should have one new service running in your host called "odbtp". Verify it's present and running in the "Services" control panel.

Don't forget to enable TCP/IP incoming connections to port 2799 in the host you have installed the service!

Now it's time to build the mssql extension alternative. First of all, verify if, in the downloaded package, under the "php" dir, there is one extension suitable for your PHP server/version. If it's present, you can simply copy it to the php/extensions dir in your PHP server and skip next points about compiling it from source. It's important to point that, inside each directory, you'll find two different libraries/dll files. The one that must be copied to the extensions dir is the one called "php_odbtp_mssql.xxx"!

If in the downloaded package isn't present the extension matching your PHP platform/version, you should build if from source files. To do that, just "configure, make, make install". That will create some stuff under "/usr/local".

Now that you've successfully built ODBTP is time to create the mssql extension alternative that will provide us with the capacity of handling MSSQL DBs from within Moodle. To do so, just configure your PHP server adding this new option to the usual ones:

--with-odbtp-mssql

then, after the standard "make and make install" steps, your PHP server will be built with MSSQL support provided by ODBTP.

Do the following on the moodle webserver:
Finally, independently if we are using the binary extension provided in the download or if you have built it from source files, it's time to configure the extension.
1. To do so, add this lines, if no present, to your php.ini file:

With this, your PHP server will be able to connect with the MSSQL DB server using ODBTP. From here, just continue with the installation.

Finally, if you find the ODBTP executables and mssql extension alternative in binary formats, it only will be necessary to install them in your server (binary packages...) without the need to recompile anything (just the php.ini and odbtp.conf edition steps above will be necessary). Of course, it will be really welcome to have all those binary alternatives documented here.

Once ODBTP is working, Moodle config.php should include lines like these:

$CFG->dbtype='mssql_n';// Required$CFG->dbhost='localhost';// assuming MS SQL is on the same server, otherwise use an IP$CFG->dbname='moodle';// or whatever you called the database you created$CFG->dbuser='yourusername';// I usually use the 'sa' account (dbowner perms are enough)$CFG->dbpass='yourpassword';$CFG->dbpersist=false;$CFG->prefix='mdl_';//Prefix, you can change it, but NEVER leave it blank.

If you don't have a config.php file yet, it can be generated as normal from the Moodle installer.

Using ODBC on Windows

1. Go to the Administrative Tools control panel, then the Data Sources (ODBC) panel.

2. Configure one new System/User DSN (call it, for example "moodle"). Dont forget to enable these options if the driver asks for them:

ANSI NULLS Enabled = true

Quoted Identifiers Enabled = true

3. Your Moodle config.php should include lines like these:

$CFG->dbtype='odbc_mssql';// Note this is different to all the other configs on this page!$CFG->dbhost='moodle';// Where this matches the Data source name you chose above$CFG->dbname='';// Keep it blank!!$CFG->dbuser='yourusername';// I usually use the 'sa' account (dbowner perms are enough)$CFG->dbpass='yourpassword';$CFG->dbpersist=false;$CFG->prefix='mdl_';//Prefix, you can change it, but NEVER leave it blank.

4. Install Moodle as usual. Good luck!

Using the SQL Server 2005 Driver for PHP from Microsoft

In July 2008 Microsoft released a new SQL Server 2005 Driver for PHP. This is a PHP extension that allows for the reading and writing of SQL Server data from within PHP scripts and it overcomes the problems with the native SQL Server extension that was previously included with PHP.