Note: You are currently viewing documentation for Moodle 1.9. Up-to-date documentation for the latest stable version is available here: Integrate Moodle, LDAP and SIMS.net.

Integrate Moodle, LDAP and SIMS.net

From MoodleDocs

To fully integrating Moodle, SIMS.net and LDAP there is a ordered process than need to be followed, outlined below

  • Integrate SIMS.net and LDAP
  • Integrate Moodle and LDAP
  • Integrate Moodle and SIMS.net
  • Install SIMS.net Timetable Moodle Modules


TIP: Check your MS SQL Server Port Number


If the your port number is different from the standard 1427, substitute 1427 for your port number in the following instructions.


Integrate SIMS.net and LDAP

  • What SIMS.net Information needs to be stored in LDAP
  • Configuring SQL Server 2005
  • Configuring Active LDAP
  • Configuring SIMS2AD vbscript


Introduction

All SIMS2AD Scripts are run as a scheduled task on the SIMS Sevrer from D:\SIMS2AD.

SIMS2AD

Runs at 22:00 every day

Add a new view to MS SQL Server


  1. Open SQL Server Management Studio
  2. Use the Object Explorer window to browse to the database and the folder called ‘Views’.
    • [Server]\[Instance] -> Database -> sims -> Views
  3. Right-click the ‘Views’ folder and select ‘New View’.
  4. Close the Add Table dialog box – Copy and Paste the query below into the Query box
  5. Save the View via File, Save [the temporary name of the view] .
  6. Enter the above name for the new ‘View’ and click ‘OK’.
  7. If you refresh the list of views by right-clicking the ‘Views’ folder you will see the new view near the top of the list.
  8. Close SQL Server management Studio.


MS SQL 2005 Server View


Name: vbs_adsync

Query to paste in:

SELECT     person_id, legal_surname, forename, midname, dob, unique_pupil_no, reg_group, roll_mode, year_group, 
           surname, chosen_forename
FROM       sims.stud_via_student_browse
WHERE      (roll_mode = 'On Roll')

Script


The SIMS2AD script needs to copied to D:\SIMS2AD on the SIMS Server.
The latest version of the script is available from:-

https://docs.moodle.org/en/SIMS2AD.vbs


Setting Up a Scheduled Task


A scheduled task needs to be created to run the SIMS2AD Script.
This Task will need to run once a day at 22:00 PM

  1. Open Control Panel
  2. Open Scheduled Tasks
    • This will open the Schedule Task Windows
  3. Add Scheduled Task
    • This will open the Schedule Task Wizard
  4. Click Next
  5. Click Browse and Navigate to D:\SIMS2AD Folder
  6. Select SIMS2AD_Access_Manager.vbs
  7. Give the the name "SIMS2AD"
  8. Select Daily
  9. Click Next
  10. Set The Time to 22:00
  11. Click Next
  12. Enter the user account details that will be used to run the script
  13. Click Next
  14. Click Finish


SIMS2AD Access Manager

Not Currently Setup
Runs every 5 minutes, every day
A log file is created in the same folder as the script, this hold a log of all processed changes.

Add a new view to MS SQL Server


  1. Open SQL Server Management Studio
  2. Use the Object Explorer window to browse to the database and the folder called ‘Views’.
    • [Server]\[Instance] -> Database -> sims -> Views
  3. Right-click the ‘Views’ folder and select ‘New View’.
  4. Close the Add Table dialog box – Copy and Paste the query below into the Query box
  5. Save the View via File, Save [the temporary name of the view] .
  6. Enter the above name for the new ‘View’ and click ‘OK’.
  7. If you refresh the list of views by right-clicking the ‘Views’ folder you will see the new view near the top of the list.
  8. Close SQL Server management Studio.


MS SQL 2005 Server View


Name: vbs_admanager

Query to paste in:

SELECT     TOP (100) PERCENT sims.stud_via_student_browse.unique_pupil_no, sims.stud_via_student_browse.forename, 
                      sims.stud_via_student_browse.surname, sims.udf_field.description AS type, sims.udf_lookup_value.description
FROM         sims.udf_value INNER JOIN
                      sims.udf_field ON sims.udf_value.field_id = sims.udf_field.field_id INNER JOIN
                      sims.stud_via_student_browse ON sims.udf_value.entity_id = sims.stud_via_student_browse.person_id INNER JOIN
                      sims.udf_lookup_value ON sims.udf_value.lookup_value_id = sims.udf_lookup_value.lookup_value_id
WHERE     (sims.udf_field.active = 'T')

Script


The SIMS2AD Access Manager script needs to copied to D:\SIMS2AD on the SIMS Server.
The latest version of the script is available from:-

https://docs.moodle.org/en/SIMS2AD_access_manager.vbs


Setting Up a Scheduled Task


A scheduled task needs to be created to run the SIMS2AD Access Manager Script.
This Task will need to run every 5 minutes from 08:00 AM to 20:00 PM

  1. Open Control Panel
  2. Open Scheduled Tasks
    • This will open the Schedule Task Windows
  3. Add Scheduled Task
    • This will open the Schedule Task Wizard
  4. Click Next
  5. Click Browse and Navigate to D:\SIMS2AD Folder
  6. Select SIMS2AD_Access_Manager.vbs
  7. Give the the name "SIMS2AD Access Manager"
  8. Select Daily
  9. Click Next
  10. Click Next as these Settings will be modified later.
  11. Enter the user account details that will be used to run the script
  12. Click Next
  13. Tick the box
    • This will open Advance Properties Window
  14. Click Finish
  15. Click the Schedule Tab
  16. Set the Start Time 08:00 AM
  17. Click the Advance Button
    • This will open an Advance Settings Window
  18. Set a Start date
  19. Tick Repeat Task
  20. Set it to repeat every 5 minutes
  21. Select Duration and set at 12 hours
  22. Click OK
  23. Click Apply
  24. Click OK


Required Logon Script Changes


The below code needs to be added to the beginning of the Kixtart Logon Script.
This will check the current time and see if the user belongs to a group that has restricted computer access.
If the user has logged on during a restricted time a message box will be displayed informing of this and then forcefully log them off.

IF ((@time > "20:00:00") AND (@time < "08:50:00"))
	If InGroup("SIMS2AD_LessonOnly_E4") or InGroup("SIMS2AD_LessonOnly_L4") or InGroup("SIMS2AD_Lesson_Evening_Only_E4") 
        or InGroup("SIMS2AD_Lesson_Evening_Only_L4")
	
                MESSAGEBOX ("Your Computer Access Been Restricted, For More Information Please Speak To Your Director Of Year", 
                "IT SERVICES", 16, 15)
                $RC = LogOff(0)
	endif
endif
IF ((@time > "11:15:00") AND (@time < "11:35:00"))
	If InGroup("SIMS2AD_LessonOnly_E4") or InGroup("SIMS2AD_LessonOnly_L4") or InGroup("SIMS2AD_Lesson_Evening_Only_E4") 
        or InGroup("SIMS2AD_Lesson_Evening_Only_L4")
		
                MESSAGEBOX ("Your Computer Access Been Restricted, For More Information Please Speak To Your Director Of Year",
                "IT SERVICES", 16, 15)
                $RC = LogOff(0)
	endif
endif
IF ((@time > "12:35:00") AND (@time < "13:35:00"))
	If InGroup("SIMS2AD_LessonOnly_E4")  or InGroup("SIMS2AD_Lesson_Evening_Only_E4") 

                MESSAGEBOX ("Your Computer Access Been Restricted, For More Information Please Speak To Your Director Of Year", 
                "IT SERVICES", 16, 15)
                $RC = LogOff(0)
	endif
endif
IF ((@time > "13:35:00") AND (@time < "14:35:00"))
	If InGroup("SIMS2AD_LessonOnly_L4") or InGroup("SIMS2AD_Lesson_Evening_Only_L4")

		MESSAGEBOX ("Your Computer Access Been Restricted, For More Information Please Speak To Your Director Of Year", 
                "IT SERVICES", 16, 15)
		$RC = LogOff(0)
	endif
endif
IF ((@time > "16:35:00") AND (@time < "18:00:00"))
	If InGroup("SIMS2AD_LessonOnly_E4") or InGroup("SIMS2AD_LessonOnly_L4") or InGroup("SIMS2AD_Lesson_Evening_Only_E4") 
        or InGroup("SIMS2AD_Lesson_Evening_Only_L4")

		MESSAGEBOX ("Your Computer Access Been Restricted, For More Information Please Speak To Your Director Of Year", 
                "IT SERVICES", 16, 15)
		$RC = LogOff(0)
	endif
endif
IF ((@time > "18:00:00") AND (@time < "20:00:00"))
	If InGroup("SIMS2AD_LessonOnly_E4") or InGroup("SIMS2AD_LessonOnly_L4")

		MESSAGEBOX ("Your Computer Access Been Restricted, For More Information Please Speak To Your Director Of Year", 
                "IT SERVICES", 16, 15)
		$RC = LogOff(0)
	endif
endif


Integrate Moodle and LDAP

Setting up LDAP Authentication

  1. Login to moodle as the admin user.
  2. Under Site Administration
    • Users - > Authentication -> LDAP Server
  3. Set the Following Fields as follows
    • LDAP server settings
      • Host Url = ldap://server.domain.com/
      • Version = 3
      • LDAP encoding = utf-8
    • Bind settings
      • Hide passwords = Yes
      • Distinguished Name = cn=LDAP Guest,cn=Users,dc=domain,dc=com
      • Password = Password of LDAP Guest Account
    • User lookup settings
      • User type = MS Active Directory
      • Contexts = cn=LDAP Guest,cn=Users,dc=domain,dc=com
      • Search subcontexts = Yes
      • Dereference aliases = Yes
      • User attribute = sAMAccountName
      • Member attribute = member
    • Force change password
      • Force change password = No
      • Use standard Change Password Page = No
    • LDAP password expiration settings
      • Expiration = No
    • Enable user creation
      • Create users externally = No
    • Data Mapping
      • First Name = givenName
      • Surname = sn
      • Email address = mail
      • Description = description
      • ID number = employeeID
      • Department = department
  4. Click Save


Integrate Moodle and SIMS.net

Configuring SQL Server 2005

Add a new view to MS SQL Server


  1. Open SQL Server Management Studio
  2. Use the Object Explorer window to browse to the database and the folder called ‘Views’.
    • [Server]\[Instance] -> Database -> sims -> Views
  3. Right-click the ‘Views’ folder and select ‘New View’.
  4. Close the Add Table dialog box – Copy and Paste the following query into the Query box
  5. Save the View via File, Save [the temporary name of the view] .
  6. Enter the above name for the new ‘View’ and click ‘OK’.
  7. If you refresh the list of views by right-clicking the ‘Views’ folder you will see the new view near the top of the list.
  8. Close SQL Server management Studio.


Configuring SQL Server 2005

Add a new view to MS SQL Server


  1. Open SQL Server Management Studio
  2. Use the Object Explorer window to browse to the database and the folder called ‘Views’.
    • [Server]\[Instance] -> Database -> sims -> Views
  3. Right-click the ‘Views’ folder and select ‘New View’.
  4. Close the Add Table dialog box – Copy and Paste the following query into the Query box
  5. Save the View via File, Save [the temporary name of the view] .
  6. Enter the above name for the new ‘View’ and click ‘OK’.
  7. If you refresh the list of views by right-clicking the ‘Views’ folder you will see the new view near the top of the list.
  8. Close SQL Server management Studio.


MS SQL 2005 Server View


Name: mdl_enrolments_current
SELECT DISTINCT     class_name AS course, staff_initials AS person, 'editingteacher' AS role, year_code, start_date
FROM                sims.curr_via_classperiods AS p
WHERE               (start_date BETWEEN '2008-09-01' AND '2009-08-31')
UNION
SELECT DISTINCT TOP (100) PERCENT p.class_name AS course, m.unique_pupil_no AS person, 'student' AS role, p.year_code, p.start_date
FROM                sims.curr_via_classperiods AS p INNER JOIN
                    sims.stud_via_group_members AS m ON m.base_group_id = p.base_group_id
WHERE               (p.start_date BETWEEN '2008-09-01' AND '2009-08-31')
ORDER BY            start_date

Configuring Moodle

FreeTDS 0.64 on Linux moodle servers

Install and configure FreeTDS 0.64 so that the linux moodle server can communicate with the remote MSSQL

Modify the /etc/freetds.conf file appropriately

[global]
	host = xx.xx.xx.xx
	port = 1427
	client charset = UTF-8
	tds version = 7.0
	text size = 20971520

If your version of FreeTDS is newer than 0.64 you may need a slight modification to the above configuration.

[global]
	host = xx.xx.xx.xx
	port = 1427
	client charset = UTF-8
	tds version = 9.0
	text size = 20971520

and make sure php-mssql is installed

rpm -ql php-mssql
/etc/php.d/mssql.ini
/usr/lib64/php/modules/mssql.so 

Add Moodle SIMS.net Course Template


  1. Login to moodle as the admin user.
  2. Under Site Administration
    • Courses - > Add/Edit Courses
  3. Click Miscellaneous
  4. Click New Course Button
  5. Set the Following Fields as follows
    • General
      • Full name = SIMS Template Course
      • Short name = simstemplate
      • Course ID number = simstemplate
      • Format = Topics Format
      • Number of weeks/topics = 10
      • Course Start Date = 1st Sept
      • Hidden sections = Hidden sections are shown in collapsed form
      • News Items to show = 5
      • Show gradebook to students = Yes
      • Show activity reports = No
      • Maximum upload size = [Maximum Size Allowed]
      • Is this a meta course? = No
    • Enrolments
      • Enrolment Plugins = Site Default (Internal Enrolment)
      • Default role = Site Default (Student)
      • Course enrollable = Yes
      • Start date = Disabled
      • End date = Disabled
      • Enrolment duration = Unlimited
    • Enrolment expiry notification
      • Notify = No
      • Notify Students = No
      • Threshold = 10 days
    • Groups
      • Group mode = No Groups
      • Force = No
      • Default grouping = No
    • Availability
      • Availability = This course is avaliable to students
      • Enrolment key = [Blank]
      • Guest access = Do Not Allow Guest In
    • Language
      • Force language = No
    • Role renaming
      • [All Fields are Blank]

Moodle Settings


  1. Login to moodle as the admin user.
  2. Under Site Administration
    • Courses - > Add/Edit Courses
  3. Click Add New Category
    • Parent Category = Top
    • Category Name = SIMS Courses
  4. Click Save
  5. Under Site Administration
    • Courses - > Enrolments
  6. Enable External Database
  7. Click Edit
  8. Set the Following Fields as follows
    • External Database Server Settings
      • enrol_dbtype = mssql
      • enrol_dbhost = sims:1427
        • 1427 Port number is optional
      • enrol_dbuser = sa
      • enrol_dbpass = [SA Password]
      • enrol_dbname = sims
      • enrol_dbtable = dbo.mdl_enrolments_current
    • Enrolment (remote) database fields
      • enrol_localcoursefield = idnumber
      • enrol_localuserfield = idnumber
      • enrol_db_localrolefield = shortname
      • enrol_remotecoursefield = course
      • enrol_remoteuserfield = person
      • enrol_db_remoterolefield = role
    • Roles
      • enrol_db_defaultcourseroleid = student
    • Auto-creation of new courses
      • enrol_db_autocreate = Yes
      • enrol_db_category = SIMS Courses
      • enrol_db_template = simstemplate
    • General Options
      • enrol_db_ignorehiddencourse = No
      • enrol_db_disableunenrol = No
  9. Click Save

Install SIMS.net Timetable Block V1.0 for Moodle


SIMS.net Timetable Block V1.0 now has ability to auto install and upgrade views on Microsoft SQL Server 2005.

Prerequisite


Students must have there emplyeeid field set to their UPN and staff need to set their employeeid field to there teacher code in Active Directory/LDAP for the module to display the timetable correctly.

Download the Block


The SISM.net Timetable Block files needs to copied to the html folder on the Moodle server.
The latest version of the files are available from:-

http://www.uctc.e-sussex.sch.uk/SIMS.net_Timetable_Module.zip

IMPORTANT: IF UPGRADING


If you are upgrading from a version before the release of 1.0, please do the following:-

  1. Uninstall the SIMS.net Timetable block from moodle.
  2. Delete the folders \blocks\simstimetable and \mod\simstimtable
  3. Delete the view mdl_student_timetable from SQL Server Management Studio

Install SIMS.net Timetable Block for Moodle


  1. Copy simstimetable folder to the blocks folder in moodle
  2. Login to moodle as the admin account
  3. Click on Notifications
    • A message should state that the block has been successfully installed.
  4. Click Modules -> Blocks -> SIMS.net Timetable to configure the block.

Configure SIMS.net Timetable Block for Moodle


Every schools timetable is slightly different, so the block will need to be configured.

Block Title: This text will be displayed as the block title.
Link Text: This is the text of the timetable url.
No. Weeks: Number of weeks timetabled in SIMS.net. (Only 1 or 2 supported)
Seperator: The separator is the character using in the SIMS.net to separate the day from the period name eg Fri:1, so the separator will be ":"
Lesson Labels: These are the names all there periods that are timetabled in SIMS.net seperated with a ","