MS SQL encryption with Moodle

If you like things a bit niche you might be running Moodle on Linux connecting to a MS SQL server running on Windows. This all works well with the MS SQL Server drivers for PHP.

The drivers support encrypted SQL connections but Moodle doesn’t provide an option to enable this. There is an issue logged on the Moodle tracker but it’s been sat there for ages and to be honest there seems a lot of politics around submitting patches to Moodle. If you just want a quick and dirty way of enabling this yourself you can use the wonderful patch I’ve created below against lib/dml/sqlsrv_native_moodle_database.php. Hold on to your hats though.

>         'Encrypt' => !empty($this->dboptions['encrypt']),

Yep, a beautiful one line change. This applies cleanly against 3.9 but I imagine it will work in most recent versions as this file hasn’t changed for a a while.

Once the change has been made you can modify your config.php. Just add encrypt => 1 to your dboptions array.

$CFG->dboptions = array (
  'dbpersist' => 1,

  'encrypt' => 1,


Obviously you need to ensure you have SSL/TLS set up correctly on the SQL side, you have the correct trusted root certs on your linux box and that you’re connecting to the FQDN of your SQL Server that matches the subject of the cert. If you’ve done everything correctly you’ll now be enjoying that sweet, sweet encryption.

Office 365

Relaying SMTP via Office 365 with legacy applications that don’t support TLS

So you’ve got some horrible application that needs to send out email but doesn’t support TLS or possibly even authenticated SMTP. Of course it’s critically important to the business and the vendor has no intention of implementing anything to help you out. You’ve done your cloud migration and the cloud vendor of course has disabled plain text SMTP ages ago. What do you do hotshot? WHAT DO YOU DO?

Well one way around it is to keep an on premise mail server, perhaps Exchange if you live the Office 365 life. This becomes a pain though, keeping it patched and having something else to administer. What you need is a lightweight relaying agent that you can install on your application server. That’s where comes in. It comes in *nix and Windows flavours and is nice and easy to install. The Windows installer walks you through the process and installs itself as a service.

Obviously you need to set up an account for the outbound email. In Office 365 this is nice and easy to do. Make sure you remember to enable “Authenticated SMTP” for the user in the the “Mail” tab in the 365 admin portal as it’s disabled by default. You probably also want to disable password expiry for the new account.

Set your outbound server to port 587 with STARTTLS enabled, enter your new 365 credentials and away you go. Make sure you don’t enable remote clients in EmaiRelay or people will be able to send out as the configured user which is obviously a bad thing.


Quick and dirty PlaySMS LDAP auth

PlaySMS is some awesome Open Souce SMS software but it lacks a couple of features for our use case, one of which was some form of centralised auth. Ultimately I’d like to write a proper plugin to allow SAML auth so we can front this with AzureAD but for now, as it’s at on premise anyway, we’ll have to make do with this bodgey LDAP integration.

Bear in mind that with this in place you’ll no longer be able login with any internal PlaySMS credentials so ensure that you create a user that matches your LDAP username and grant it admin permission before you apply this modification. It would of course be a trivial change to make this try to auth via the DB, then fail back to LDAP or vice versa if that’s what you’d prefer.

Anyway, open up plugins/core/auth/fn.php and replace

$db_query = "SELECT password,salt FROM " . _DB_PREF_ . "_tblUser WHERE flag_deleted='0' AND username='$username'";
	$db_result = dba_query($db_query);
	$db_row = dba_fetch_array($db_result);
	$res_password = trim($db_row['password']);
	$res_salt = trim($db_row['salt']);
	$password = md5($password . $res_salt);
	if ($password && $res_password && ($password == $res_password)) {


$ldapserver= "ldaps://ldapservername";
$ldap = ldap_connect($ldapserver);
$bind = @ldap_bind($ldap, $username . "@domainname.tld", $password);
if ($bind) {

Told you it was quick and dirty, but it works. Obviously you’ll need to ensure that you create users within PlaySMS that match the users in LDAP, we’re currently shoving this directly into the MySQL database.

You’ll also need to install the PHP LDAP extension.


Upgrading Dell Latitude 7390 2 in 1 from i5 8GB to i7 16GB

So this is super niche but I couldn’t find any info on this and took a leap of faith that things would work. Who knows, maybe it’ll help someone out one day.

I had a Latitude 7390 2 in 1 with i5-8250u CPU and soldered on 8GB RAM. It’s a fine laptop and I sort of love it but was really starting to struggle with only 8GB RAM and my battery was also knackered. Not wanting to replace the laptop, I set out on a quest to upgrade it but found little to no info as to whether it would work. Being me I thought “screw it let’s try anyway” and here we are.

What I started out with:
Motherboard: 0XMNM2 – i5 8250u, 8GB, no Thunderbolt 😦
Battery: 71TG4 – 45wh 11.4v
Cooler/fan/heatsink: 0P51WH

What I ended up with:
Motherboard: 02WCVJ – i7 8650u, 16GB, Thunderbolt 🙂
Battery: K5XWW – 60wh 7.6v
Cooler/fan/heatsink: 034T0C

So there you go, exciting stuff. Everything was plug and play really. Once you first reconnect the battery you’ll need to connect the laptop to a power source or it won’t boot. If you use a brand new motherboard you’ll then be asked to provide a service tag. I imagine you can enter anything here but I used the existing service tag of my device.

The original cooler does still attach to the board and you could probably get away with using it like I did for a couple of weeks until I could work out the right part number. It’s pretty much the same except for the fan being a bit smaller with more blades. The CPU is slightly further over to the left on the new board so the old cooler doesn’t quite fit correctly in the case making the back plate sit a few mm proud, you’ll also need to snap off one of the mounts on the fan to stop it fouling on the board. All in all better to get the new cooler.

For all you fan nerds out there, this is a photo with the new cooler on top and the old underneath. It’s a super bad photo that makes it just look like a shadow from the flash but you get the idea.

This also fixed my once a day random disconnect of USB devices which is nice. Guess the original board was faulty from day one, thanks Dell!


Moodle authentication against ASP.NET identity services database

Picture the scene – you have a custom enrolment application using ASP.NET identity for authentication and from out of nowhere someone decides that the users now need to be able to login to a VLE to complete assignments. Moodle already has a external database plugin so it can’t be too hard, except it doesn’t support the hashing that identity uses.

Given the short timescale to implement and crazy workload I of course went looking to see if anyone else had done this. There are some threads on Stack Exchange where people have tried to do the same thing and lots of info about how the hashing works so I set about porting the code to PHP only to find that someone had already done a much better job than I’d ever do. Thanks MDHearingAid.

So I cloned the repo and set about bodging it into Moodle. My bodge is not pretty but it works. If you want to do the same thing you can download my patch file (apologies for the Zip, WordPress won’t accept plain text files for some reason) and go at it, just don’t judge me too harshly. This is a patch against Moodle 3.8 but will probably/possibly work against other versions.

Obviously you need connectivity to the database that Identity Services is running on. So you’ll probably want to install Microsoft Drivers for PHP for SQL Server if you haven’t already and then set up your connection in Moodle under Site Administration -> Plugins -> Authentication -> External database. The table name will most likely be AspNetUsers. Username = Username , Password = PasswordHash. Under password format you should now see ASP.NET Identity Service or maybe just [[identityservice]] if my patch to the language file didn’t work properly.

Office 365

Restricting presenters in MS Teams meetings

You might want to prevent everyone in a meeting from being able to present, perhaps you work in Education or something. You can only do that via Powershell at the moment, because Microsoft.

Install SfB Online Powershell Module

Open Powershell

Import-Module SkypeOnlineConnector
$sfbSession = New-CsOnlineSession
Import-PSSession $sfbSession

or if, like me, you’re in a hybrid set up and your account is homed On-Premise you’ll need to do

Import-Module SkypeOnlineConnector
$sfbSession = New-CsOnlineSession -OverrideAdminDomain ""
Import-PSSession $sfbSession

Once connected you can see your meeting policies and the current DesignatedPresenterRoleMode with

Get-CsTeamsMeetingPolicy | ft Identity, DesignatedPresenterRoleMode

Set your policy to allow only the organiser to present by default and allow the presenter to override this setting. I’m going to do this globally but obviously replace the identifier with whatever policy name you want to set.

Set-CsTeamsMeetingPolicy -Identity Global -DesignatedPresenterRoleMode OrganizerOnlyUserOverride

You can set four options here, which are self explanatory



Full details at


Mist AzureAD SAML configuration

Login to your Azure portal and go to Azure Active Directory -> Enterprise Applications -> New Application. Click “Non-Gallery Application”, enter whatever name you wish to use for the Application, e.g. “Mist” then Add. Go to the new application, click “Single Sign-On” and select “SAML”. Copy the “AzureAD Identifier” from section 4.

Click on the pencil icon next to SAML Signing Certificate and set “Signing option” to “Sign SAML response and assertion”

Download “Certificate (Base64)”

Login to and go to Organisation -> Settings. Under Single Sign-On click “Add IDP”. Enter a name such as “AzureAD” and complete the fields as per the below table. For the certificate, open the “Certificate (Base64)” file that you downloaded from your Azure app in a text editor, copy the entire content of the file and paste into the “Certificate” box.

The rest of the fields can be copied and pasted from your Azure app as follows

Custom Logout URLLogout URL
IssuerAzureAD Identifier
CertificateContent of downloaded “Certificate (Base64)”

Next, we need to complete the configuration of Azure. You can do this manually, but it is easier to download your metadata file from Mist and upload it to Azure.

The URL for contains your organisation ID, i.e.{orgid} copy your orgid from this link and go to{orgid}/ssos you’ll see a JSON object returned, one of the elements will be “id”: “{ssoid} copy your ssoid from this link and go to{orgid}/ssos/{ssoid}/metadata.xml which will give you your metadata.xml file. Save this somewhere.

Go to your AzureAD application -> Single Sign On – > SAML -> Upload metadata file and select the metadata.xml you downloaded.

You now have SAML set up between AzureAD and Mist, but you can’t login as you’re not currently passing a “Role” attribute.

Firstly, log into Mist -> Organization -> Settings. Under “Single Sign-on” click Create Role. For “Name” enter a sensible value for each role that you wish to set up.

It probably makes most sense to use AzureAD group memberships for this. There are several ways to do this, but we do it like this.

Go back to your Azure Mist app -> Single Sign-on. Click the pencil button next to “User Attributes & Claims” then “Add a new claim”. Enter the name as “Role”, source as Attribute and then enter “None” in the “Source attribute”, this ensures people that aren’t in the correct groups get a role of “None” which prevents them from logging in.

Expand “Claim conditions” then in the “User type” dropdown select “Members”. Click “Select groups” and select the relevant group. For “Source” select Attribute and for “Value” type in the Mist role name you wish to assign to users that are members of this group. Continue to add groups for your desired roles.

In your Azure Mist app you’ll need to grant access to users at “Users and Groups”. Add the relevant groups to allow access. Users will see Mist in the Office 365 launcher under


Workrite quick and dirty user creation from AD when you already have SSO

This is ugly but it’ll get you started, there are definitely better ways. User creation script for

[System.Net.ServicePointManager]::SecurityProtocol = [System.Net.SecurityProtocolType]::Tls12

$Workriteuser="{workrite username}"
$WorkritePW="{workrite password}"
$WorkriteID="{workrite ID}"


$BaseOUS| ForEach-Object{

	get-aduser -properties mail,employeenumber -searchbase $_ -filter {enabled -eq "True" -and emailaddress -like "*"} -resultsetsize 10000 | foreach-object {

	$mailaddress = $_.mail
	$firstname = $_.givenname
	$surname = $_.surname
	$employeeid = $_.employeenumber


	$url = $url -replace '\s',''

	[xml]$output = Invoke-WebRequest "$url"
		#API response 13 duplicate username
		if ($"#text" -eq "13")
			Write-Host "User $mailaddress already exists"
		#API Response 0 success
		elseif ($"#text" -eq "0")
			Write-Host "User $mailaddress successfully created"