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:
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
Also, don’t forget to set one of the following alternatives, in order to get all the data properly “slashed”:
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.
Make sure that any lines referring to the php_mssql.dll extension are DISABLED (commented out).
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