Azure AD SSO provisioning duplicates group assignments / mappings in sys_user_grmember

[cphanson]

I am configuring the Azure AD plugin for user provisioning and SSO.   In the future state, every domain in our ServiceNow instance (we are domain separated) may or may not use SSO, each with its own Identity provider.   In my PoC, I am using Azure AD.   We are on Helsinki.

Azure AD SSO Plugin:   Microsoft Azure Marketplace  

Tutorial:   Tutorial: Azure Active Directory integration with ServiceNow | Microsoft Docs

My provisioning user is in a "customer" domain in ServiceNow and has two roles:   user_admin and SOAP   (not full admin role).   Everything is working well except for Group syncing:

Working:

1. Azure AD users get synced to ServiceNow based on my scoping criteria, get assigned the right domain in SN, etc (good)
2. Azure AD Groups that meet my scoping criteria get created in ServiceNow, including a couple of extra attributes we have in the sys_user_group table (Company and Type). (good)
3. Azure AD Group members get added to the appropriate groups in ServiceNow (good)
4. Users and Groups become inactive if I delete or deactivate them in AD (good)

Problems with Group syncing:

1. Every time I add a new user to a group in Azure AD (a group that is also synced to SN), when it syncs the group member to SN it creates duplicate records of the existing users in the group.   (it is not creating new SN users, it is creating duplicate user-group mappings in sys_user_grmember table).   If I have 5 members in a group, then I add a user, I get 11 members (2 of each of the first 5, plus the new one).   The next time I sync with a new group user, I'll get those 11 plus 7 more (6 are dupes, 1 is new).   The same thing happens when I manually restart provisioning in Azure AD Classic portal > Application > Configure tab > Restart provisioning button.   It duplicates the existing user mappings in the group.   Expected behavior:   I should only see NEWLY added users get added to the group, and no duplicates.
2. When I remove a member from the AD group, nothing happens in the ServiceNow group.   The user is not removed.   Expected behavior:   User should be removed from the ServiceNow group

I tried giving my SN provisioning user (user configured for Azure AD provisioning) full admin role, and that hasn't helped.

Any ideas on how to troubleshoot / solve these two problems?

[henry_cheng]

Hi Chris,

From the behavior you provided I suspect you did not enable any coalesce field mapping in the transform map.

Please have a read first at below wiki page regarding SSO user provisioning.

SAML user provisioning

================

When SAML user provisioning is enabled and the system encounters a new user that is not in the instance, the instance automatically creates a record in a temporary table with the name u_import_saml_user_<suffix>, where <suffix> is an automatically generated text identifier. The system also creates transform map that specifies the data relationships between the import table and the User table. Each IdP in identified in the system has its own transform map. The transform map is created once for each IdP. Administrators can update it as necessary.

================

You need to find out which transform map is being used to transform the user record from the temp table to the target table.

In that transform map you need to set coalesce to true for at least one field mapping to make sure system only update on these record if the same value on the field is detected instead of create a new record in the target table.

Hope above information helps.

Cheers

Henry

[cphanson]

Thanks Henry.   I found a transform map u_imp_saml_user_54xz1xpjcf under System Import Sets > Transform Maps.   However it doesn't make any reference to sys_user_group or sys_user_grmember.   The provisioning of users isn't an issue, its the provisioning of user to group mappings in sys_user_grmember.   If I understand you correctly, there might be another transform map that handles the transform from u_imp_saml_user_54xz1xpjcf to sys_user_grmember?

The concept of a transform map is totally new to me, but it makes sense.   I would think I'd also see something for sys_user_group since the Azure plugin also syncs groups.   However, I don't see a transform map for that.

I see under System LDAP > Transform Maps there is a map for LDAP Group Import.   However, I don't know if this is what is being used by Azure AD SSO plugin to sync groups.   Is there any way to know whether this is being used?   In any case, the OnAfter script contains a single line:   ldapUtils.addMembers(source, target);

Any help to point me in the right direction?   Thanks!

[henry_cheng]

Hi Chris,

Can you check in the transform map to see if there is any transform script that will operation on table sys_user_grmember?

Also please search for any business rules that will operation on sys_user_grmember when a new user is added in the group.

If still no found, please raise a HI ticket to ServiceNow technical support then we can login into your instance to have a look.

Cheers

Henry

[cphanson]

Nothing in the transform script for u_imp_saml_user_54xz1xpjcf refers to sys_user_grmember.   There are business rules for table sys_user_grmember, but looks like they handle assignment of skills, roles, labels, etc.   Nothing that checks for duplicates or anything like that.   I'll go ahead and raise a ticket in HI.   Thanks!