Last week,I have faced very critical problem to connect moodle1.9 with MSSQL 2008 on window server 2008 R2 64bit Operating System.Moodle community has lot of information about how to connect MySQL with moodle but buried information for MSSQL 2008.First of MSSQL is not open source so if anyone need to use for production server they must have to buy license version but i had install Express version of MSSQL for my development server.I never done any work on MSSQL so kind of very new experience for me.
If you have Firewall on your network so please be careful for port number which you are going to enter in MSSQL installation.
Step 1:-MS SQL SERVER 2008 Express
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. Install SQL Server Management Studio.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:
- ANSI NULLS Enabled = true (ALTER DATABASE xxxx SET ANSI_NULLS ON)
- Quoted Identifiers Enabled = true (ALTER DATABASE xxxx SET QUOTED_IDENTIFIER ON)
5. Get PHP installed with a web server.
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
Also, don’t forget to set one of the following alternatives, in order to get all the data properly “slashed”:
- magic_quotes_gpc = Off or
- magic_quotes_gpc = On and magic_quotes_sybase = On
8. With all this properly configured, you can continue with a standard Moodle installation.
Step 2:-Using FreeTDS to connect Moodle with MSSQL 2008
Moodle not easily allow to connect with MSSQL 2008.we need to install FreeTDS drive to connect with moodle.
- Download the appropriate copy of php_dblib.dll from the list below, and save it into your /PHP/ext directory.
- 2. You can download the required DLL(msvcr71.dll) from Frank’s site, and save it into your /PHP root directory.
- 3. Edit your /PHP/php.ini file and add this line:
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 information 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:
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:
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