Tuesday, August 30, 2016

Why you should use Azure AD id’s and not e-mail addresses or UPN when bulk importing user profile properties to SharePoint Online


Back in April Microsoft released details of a new API do do bulk import of user profile properties in SharePoint Online. Say you have a field with an employee id, and you want to set this for all users in your organization. As Azure AD does not allow you to map custom fields into the user profiles automatically you have to set the data your self. Previously you could use the user profile API as an admin to do this, but it was not possible if you were for example using an automated app with app tokens, and you also had to loop over all profiles, updating one at a time.

You can read more about the API and see a full code sample at https://dev.office.com/blogs/introducing-bulk-upa-custom-profile-properties-update-api.

The bulk API to the rescue and it works like this: You create a JSON file with an entry per user. Users are identified either by e-mail, account name (principal name/UPN) or the Azure AD Object id (Cloud Id). The below sample uses the e-mail address.

  "value": [
      "IdName": "mikael@contoso.com",
      "EmployeeId": "1"
      "IdName": "yooba@contoso.com",
      "EmployeeId": "2"

Next you upload this file to SharePoint, and kick off an import with the QueueImportProfileProperties method of the Office365Tenant object.

o365.QueueImportProfileProperties(ImportProfilePropertiesUserIdType.CloudId, "IdName", propertyMap, url);

In my case I wanted to update all users with a specific value, and I retrieved all available profiles from the user profile application using search – as I figured that’s quicker than looping over AAD or the UPA. So far so good. Next I decided to use the account name (UPN) as the identifier, as all user have that field set and it’s unique. Once the import job had ran, I got the following error:

GenericError eb659162-c499-4fe9-a38d-c579d3c380f6 System.Data.SqlClient.SqlException (0x80131904): Input data contains multiple instances of the same single-value property for RecordId [2923275] at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

A very internal error indeed, and apparently there are some duplicate values somewhere. But how can that be when I have all unique accounts in my import file? Turns out that many of the external accounts and SPO internal accounts have records in the user profile application – but they are missing an Azure AD object id, or cloud id. If I skipped all accounts having either a blank AAD object id or the id was 00000000-0000-0000-0000-000000000000, the error went away. And I had no need to update those accounts anyway.

Seems to me that internally the bulk import code will look up the AAD object id for each entry you have in the file when using either email or UPN, and if the id is blank for more than one account, well, then you get this nice SQL error.

To sum it up, use the AAD object id, and make sure it exists before you try to update the profile using the bulk API :-)