Note: You are currently viewing documentation for Moodle 2.5. Up-to-date documentation for the latest stable version of Moodle may be available here: Installing MSSQL for PHP.

Installing MSSQL for PHP: Difference between revisions

From MoodleDocs
No edit summary
(157 intermediate revisions by 29 users not shown)
Line 1: Line 1:
=== Introduction ===
{{Installing 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.
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.


First of all, minimum required version of MSSQL has been stabilised to MSSQL 2000 (v.8), although it's '''highly recommended to use MSSQL 2005 (v.9) or newer''', mainly because all the development process has been performed using this release and it could be some '''unknown problems''' with previous releases.
Some of this may also apply if you wish to access an MSSQL server for external db authentication/enrollment.  


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 this problems [[Development:XMLDB problems#MSSQL, PHP, UTF-8 and UCS-2|here]].
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.


So, in order to allow PHP (i.e. Moodle) to access to MSSQL DBs properly we have to install some '''mssql extension alternative''' to save us from the problems related above. See the sections below for details about the various options.
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 [[Development:XMLDB problems#MSSQL, PHP, UTF-8 and UCS-2|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 ===
== 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 you can use later to keep things simple).
1. Get MSSQL Server installed and running. ([http://www.microsoft.com/sql/editions/express/default.mspx A free limited version, SQL Server Express Edition] is available for testing.)
: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'''
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.
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:
4. Configure these settings in your created (and still empty) database:
Configure these settings in your created (and still empty) database:
:*Use a case sensitive collation, such as Latin1_General_CS_AS.
:*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)
:** This is not settable via the DB properties. To set READ_COMMITTED_SNAPSHOT, there must be no active connections to the database except for the connection executing the ALTER command. If you are viewing the DB in the Server Management Studio, disconnect from any servers in the "Object Explorer" (right-click > Disconnect), then create a "New Query" and run the ALTER command. See http://msdn.microsoft.com/en-us/library/bb522682.aspx for details.
:** If your DB name starts with a number, you may need to put quotes around the DB name in the query.


:* ANSI NULLS Enabled = true
:* Quoted Identifiers Enabled = true


5. Get PHP installed with a web server.  Unless you want to do it under IIS or some other way, the packages on the [http://download.moodle.org Moodle download page] are a good solution.
5. Get PHP installed with a web server.  Unless you want to do it under IIS or some other way, the packages on the [http://download.moodle.org Moodle download page] are a good solution.
Line 27: Line 35:
6. Choose one of the following specific sections for your server to install the '''mssql extension alternative''' installed and running properly on your PHP box.
6. Choose one of the following specific sections for your server to install the '''mssql extension alternative''' installed and running properly on your PHP box.


7. Don't forget to set one of these alternatives in your php.ini file, in order to get all the data properly "slashed":
7. Set the following settings in your php.ini file
 
:* mssql.textlimit = 20971520
:* magic_quotes_gpc = Off
:* mssql.textsize = 20971520
:* magic_quotes_gpc = On and magic_quotes_sybase = On


8. With all this properly configured, you can continue with a [[Installing Moodle|standard Moodle installation]].
8. With all this properly configured, you can continue with a [[Installing Moodle|standard Moodle installation]].


== Microsoft Drivers for SQL Server for PHP ==


=== Installing FreeTDS on Unix ===
In July 2008 Microsoft [http://social.msdn.microsoft.com/forums/en-US/sqldriverforphp/thread/a10e5202-9e41-4ff8-a33e-fbcc7b951be2/ 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.


If you web server is on Linux or some other flavour of Unix, try FreeTDS.  You can download FreeTDS from http://www.freetds.org. Also you will access to the documentation at http://www.freetds.org/docs.html.
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.


Note that the download link above is a '''source download''', so you will need to install and compile it properly (at the time of writing this, it has been successfully compiled and used under Unix-like servers only, so all the instructions below are exclusively for those systems). If you compile it under Windows and it works, feel free to document your experience here!
For Windows servers with [[Apache]] see http://www.microsoft.com/en-us/download/details.aspx?id=20098.


Once downloaded and uncompressed you must '''"configure, make, make install"''' it. This will deploy some stuff in the "/usr/local" directory of your machine, mainly:
== Using FreeTDS on Windows ==
* /usr/local/etc: where the freetds conf files will reside.
* /usr/local/lib: where compiled libraries will reside.
* /usr/local/bin: where some executables will reside.


Then, you must configure FreeTDS to point to your MSSQL DB server. To do so, edit (or create) the /usr/local/etc/freetds.conf file and put in there exclusively these lines:
<p class="note">'''Important Note 1:''' Due to some previous bugs it's highly recommendable to use PHP >= 5.2.6 and FreeTDS 0.82 + post-release patches ([http://tracker.moodle.org/browse/MDL-14725 more info]).</p>


  [global]
If your web server is on Windows, use '''php_dblib.dll'''. Despite the name, it's FreeTDS compiled for Windows. (Go to this page for information on [https://docs.moodle.org/en/FreeTDS Using FreeTDS for Unix].)  
      host = xxx.xxx.xxx.xxx (ip of the MSSQL server)
      port = 1433
      client charset = UTF-8
      tds version = 7.0
      text size = -1


At this point, and previously to build the '''mssql extension alternative''', you can test conectivity with your MSSQL DB using the "/usr/local/bin/tsql" executable. Just do this:
Originally we were using the DLLs available at [http://kromann.info/article.php?Id=11062598797760000 Frank Kromann's site], but they are outdated (using old versions of FreeTDS) and that has caused [http://tracker.moodle.org/browse/MDL-14725 some problems] in the past.


  tsql -S serverhost -U dbowner -P dbpassword
So, right now, the recommended way to use FreeTDS under Windows is to use PHP 5.2.x following the following instructions:


If everything is ok, you'll get this output:
1. Download the appropriate copy of php_dblib.dll from the list below, and save it into your /PHP/ext directory.


  locale is "es_ES.UTF-8"
{| border="1" align="center" cellpadding="5" style="text-align: center;"
  locale charset is "UTF-8"
|-
  1>
! PHP version !! [http://www.iis-aid.com/articles/my_word/difference_between_php_thread_safe_and_non_thread_safe_binaries Thread Safe]  !! FreeTDS version !! Download URL
|-
| rowspan="2" | PHP 5.2.x (vc6) || Yes || 0.82 + 20090302 patches || [http://download.moodle.org/download.php/dblib/php52/DBLIB_TS.zip Download!]
|-
| No || 0.82 + 20090302 patches || [http://download.moodle.org/download.php/dblib/php52/DBLIB_NOTS.zip Download!]
|-
| rowspan="2" | PHP 5.3.x (vc9) || Yes || 0.82 + 20090904 patches || [http://download.moodle.org/download.php/dblib/php53/DBLIB_TS.zip Download!]
|-
| No || 0.82 + 20090904 patches || [http://download.moodle.org/download.php/dblib/php53/DBLIB_NOTS.zip Download!]
|-
| rowspan="2" | PHP 5.4.x (vc9) || Yes || 0.82 + 20110906 patches || [http://inethub.olvi.net.ua/ftp/pub/soft/programming/php/ms%20sql%20driver%20for%20%20php%205.4.10/dblib_ts.zip Download!] [ftp://inethub.olvi.net.ua/pub/soft/programming/php/ms%20sql%20driver%20for%20%20php%205.4.10/DBLIB_TS.zip FTP Download!] [http://www.sendspace.com/file/vtger3 Mirror]
|-
| No || 0.82 + 20110906 patches || [http://inethub.olvi.net.ua/ftp/pub/soft/programming/php/ms%20sql%20driver%20for%20%20php%205.4.10/dblib_nts.zip Download!] [ftp://inethub.olvi.net.ua/pub/soft/programming/php/ms%20sql%20driver%20for%20%20php%205.4.10/DBLIB_NTS.zip FTP Download!] [http://www.sendspace.com/file/nkg2j4 Mirror]
|-
| rowspan="2" | PHP 5.5.x (vc11) || Yes || 0.91.89 || [https://moodle.org/pluginfile.php/133/mod_forum/attachment/1012945/FreeTDS_0.91.89_PHP_5.5.x_%28vc11_ts_dblib%29.zip Download!]
|-
| No || 0.91.89 || [https://moodle.org/pluginfile.php/133/mod_forum/attachment/1012945/FreeTDS_0.91.89_PHP_5.5.x_%28vc11_nts_dblib%29.zip Download!]
|-
| colspan="4" |  Thanks to [http://remote-learner.net/ Remote-Learner]] (Moodle [http://moodle.com/partners/ 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 [http://freetds.org freetds.org] by their support. Thanks to [[User:Alastair Hole|Alastair Hole]] for providing the PHP 5.3 builds of the libraries. Thanks to Enyby by providing the PHP 5.4 builds of the libraries. Thanks to David Aylmer for providing the PHP 5.5 builds of the libraries. Thanks!
|}


just type, for example:
(alternatively here you can find some [[Development:Compiling FreeTDS under Windows|instructions to build those freetds extensions under win32]] yourself)


  sp_help sysobjects


and you might get some output from DB. Finally type:
2. FreeTDS requires the .NET Framework v1.1 to be installed.  You can [http://www.microsoft.com/downloads/details.aspx?FamilyID=262d25e3-f589-4842-8157-034d1e7cf3a3&DisplayLang=en download it from the Microsoft website] along with its [http://www.microsoft.com/downloads/details.aspx?FamilyID=a8f5654f-088e-40b2-bbdb-a83353618b38&DisplayLang=en service pack].  Alternatively, if you do not wish to install this framework, you can [http://kromann.info/ms-libs/msvcr71.dll download the required DLL] from Frank's site, and save it into your /PHP root directory.


  exit


and you'll be out from the "tsql" command line interpreter.
3. Edit your /PHP/php.ini file and add this line:
 
Now that you've successfully built, configured and tested FreeTDS it 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, you'll need configure your PHP server adding this new option to the usual ones:
 
  --with-mssql=/usr/local/  
 
then, after the standard "make and make install" steps, your PHP server will be built with MSSQL support provided by FreeTDS.
 
Finally, configure your Moodle config.php with this DB related info and continue with a normal Moodle install:
 
$CFG->dbtype    = 'mssql';          // Required
$CFG->dbhost    = 'xxx.xxx.xxx.xxx'; // 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
$CFG->dbpass    = 'yourpassword';
$CFG->dbpersist =  false;
$CFG->prefix    = 'mdl_';            //Prefix, you can change it, but '''never''' leave it blank.
 
 
=== Installing FreeTDS on Windows ===
 
If your web server is on Windows, use '''php_dblib.dll''' from Frank Kromann ([http://kromann.info/article.php?Id=11062598797760000 original details here]).  Assuming that you have PHP 5.1.x, here how to set it up:
 
1. [http://download.moodle.org/download.php/windows/php5.1/php_dllib.php Download php_dblib.dll for PHP 5.1.x] and save it into your PHP extensions directory.
 
2. Edit your php.ini and add this line:


   extension=php_dblib.dll  
   extension=php_dblib.dll  
Line 104: Line 96:
Make sure that any lines referring to the php_mssql.dll extension are DISABLED (commented out).
Make sure that any lines referring to the php_mssql.dll extension are DISABLED (commented out).


3. Create a file called C:\freetds.conf with:


[global]
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:\).
        tds version = 7.0
        swap broken dates = no
        swap broken money = no
[host_name]
        host = localhost
        port = 1433
        tds version = 7.0


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


$CFG->dbtype    = 'mssql';          // Required
'''freetds.conf''' should have the following structure:
$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
$CFG->dbpass    = 'yourpassword';
$CFG->dbpersist =  false;
$CFG->prefix    = 'mdl_';            //Prefix, you can change it, but '''never''' leave it blank.


4. Restart or start your web server.
  [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


5. Install Moodle as usual.  Good luck!


If you want to connect to a particular [http://msdn.microsoft.com/en-us/library/aa174516(SQL.80).aspx instance] of MSSQL you should specify the instance name:


=== Installing ODBC on Windows ===
  [global]
 
      host = xxx.xxx.xxx.xxx (host name or ip of the MSSQL server)
1. Go to the '''Administrative Tools'''  control panel, then the '''Data Sources (ODBC)''' panel.
      instance = xxx (instance name, e.g. INST2)
 
      port = 1433
2. Configure one new System/User DSN (call it, for example "moodle").
      client charset = UTF-8
 
      tds version = 8.0
3. Your Moodle config.php should include lines like these:
      text size = 20971520


$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
$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!
'''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 [http://www.freetds.org/userguide/envvar.htm Setting the environment variables] documentation for more information about this FREETDS environment variable.


*Alternatively, you can [[Development:Compiling FreeTDS under Windows|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.


=== Installing ODBTP on Unix or Windows ===
*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.


You can download ODBTP from http://odbtp.sourceforge.net/. Also you will access to the documentation from the same page.


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).
5. Your Moodle '''config.php''' should include lines like these:


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).
<code php>
$CFG->dbtype    = 'mssql';        // 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.
</code>


To do do, following the instructions present in http://odbtp.sourceforge.net/install.html, you must:
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.


# 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.
# 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"'''!
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.


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


  --with-odbtp-mssql
<code 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);
?>
</code>


then, after the standard "make and make install" steps, your PHP server will be built with MSSQL support provided by ODBTP.
8. Install Moodle as usual.  Good luck!


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. To do so, add this lines, if no present, to your php.ini file:


  extension=php_odbtp.dll
=== 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)<br />
*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)<br />
*make sure the SQL Server Browser service is running SQL Server 2005 Network Configuration -> SQL Server Services<br />
*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 :(


(only for Win32 PHP servers!)
== FreeTDS on Linux (on Ubuntu by compiling an mssql.so extension) ==
This is a good read to [http://www.robert-gonzalez.com/2009/02/18/building-the-php-ms-sql-server-extension-from-source-on-ubuntu-810/ building a FreeTDS based mssql extension for apache on Ubuntu]. Do note that [http://www.freetds.org/news.html freeTDS] 0.91 was recently released, you can find latest versions [http://freetds.sourceforge.net/ here].


And, for all the server platforms:
Note: the freetds.conf file you use should have "text size = 20971520" as mentioned in the FreeTDS on Windows section otherwise you might see sessions logging out or worse apache segmentation faults. Also see [[FreeTDS]].


  [odbtp]
Note2: Please note that updating php tends to remove the freetds configurations. You might need to reconfigure freetds after an upgrade.
  odbtp.interface_file = "/path/to/your/odbtp.conf"
  odbtp.datetime_format = mdyhmsf
  odbtp.detach_default_queries = yes


(where ''/path/to/your/odbtp.conf" is usually "/usr/local/etc/odbtp.conf"" for Unix systems and "C:\odbtp\odbtp.conf" for Windows systems)
== Using FreeTDS on Debian Lenny ==
I found the following solution using:
* PHP Version 5.2.6-1+lenny9
* Microsoft SQL Server Enterprise Edition, version: 9.00.4053.00
<pre>apt-get install libsybdb5 freetds-common php5-sybase
/etc/init.d/apache2 restart</pre>
At the end of the process, if all goes fine, you will find in the mssql section of phpinfo();


Then, edit such "odbtp.conf" file and put there these contents:
{| class="nicetable"
 
|-
  [global]
! MSSQL Support
  odbtp host = xxx.xxx.xxx (ip or hostname of the MSSQL server)
! enabled
  type = mssql
|-
  unicode sql = yes
| Library version
  use row cache = yes
| FreeTDS
  right trim text = yes
|}
  var data size = -1


With this, your PHP server will be able to connect with the MSSQL DB server using ODBTP. From here, just continue with the installation.
Once FreeTDS is correctly installed, don not forget to set it up following explanations in https://docs.moodle.org/en/FreeTDS


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.
== See Also ==
* FreeTDS for Windows is obsolete and too slow as of 2011. See: [http://moodle.org/mod/forum/discuss.php?d=183987 Hardware and Performance Forum]
* [[Errors FAQ]]
* Using Moodle [http://moodle.org/mod/forum/view.php?id=28 Installation problems forum]
* [[Installing Postgres for PHP]]
* [[Using the Microsoft SQL Server Driver for PHP]]
* [[Installing Oracle for PHP]]


[[Category:Installation]]
[[Category:Developer]]
[[Category:XMLDB]]
[[Category:XMLDB]]
[[Category:DB]]
[[Category:SQL databases]]

Revision as of 03:12, 5 November 2013

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. (A free limited version, SQL Server Express Edition is available for testing.)

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.

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

  • Use a case sensitive collation, such as Latin1_General_CS_AS.
  • 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)
    • This is not settable via the DB properties. To set READ_COMMITTED_SNAPSHOT, there must be no active connections to the database except for the connection executing the ALTER command. If you are viewing the DB in the Server Management Studio, disconnect from any servers in the "Object Explorer" (right-click > Disconnect), then create a "New Query" and run the ALTER command. See http://msdn.microsoft.com/en-us/library/bb522682.aspx for details.
    • If your DB name starts with a number, you may need to put quotes around the DB name in the query.


5. Get PHP installed with a web server. Unless you want to do it under IIS or some other way, the packages on the Moodle download page are a good solution.

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

7. Set the following settings in your php.ini file

  • mssql.textlimit = 20971520
  • mssql.textsize = 20971520

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

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

Important Note 1: Due to some previous bugs it's highly recommendable to use PHP >= 5.2.6 and FreeTDS 0.82 + post-release patches (more info).

If your web server is on Windows, use php_dblib.dll. Despite the name, it's FreeTDS compiled for Windows. (Go to this page for information on Using FreeTDS for Unix.)

Originally we were using the DLLs available at Frank Kromann's site, but they are outdated (using old versions of FreeTDS) and that has caused some problems in the past.

So, right now, the recommended way to use FreeTDS under Windows is to use PHP 5.2.x following the following instructions:

1. 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!
PHP 5.4.x (vc9) Yes 0.82 + 20110906 patches Download! FTP Download! Mirror
No 0.82 + 20110906 patches Download! FTP Download! Mirror
PHP 5.5.x (vc11) Yes 0.91.89 Download!
No 0.91.89 Download!
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 for providing the PHP 5.3 builds of the libraries. Thanks to Enyby by providing the PHP 5.4 builds of the libraries. Thanks to David Aylmer for providing the PHP 5.5 builds of the libraries. Thanks!

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


2. FreeTDS requires the .NET Framework v1.1 to be installed. You can download it from the Microsoft website along with its service pack. Alternatively, if you do not wish to install this framework, you can download the required DLL from Frank's site, and save it into your /PHP root directory.


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


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


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


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.


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

$CFG->dbtype = 'mssql'; // 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)...

<?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. Install Moodle as usual. Good luck!


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

FreeTDS on Linux (on Ubuntu by compiling an mssql.so extension)

This is a good read to building a FreeTDS based mssql extension for apache on Ubuntu. Do note that freeTDS 0.91 was recently released, you can find latest versions here.

Note: the freetds.conf file you use should have "text size = 20971520" as mentioned in the FreeTDS on Windows section otherwise you might see sessions logging out or worse apache segmentation faults. Also see FreeTDS.

Note2: Please note that updating php tends to remove the freetds configurations. You might need to reconfigure freetds after an upgrade.

Using FreeTDS on Debian Lenny

I found the following solution using:

  • PHP Version 5.2.6-1+lenny9
  • Microsoft SQL Server Enterprise Edition, version: 9.00.4053.00
apt-get install libsybdb5 freetds-common php5-sybase
/etc/init.d/apache2 restart

At the end of the process, if all goes fine, you will find in the mssql section of phpinfo();

MSSQL Support enabled
Library version FreeTDS

Once FreeTDS is correctly installed, don not forget to set it up following explanations in https://docs.moodle.org/en/FreeTDS

See Also