Moodle & Azure Active Directory, the trials and tribulations

Recently I’ve undertaken the task of moving our teaching and learning system to authenticate using Azure AD.. There’s some 120,000 accounts on our Virtual Learning Environment, whether these are active / inactive or suspended doesn’t matter they all need to be able to be logged into if required by the user.

So how did I go about it?

By first installing the following plugins from the Office 365 set:

  • Microsoft Office 365 Integration (local_o365)
  • OpenID Connect (auth_oidc)
  • Office 365 Repository (repository_office365)
  • Microsoft Block (block_microsoft)

I used these plugins rather than the core OAuth2 method as they promise much higher level of AD customisation and are developed by Microsoft, as well as the extra levels of integration to Office 365.

The main task I was trying to complete was to attempt to use the account pattern match function, so the existing LDAP accounts could be matched to the ones within our Azure AD and gain a complete single sign on solution for our VLE… All this sounded like a simple task to aAddition to original postchieve.

The one thing you must do, is read the instruction manual for this plugin set, this is some 20 pages in length, find a quiet place before attempting this, there’s a lot to take in and a lot of prerequisites that you need to have in place, obviously one of these is a working Azure Active Directory, running in Azure portal.

I won’t go into detail of the installation or setup as the 20 pages does a good job of explaining that. What I do need to explain is the parts of what to do when your trying to achieve something when the manual has stopped.

Such as the one part that lead me down this path, the ability to match any preexisting moodle users with the same named accounts in Azure AD

The manual says “This requires the “Match” setting above to be enabled. When a user is matched, enabling this setting will switch their authentication method to OpenID Connect. They will then log in to Moodle with their Office 365 credentials. Note: Please ensure the OpenID Connect authentication plugin is enabled if you want to use this setting.” and that is it..

Well I can tell you I spent far too much time attempting to get this working to no avail. I’ve since learnt that our hosting partner has also tried it to no avail. So just don’t bother, unless it gets fixed in a later release than 3.5.0.2, see my github issue ticket that I’ve raised to see if it get solved in a future release or an explanation by the developers.

Here is an example of the errors which were being kicked out by the Azure AD sync in scheduled tasks:

......... Syncing user <strong><a href="mailto:studentID@tenant.ac.uk">studentID@tenant.ac.uk</a></strong>
......... Assigning Moodle user 30 (objectid 3c1a8a67-1234-4ace-bc38-22b0e4aa973e) to application
......... Could not assign user "<a href="mailto:studentID@tenant.ac.uk"><strong>studentID@tenant.ac.uk</strong></a>" Reason: No token available for usersync
......... Found a user in Azure AD that seems to match a user in Moodle
......... moodle username: <strong>studentID</strong>, aad upn: <a href="mailto:studentID@tenant.ac.uk"><strong>studentID@tenant.ac.uk</strong></a>
......... User is already matched.
......... User is now synced.

But nothing gets written back to the database, so whatever ‘No token available for usersync‘ means, is the reason why.

Additional to the original post

Here’s the answer to the above, see my github ticket for full details

Looking at the output you posted, it looks like this user is a “matched” user – i.e. they have not yet logged in to the site, correct? A “matched” user just sets up a user for a future connection, but the user has to actually log in to the Moodle site using their Office 365 credentials to complete the connection. User information syncing will only happen after this connection has been completed.

I’ve not proved this to be the case, as the plugins are only on our Dev server, until the summer upgrade, but I will be trying it again then.

Back to original post

After much soul searching the answer became obvious, just write the required field to the mdl_user table, using SQL on the back-end MySQL database.

Here is the SQL which I used to do that:

The SQL is updating the username and adding our Azure student and staff tenant to that. The regular expression, which you might not require is only checking for a ‘username’ which is 8 digits in length.

#SQL to change all students

UPDATE mdl_user
SET username = concat(ifnull(username,""), '@xxx.xxxx.ac.uk'), auth = 'oidc'
WHERE auth = 'ldap'
AND username regexp '^[0-9]{8}$'
AND email like '%@xxx.xxxx.ac.uk';

#SQL to change all staff

UPDATE mdl_user
SET username = concat(ifnull(username,""), '@xx.xxxx.ac.uk'), auth = 'oidc'
WHERE auth = 'ldap'
AND username regexp '^[0-9]{8}$'
AND email like '%@xxxx.ac.uk';

I hope the above helps you get to where I am quicker and with less pain.

I’ll finish here for now and go into detail about how to plugins handles new accounts at another time.

Addition to original post : Sync’ing new users

This works like a dream, I increased the cron task ‘Sync users with Azure AD’ to run every 7 mins, so it could chew through our AAD (Azure Active Directory) and it just creates users for you..

User Creation Restriction

Beware when using this, the obvious choice of the UPN / Username is not here, Object ID is not that, I had thought it was and wasted a day of tinkering and wondering why my Reg Ex was not working

(?im)^[0-9]{8}\@staff\.xxx\.ac\.uk$|(?im)^[0-9]{8}\@student\.xxx\.ac\.uk$

It because it wasn’t trying to match to the username, the Object ID looks like this:

06273d5ea-2ef1-1d10-a315-4ac234fe34f2

I’ve asked the developer to add this as a feature enhancement, see here

We’ve now used the option at the bottom, ‘Office 365 Group Membership‘ and created a dynamic security group, this is working as expected, which is fabulous, some people on the GitHub forum are asking for the ability to use more than one group to do this.

We’ve also used RegEx to block logins that are not formatted like our ID 12345678@staff.xxx.ac.uk or 12345678@student.xxx.ac.uk

Using the same regex as above, for these settings see User Restrictions in /admin/settings.php?section=authsettingoidc on your site.

BMW Mini R56 Start Stop, Stopped working?

A car related post… A break from the norm

I’ve got a UK 61 plate (2011) mini cooper diesel.

Within the last year I noticed that the Start / Stop functionality stopped working, no other problems noticed until the other day, I reached my normal parking spot switched off the engine in order to carry on listening to the radio, 1 minute later, if that & then the battery light appeared on the dash and the car shut down…..

The following morning I got a yellow light telling me to have the car looked at, so after an amount of fiddling with the service info on the stalk and pressing the BC button I got the error code CC-ID-415 : Battery Discharge Rate High.

The car was fine for 4 more days, performing it’s 25 miles return journey a day, the code disappeared and I got a slot in the garage.. I had them replace the battery with a new AGM battery and had it recoded, this was based on my guess it all pointed to the battery failing due to age, to have diagnostics performed to look further into the issues might have increased the bill.

Start stop worked straight away. I was shocked at the £200 bill. Although if I had it fitted at Mini dealership garage would have cost another £50+ pounds.

Just thought I’d post this incase it helps anyone else out with similar issues.

How to list users in an Active Directory group within Powershell

We’ve been checking AD group permissions, making sure that the right people have the right access.

Here’s a simple script to keep handy, it’ll list all the users, name and their department of a AD group, in the examples below it’s listing a group called ‘Administrator’, the first script lists to screen the second one creates a .csv file in C:\Temp

Displays to screen in Powershell

Get-AdGroupMember 'Administrators' | Select samAccountName,
@{Name="DisplayName";Expression={(Get-ADUser $_.distinguishedName -Properties Displayname).Displayname}},
@{Name="Title";Expression={(Get-ADUser $_.distinguishedName -Properties Title).title}},
@{Name="Department";Expression={(Get-ADUser $_.distinguishedName -Properties Department).department}}

Creates a CSV file in C:\Temp

AdGroupMember 'Administrators' | Select samAccountName,
@{Name="DisplayName";Expression={(Get-ADUser $_.distinguishedName -Properties Displayname).Displayname}},
@{Name="Title";Expression={(Get-ADUser $_.distinguishedName -Properties Title).title}},
@{Name="Department";Expression={(Get-ADUser $_.distinguishedName -Properties Department).department}} | Export-csv -path C:\Temp\AdministratorsGroupMembers.csv

Hope it helps.

AppDynamics upgrade / Glassfish Master password / Keystore issues?

We were having real problems upgrading AppDynamics from 4.2 to 4.3 then on to 4.5. All of the issues where caused by things that were not mentioned in their documentation.

Hopefully this information might prove to be useful to someone else in the same situation.

Our main issue was been caused because we had a custom password securing our Glassfish Master / Keystore & Keystore Keys, all of which was the same, as they need to be, because of this the installer was failing as it doesn’t ask you to insert your password as a parameter during installation, you need to change it back to the default of ‘changeit‘ before commencing an upgrade when we tried to get asadmin to do this for us, we got the error:

asadmin change-master-password --savemasterpassword=true
Enter the current master password>
Enter the new master password>
Enter the new master password again>
Keystore was tampered with, or password was incorrect
Command change-master-password failed.

After many hours we worked out that you first needed to:
Browse to the folder: \AppDynamics\Controller\appserver\glassfish\domains\domain1\config and type the following commands to set the password back to the default one.

keytool -storepasswd -keystore keystore.jks
Enter keystore password:
New keystore password: changeit
Re-enter new keystore password: changeit
keytool -keypasswd -alias glassfish-instance -keystore keystore.jks
Enter keystore password: changeit
Enter key password for :
New key password for : changeit
Re-enter new key password for : changeit
keytool -keypasswd -alias reporting-instance -keystore keystore.jks
Enter keystore password: changeit
Enter key password for :
New key password for : changeit
Re-enter new key password for : changeit
keytool -keypasswd -alias s1as -keystore keystore.jks
Enter keystore password: changeit
Enter key password for :
New key password for : changeit
Re-enter new key password for : changeit
The most important step, which is missed in the documentation, if you’ve secured this file using anything but ‘changeit‘ then it’ll cause the above asadmin command to fail everytime, even half way through an upgrade.
keytool -storepasswd -keystore cacerts.jks
Enter keystore password:
New keystore password: changeit
Re-enter new keystore password: changeit

Other possible issues which you might encounter:

JRE Path is incorrect

Error: could not open `\AppDynamics\Controller\jre\lib\amd64\jvm.cfg’ The above error is telling you that your JRE path is messed up and it cannot locate the required files.

Solution:

  • check location of above file
  • Edit \AppDynamics\Controller\appserver\glassfish\config\asenv.bat
    • Make sure ‘set AS_JAVA=’ is point to the root of the JRE with the above file.

Error:

Stage [Discover Controller SSL certificate] failed due to
[Task failed: Discovering SSL certificate
on host: HOST
as user: HOST$
with message: Keystore was tampered with, or password was incorrect]

Solution: When upgrade the controller from 4.5 to 4.5, you can set your homemade password in for Glassfish / Keystore / Keys in controller.groovy

keypasswd = "changeit"
storepasswd = "changeit"

but this still failed until we had set all our passwords back to ‘changeit‘ see above on how to do this, then the above controller.groovy file to ‘changeit‘ as well.

Error:

Task failed: Starting Reporting Service
on host: HOST
as user: HOST$
with message: Expected is [RUNNING], but actual result is [STOPPED].

Solution:

Stop the AppDynamics Reporting Service and restart it, then continue the installation back in the AppDynamics 4.5 Enterprise Console.

Good luck out there, upgrading AppDynamics is not an easy task!

We are now looking to move to SaaS because it caused us so many issues.

Windows 10 Update October 2018 / 1809 : No browser website access?

I installed Windows 10 1809 update from here

Bypassing my domain group policy which has greyed out the ‘check for updates’ button. This is slightly annoying in itself.

After installation was completed, I noted that random things were happening when browsing the internet, this was on all the web browsers that I had installed (Chrome, Edge, Firefox). Things like no pages being displayed to browsers working for the first 5 minutes since a fresh reboot, to Chrome crashing and refusing to open again that session. Odd! so I checked whether I could do a

nslookup google.com

From the command prompt.

This was still working as expected. So the network card and settings weren’t the cause of my strife.

I had already upgrade two other machines without any issues, so what was going on? The only difference to the machines were the working ones were laptops using WiFi, the broken one was a desktop using a connected ethernet card.

After a day and a half tweaking / reading the internet, the solution lied with Malwarebytes ! I removed the installation and everything started working again. The installation had recently upgraded itself.

I’ve no idea why and frankly don’t care as it caused me so much hassle to figure out what was going on.

I’ve still got Malwarebytes installed on the two other machines and they are still working as expected, so there must be a buggy version out there that halts the internet access or it detected malware and shut the internet down when using the 1809 update.

Hope this blog post helps someone else too.

Oracle Scheduler Jobs and Email Notifications vs crontab

Question: How do I run scheduled jobs and get a notification via email?

The above is something that our business does all the time, but the answer was, to do that you’ll have to create a crontab direct job on the server.

I thought surely not.. So I thought about it and with a bit of Googling I figured out that the above wasn’t true, it’s possible to do these as scheduled jobs in SQL Developer.

Create Stored Procedure

  • In SQL Developer create a new Procedure
    • Right click on ‘Procedures’ and select ‘New Procedure’, give it a logical name describing what it does, spaces are not allowed, use underscores.
  • Type in your SQL over where the blank Procedure template says ‘NULL;’

Create Scheduled Job

Or jump straight to creating the job if you want to run a block of SQL which doesn’t suit being a Procedure, you can put the SQL directly into the job.

  • Go to the Schema in your database which has the rights to run a scheduled job
  • Under there expand ‘Scheduler’ and ‘Jobs’, right click on ‘Jobs’ and select ‘New Job (Wizard)…’
  • Fill in the field like below
    • Use logical names and give a full description of what the job is doing
    • Either put the SQL in directly in ‘PL/SQL Block’ or select the Procedure you created

JobWizard

When to execute the job?

  • use the ‘When to Execute Job’ drop-down to select ‘Repeating’ and click the pencil this will make the ‘Repeat Interval’ dialog box appear, shown above
  • Select required time and days for the job to run, click ‘OK’.
    • I selected Daily as well as the actual days, just to be sure 🙂
  • Once happy with your choices click ‘Next >’
  • Set Destination to ‘Local’ for it to run on the server, click ‘Next >’
  • Skip over ‘Job Arguments’ to ‘Notification’
  • Now add in ‘job_succeded’ just whilst you are checking the whether your emails are working when the job ran, add it by holding down Ctrl and clicking it, otherwise you will lose your current default options of : job_broken, job_chain_stalled, job_failed, job_over_max_dur, job_sch_lim_reached
  • Move Date: %event_timestamp% from the body to the bottom, as I’ve noticed that it doesn’t create a carriage return after it so will bunch up all the notification output.
    • Also fix the line Error code: by moving the end % back up a line from Error message. This might be a bug on my version of SQL Developer and will be fixed on yours.

From like this:

Retry count: %retry_count%
Error code: %error_code
%Error message: %error_message%

To like this:

Retry count: %retry_count%
Error code: %error_code%
Error message: %error_message%
  • Now Next through the rest of the setting and click ‘Finish’

How to Set up the email side of things

  • Edit and run the following SQL using your System account
BEGIN 

DBMS_SCHEDULER.set_scheduler_attribute('email_server', 'outlook.blah.co.uk:25');
DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'noreply@blah.co.uk');

END;

That should be it, all that is left to do is to run your job. You can do that by right clicking the job and selecting ‘Run Job…’

Now when people start to automate jobs, they will be visible to your whole team, rather than hidden away on the server in a crontab.