Sync users with Azure AD - A duplicates problem

[acquaalta]

Hi, Please help us with the following problem.

The problem occurs on: Moodle version: 3.11.6+ (20220318) Microsoft 365 Integration (local_o365) plugin version: 3.11.3 (2021051723) OpenID Connect (auth_oidc) plugin version: 3.11.3 (2021051720) The "Match Azure usernames to moodle emails instead of moodle usernames during the sync" is ON.

The problem does NOT occur on: Moodle version: 3.10.3+ (20210330) Microsoft 365 Integration (local_o365) plugin version: 3.10.3 (2020110904) OpenID Connect (auth_oidc) plugin version: 3.10.2 (2020110903) The "Match Azure usernames to moodle emails instead of moodle usernames during the sync" is ON.

We use LDAP as the authentication method in our Moodle systems.

We installed and configured the Microsoft 365 integration plugins on a new Moodle 3.11.6 environment, after doing so successfully in the past with other environments. We discovered that each time the "Sync users with Azure AD" scheduled task runs, it fails after 10-15 minutes, because of a duplicate entry. Here is a "blurred" example from the task log:

......... Syncing user [email protected]
......... Assigning Moodle user 192 (objectid [objectid]) to application
......... User assigned to application.
......... Found a user in Azure AD that seems to match a user in Moodle
......... moodle username: studento, aad upn: [email protected]
......... Matched user, but did not switch them to OIDC.
......... Adding o365 object record for user.
......... User is now synced.
.........  
......... Syncing user [email protected]
......... Assigning Moodle user 192 (objectid [objectid]) to application
......... User assigned to application.
......... Found a user in Azure AD that seems to match a user in Moodle
......... moodle username: studento, aad upn: [email protected]
... used [number] dbqueries
... used [number] seconds
Scheduled task failed: Sync users with Azure AD (local_o365\task\usersync),Error writing to database

Debug info:
Duplicate entry '192' for key 'mdl_locao365conn_mus_uix'
INSERT INTO mdl_local_o365_connections (muserid,aadupn,uselogin) VALUES(?,?,?)
[array (
  0 => '192',
  1 => '[email protected]',
  2 => 0,
)]
Backtrace:
* line 1357 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
* line 1403 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
* line 1417 of /local/o365/classes/feature/usersync/main.php: call to mysqli_native_moodle_database->insert_record()
* line 1350 of /local/o365/classes/feature/usersync/main.php: call to local_o365\feature\usersync\main->sync_users_matchuser()
* line 1107 of /local/o365/classes/feature/usersync/main.php: call to local_o365\feature\usersync\main->sync_existing_user()
* line 259 of /local/o365/classes/task/usersync.php: call to local_o365\feature\usersync\main->sync_users()
* line 193 of /local/o365/classes/task/usersync.php: call to local_o365\task\usersync->sync_users()
* line 253 of /lib/cronlib.php: call to local_o365\task\usersync->execute()
* line 120 of /lib/cronlib.php: call to cron_run_inner_scheduled_task()
* line 73 of /lib/cronlib.php: call to cron_run_scheduled_tasks()
* line 178 of /admin/cli/cron.php: call to cron_run()

In our institution some old users have 2 Azure AD users. For example: The old one: [email protected] The new one: [email protected]

However, there is only 1 user in Moodle for each person entity - For example, there would be only one user named "studento" in Moodle. And of course only one e-mail address of one Azure AD user would be included in the e-mail address field of this Moodle user.

We assume that because the users sync process, at least at some point, deals only with the username , a duplicates issue arise - both AAD UPNs (Azure AD UserPrincipalName) are referring to the same username, so the first AAD UPN can be synced but the second one cannot because it's a duplicate.

As mentioned above, this problem didn't occur in past versions of the plugins. I'm not sure, but I think it's because of changes you made since then in the following file: \o365\classes\feature\usersync\main.php I'm even more not sure , but I suspect that the relevant change is in this approximately area of the file:

// In order to find existing user accounts using isset($existingusers[$aadupn]) we have to index the array
// by email address if we match AAD UPNs against Moodle email addresses

Is it possible that in the past the code/query in this area of the file tried to match only e-mail addresses of users that actually exist in Moodle? If so, that explains why there weren't duplicates problems in the past.

Please clarify this issue and if it's possible, fix/update the plugin, or at least tell us what can be done on our side.

Thanks, even just for reading this :-)

[weilai-irl]

Hi @acquaalta,

I confirm your guess is quite reasonable. The changes lines are what caused the error in your task run.

The code changes were made in https://github.com/microsoft/o365-moodle/commit/a73cc0f13ba6d9e4fd61106638e1d01be4186839 and https://github.com/microsoft/o365-moodle/commit/a030fda8fbd854bf6bcb547c746a6b136b5f01ff, while the later one is more related to the error you saw.

Basically what it does is - if the "Match Azure usernames to moodle emails instead of moodle usernames during the sync" option is ON, and if we find two active (not suspended, not deleted) Moodle accounts with same matching email address, then the user sync task will ignore the email sync option, and revert back to use username to find matching user. Later on in the process, it will try to add this matching record in the local_o365_connections table, and it is here when you saw the errors. It looks like the database contains an existing connection record for the Moodle user already. This can be connected to either of the two Microsoft 365 account.

We can look at adding a check before creating new user connection record to avoid causing the database error in your note, but this will not guarantee solving the whole issue. The "proper" way would be to try "merging" the two accounts in the context of Moodle (not in Microsoft 365 as I understand that Microsoft 365 doesn't support merging accounts. If if it's possible to get rid of the one of the two accounts for the same user, it's even better). In Moodle, you can either use external tools such as https://moodle.org/plugins/tool_mergeusers, or simply suspend/deleted the unwanted account.

I hope this clarifies things a bit.

Regards, Lai

[weilai-irl]

Hi @acquaalta,

You can try to apply the connected pull request in your test environment before it's merged and released. It should avoid causing the database error.

But as I explained in my last note, the proper solution is still to remove the duplicate accounts.

Regards, Lai

[acquaalta]

Hi @weilai-irl, First of all, thanks a lot for the very quick and detailed response, and also for the new patch. The thing is that I'm not sure that I explained the issue well enough. Reading your answer, it seems that you understood that in Moodle we have 2 duplicate accounts for some users. It's actually the opposite - At least for some of the users (old ones), there are 2 duplicate Microsoft 365 accounts, but in Moodle there is always 1 account, and it doesn't matter if the user has 1 or 2 Microsoft 365 accounts.

On Moodle side there is only 1 account for such users, so there are no 2 accounts that can be merged into one. On Microsoft 365 side there are duplicates, of course, and you wrote "...in Microsoft 365 as I understand that Microsoft 365 doesn't support merging accounts. If if it's possible to get rid of the one of the two accounts for the same user, it's even better". For various reasons, deleting (getting rid of ) the duplicate account is out of the question. There are IT considerations that prevent any action in that direction. So at the end, the only possible direction that I can think of is letting the users sync mechanism of the local_o365 plugin to take care of these duplicates issue. In the past, before you added the new code, it did just that, even if it wasn't intentional :-)

Is it possible that you'll update the plugin so it could handle our users scenario, the one that I described above in this issue? BTW, I would be surprised if we're the only ones that has this issue of duplicate Microsoft 365 accounts for some users.

Thanks :-)

[weilai-irl]

Hi @acquaalta,

Hmm, I thought I had this one figured out :)

Could you provide the following please:

• The details of the two duplicate accounts in Microsoft 365 are link, i.e. their UPN, main email, and email alias. Take a note of their object ID too - you don't need to share it with me, but will be use in a check later.
• The details of your matching Moodle account, i.e. username and email.
• The record of the user in the local_o365_connections table, by running SELECT * FROM mdl_local_o365_connections WHERE muserid = USERID;
• The record of the user in the local_o365_objects table, if there's any, by running SELECT * FROM mdl_local_o365_objects WHERE type = 'user' AND moodleid = USERID;, if a result is found, check the "objectid" field and confirm the object ID of which user it is matched to.

My suspicion is there may be a connection record in the local_o365_connections table between the Moodle user and the "old" Microsoft 365 account, which should be cleared.

Just my thought - if all the users that you don't want to give access to matches some condition, e.g. their emails are from a particular domain, you can use the "User Restrictions" setting in the auth_oidc (OpenID Connect authentication) plugin to block their access. This is not directly related to the issue, but may help remove duplicate accounts.

Regards, Lai

[acquaalta]

Hi @weilai-irl, Thanks a lot for the detailed response. The IT guy is on vacation, so it would be a couple of days or weeks before I can supply the information that we need. Please don't close this issue yet.

[juan-perlaza]

Hi Microsoft team, my name is Juan Camilo Perlaza I'm the Open LMS EDU product owner. I'm reaching out because we have been tracking this issue since customers reported authentication problems related to this plugin. I kindly ask you if there is any estimated time to integrate a fix for this issue in a stable version, as this is a blocker for some customers in their daily activities.

[weilai-irl]

Hi all,

The fix to the issue has been included to the release from today. Please upgrade your plugins to the latest version to see the change.

I'm going to close the issue now.

Regards, Lai

[jgg9026]

Hi,

I just wanted to confirm if this fix is included in the 4.0.2 release. We would like to have it for Moodle 4.0

Thanks!

[weilai-irl]

@jgg9026

Yes, this was included in the 4.0.2 and 3.11.5 releases, therefore was closed.

Lai