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.


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.