iSAMS to Moodle Course Enrolment – Part 2

This is the second post about setting up iSAMS and Moodle to perform course and user course enrolment synchronisation. Here are links to the other sections:

  1. Prerequisites and Assumptions
  2. Part 1 – Setting up your MSSQL Server
  3. Part 2 – Set up Apache to connect to MS SQL Server
  4. Part 3 – Setting up Moodle

Set up Apache to connect to MS SQL Server

We now need to configure our LAMP / Moodle server to be able to talk to MSSQL so that we can retrieve the data we prepared in our new database in the previous post. Again you will need to have already set up your LAMP / Moodle server by this point. Please either set it up as per the official Moodle documentation or as per my previous post; then come back here.

  1. First we will install the FreeTDS package to enable PHP to talk to MSSQL.
    1. Type:
      apt-get install libsybdb5 freetds-common freetds-bin php5-sybase
    2. Now restart your web server. Type:
      /etc/init.d/apache2 restart
    3. Now reload your PHP Info page and check that you have an MS SQL section with the Library Version listed as “FreeTDS”
  2. Now we need to configure FreeTDS to talk to our MSSQL server
    1. Edit the FreeTDS config file:
      nano /etc/freetds/freetds.conf
    2. At the bottom of the file, add an entry for your MSSQL server i.e.
      [isams dns]
      host = fqdn.your.mssql.server
      port = 1433
      tds version = 8.0
      text size = 20971520
      
    3. Save and exit the file…
  3. Now we can check if we can talk to the MSSQL server as follows:
    1. Type:
      tsql -S  fqdn.your.mssql.server -U sa -P YourStrongMSSQLsaPassword
    2. All being well you should see something like this:
      locale is "en_GB.UTF-8"
      locale charset is "UTF-8"
      using default charset "UTF-8"
      1>
    3. If you do not get the “1>” prompt then something has gone wrong with your FreeTDS configuration. Check the settings and try again. Otherwise type “exit” to return to your Linux terminal
  4. Now we can write a little PHP script to check that PHP can talk to MSSQL via FreeTDS
  5. Type:
    nano /var/www/html/mssql.php

    and paste the following info into the file:

    <?php
    
    error_reporting(E_ALL);
    ini_set("display_errors", 1);
    
    $host="fqdn.your.mssql.server";
    $dbname="iSAMS-to-Moodle";
    $dbuser="sa";
    $dbuserpass=" YourStrongMSSQLsaPassword";
    $dbtablename = "VwMoodleEnrolData";
    
    try
    {
       $db = new PDO("dblib:host=$host;dbname=$dbname", $dbuser, $dbuserpass);
    }
    catch(PDOException $e)
    {
       echo "Connection failed: " . $e->getMessage();
    }
    
    echo '<h1>Successfully connected!</h1>';
    
    $query = "SELECT * FROM $dbtablename";
    $statement = $db->prepare($query);
    $statement->execute();
    $result = $statement->fetchAll(PDO::FETCH_NUM);
    
    echo "<pre>\n";
    print_r ($result);
    echo "</pre>\n";
    
    ?>
  6. Save and exit the file.
  7. Now load http://yourservername/mssql.php and confirm that you are shown a long list of all enrolments in your iSAMS database.

The next post will look at installing Moodle and getting it talk to this database and complete the configuration!

2 thoughts on “iSAMS to Moodle Course Enrolment – Part 2

Leave a Reply

Your email address will not be published. Required fields are marked *