How to troubleshoot Airwatch Error Message “User is not created due to an error while saving or this user already exists in the system”


#1

How to troubleshoot Airwatch Error Message “User is not created due to an error while saving or this user already exists in the system”

Scenario:
When you trying to add an AD user, web-console able to search a user in AD but returns following error when saving:
Error: User is not created due to an error while saving or this user already exists in the system

Symptoms:
Web-Console logs in verbose mode display following:

WanderingWiFi.AirWatch.ProviderImpl.EnrollmentUserDataHandler	Failed to Save LDAP Custom Attribute. Error : 547, Severity 16, State 0, Message : "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CA_EnrollmentUser_ID". The conflict occurred in database "AirWatch", table "mobileManagement.EnrollmentUser", column 'EnrollmentUserID'.", Line : 60

WanderingWiFi.AirWatch.Console.Web.Controllers.ControllerHelpers.EnrollmentHelper	Value cannot be null. Parameter name: enrollmentUserId



**SQL Database Profiler** (customer-specific attributes replaced with [….]):
declare @p2 int
set @p2=0
exec mobileManagement.EnrollmentUser_Save @UserName=N'[….]',@EnrollmentUserID=@p2 output,@LastLoginDate=NULL,@LockoutExpirationDate=NULL,@LocationGroupID=[….],@DefaultLocationGroupID=[….],@LocationID=NULL,@DeviceGroupID=NULL,@SecurityTypeID=1,@Active=1,@DeviceStagingEnabled=0,@EnrollmentUserCategoryID=NULL,@EnrollmentRoleID=1,@RequirePasswordChange=0,@IsEnrolling=0,@ExternallyManaged=0,@Domain=N'[….]',@EmailAddress=[….]',@PhoneNumber=NULL,@EmailUserName=N'[….]',@UserPrincipleName=N'[….]',@FirstName=N'[….]',@MiddleName=NULL,@LastName=N'[….]',@EnrollmentUserDN=N'CN=[….]\, [….],OU=[….],OU=[….],OU=[….],OU=[….],DC=[….],DC=[….]',@LDAPDefinitionID=13,@MessageType=0,@ExternalID=N'45b335fc-18c8-4339-aa4d-8503a0471a4b',@FullName=N'[….]',@DisplayName=N'[….]',@Department=NULL,@LockoutTime=NULL,@MobileNumber=NULL,@UserEnrollmentStagingData=NULL,@UserEnrollmentStagingDataDate=NULL,@DeviceStagingType=0,@EnrollmentUserDNTrimmed=N'CN=[….],[….],OU=[….],OU=[….],OU=[….],OU=[….],DC=[….],DC=[….]',@EmployeeIdentifier=NULL,@CostCenter=NULL,@ManagerDistinguishedName=N'CN=[….],OU=[….],OU=[….],OU=[….],OU=[….],DC=[….],DC=[….]',@UserDisplayImageBlobID=0,@IdentityProviderIdentifier=NULL,@AzureUserPrincipleName=NULL,@UserPresentInAirwatchSchoolManager=0
select @p2

Solution:
Strategy is to search for data that coming from LDAP in query above in SQL DB table mobileManagement.EnrollmentUser. To be able to do so you would need either SQL profiler track as above or get information from AD using

CSVDE -f export.csv -r “(&(objectClass=user)(SamAccountName=[…]))”

or

Get-ADUser -Identity […] -Properties * | Export-Clixml -depth 15 -path export.xml

Or any other method by your choice.

Search for duplicates in SQL DB

for externalID

SELECT cdeu.*,eu1.*
FROM mobileManagement.EnrollmentUser eu1
JOIN mobileManagement.EnrollmentUser eu2 ON eu1.ExternalID = eu2.ExternalID
LEFT OUTER JOIN mobilemanagement.CurrentDeviceEnrollmentUser cdeu ON cdeu.EnrollmentUserID = 
eu1.EnrollmentUserID
WHERE eu1.EnrollmentUserDN <> eu2.EnrollmentUserDN
AND eu1.SecurityTypeID = 1
AND eu2.SecurityTypeID = 1
AND eu1.LocatiONGroupID = eu2.LocatiONGroupID
AND eu1.locationgroupid=xx
ORDER BY eu1.UserName

for username

SELECT cdeu.*,eu1.*
FROM mobileManagement.EnrollmentUser eu1
JOIN mobileManagement.EnrollmentUser eu2 ON eu1.UserName = eu2.UserName
LEFT OUTER JOIN mobilemanagement.CurrentDeviceEnrollmentUser cdeu ON cdeu.EnrollmentUserID = 
eu1.EnrollmentUserID
WHERE eu1.EnrollmentUserID <> eu2.EnrollmentUserID
AND eu1.SecurityTypeID = 1
AND eu2.SecurityTypeID = 1
AND eu1.LocatiONGroupID = eu2.LocatiONGroupID
AND eu1.locationgroupid=xx
order by eu1.Username

for EnrollmentUserID

SELECT cdeu.*,eu1.*
FROM mobilemanagement.enrollmentuser eu1
LEFT OUTER JOIN mobilemanagement.CurrentDeviceEnrollmentUser cdeu ON cdeu.EnrollmentUserID = 
eu1.EnrollmentUserID
WHERE eu1.securitytypeid = 1
AND eu1.externalid is null
AND eu1.locationgroupid=xx

xx = lg id where the directory is integrated.

select eu.* from mobilemanagement.EnrollmentUser eu
where username in (SELECT eu.UserName
FROM mobileManagement.EnrollmentUser eu
WHERE UserName IS NOT NULL AND LocationGroupID IN (SELECT Childlocationgroupid FROM dbo.LocationGroupFlat WHERE Parentlocationgroupid = YYYY /*Put the LGID here*/) 
Group BY eu.UserName
HAVING COUNT(eu.UserName) > 1
)

YYYY = Lg id of the Organization group.

Or, less sophisticated:

select * from mobileManagement.EnrollmentUser where ExternalID = '45b335fc-18c8-4339-aa4d-8503a0471a4b'

select * from mobileManagement.EnrollmentUser where UserName = '[…]'

etc.

Conclusion:
This case it happened that ObjectGUID in customer’s Active Directory seems to be reused by 3d-party IT company in charge of customer’s AD, resulting in value for “ExternalID” for new user already being presented in mobileManagement.EnrollmentUser. After verifying that previous user has no devices enrolled then deleting user in web-console it became possible to add new AD user.

Update 15-06-2018
Airwatch support confirmed that AD ObjectGUID is the only attribute that must be unique for a user to be successfully created in AW DB.