Problem

When running a sync, you get a similar error message to the following: 

de.resolution.atlasuser.api.exception.AttributeNotUniqueException: Attribute azure_ID with value 0121b454-3e7a-4c64-a760-13a2bee9d387 is found on john.doe@example.com and 66381539-f908-4741-9bf1-b0d884ab14c5
CODE

Depending on your connector, the message above would have a different attribute name, as follows:

  • Azure AD: azure_ID
  • Okta: okta_ID
  • Google Apps (GSuite): gsuite_ID
  • OneLogin: onelogin_ID
  • Keycloak: keycloak_ID

In this article, we will take Azure AD as an example, but everything would be similar to the other connectors as well - just replace "azure_ID" with the corresponding attribute of your connector.

Cause

That error message means that there are two users in the database having the same "azure_ID", which is currently set as the primary attribute that the UserSync connector uses to identify the users with. 

That happens in situations when:

  1. you change the primary attribute in the connector settings to something different than "azure_ID" (e.g. to "username")
  2. and the username of the user changes, whether from the IdP itself or via a transformation
  3. then when the sync runs, a new user is created with the new username but with the same azure_ID as the old one
  4. then if you change the primary attribute back to "azure_ID", you get that error

Symptoms

After step 3 and before step 4 (in the section above), you will have two users in your system:

  • The old user: has the old username but is currently deactivated (because it's not returned anymore from the IdP)
  • The new user: has the new username, enabled, but doesn't have any historical data

The problem starts to happen at step 4 (in the section above) when you change the primary attribute back to azure_ID. The error happens at the beginning of the full sync, so the full sync never runs for all the users in that case.

Solution

To solve this, you need to delete one of the two users who have the same azure_ID. In most cases, you would need to delete the "new user" because all the history is associated with the "old user" (even if it has the old username).

But you need to find the two usernames first, we know one of them already from the error message.

Take the example error message in this article:

  • One username is 'john.doe@example.com'
  • And don't get confused by '66381539-f908-4741-9bf1-b0d884ab14c5' in the error message - that is not the other's username, that's the external_ID of the other user (only visible via the database)

The next steps differ according to which Atlassian product you're using.

Jira

Find the two duplicate users

To find the two users having the same azure_ID in Jira, run the following query on your Jira database, but make sure to replace the value of the ua.attribute_value (in the last line) to the actual value of the azure_ID from your error message: 

SELECT u.user_name, u.active, u.directory_id, d.directory_name, ua.attribute_value atttibute_value_azure_id, u.external_id 
FROM cwd_user u
JOIN cwd_user_attributes ua
ON u.id = ua.user_id
JOIN cwd_directory d
ON u.directory_id = d.id
WHERE ua.attribute_name = 'azure_ID'
AND ua.attribute_value = '0121b454-3e7a-4c64-a760-13a2bee9d387';
SQL

The above would get you the two usernames having that same azure_ID.

Analyze the two duplicate users

Search for them in the Users page in Jira UI, and delete the ones that don't have history. You need to keep the one that has historical data, even if it doesn't have the updated/current username.

Delete the user

To delete the user from the UI, just click on the ... under Actions, then click on Delete user. 

Please note that the delete operation is not reversible, so make sure to delete the correct user.

Run a sync

Once the user is deleted, the full sync should run without any problem, and the existing (kept) user should be updated with the correct username.

(Extra) Find all other users not having a unique azure_ID

If you fixed the issue for that one user in the error message, but got a similar error for another user when you ran the new sync, then it might help that you find all the users who are affected by that, and fix them all, instead of finding that one by one only from the error message which only shows the first user with that issue.

To find all the duplicate users who have the same azure_ID, execute the following query: 

SELECT (SELECT lower_user_name 
        FROM cwd_user 
        WHERE cwd_user.id = cwd_user_attributes.user_id) AS username, 
        directory_id, 
        attribute_value AS azure_ID
FROM cwd_user_attributes 
WHERE attribute_value IN (SELECT attribute_value 
                          FROM cwd_user_attributes
                          WHERE attribute_name = 'azure_ID'
                          GROUP BY attribute_value
                          HAVING COUNT("attribute_value")>1)
AND directory_id = 10000
ORDER BY attribute_value;
SQL

In the above, please change the directory_id = 10000 to the actual value of your UserSync directory ID, which you can find from the connector settings:

Confluence

Find the two duplicate users

To find the two users having the same azure_ID in Confluence, run the following query on your Confluence database, but make sure to replace the value of the ua.attribute_value (in the last line) to the actual value of the azure_ID from your error message: 

SELECT u.user_name, u.active, u.directory_id, d.directory_name, ua.attribute_value atttibute_value_azure_id, u.external_id 
FROM cwd_user u
JOIN cwd_user_attribute ua
ON u.id = ua.user_id
JOIN cwd_directory d
ON u.directory_id = d.id
WHERE ua.attribute_name = 'azure_ID'
AND ua.attribute_value = '0121b454-3e7a-4c64-a760-13a2bee9d387';
SQL

The above would get you the two usernames having that same azure_ID.

Analyze the two duplicate users

Search for them on the Users page in Confluence UI, and delete the one that doesn't have history. You need to keep the one that has historical data, even if it doesn't have the updated/current username.

Delete the user

Since there is a limitation in the User Directory permissions in Confluence, it is not possible to easily delete a user from Confluence UI. The user could be deleted by using our SAML Toolbox endpoint via a REST API call.

  • Install the SAML Toolbox

  • Run the following cURL command for the user that you would like to delete (this will only retrieve its data/attributes to validate before applying the delete command): 

    curl -X "GET" "https://<base_url>/rest/samlsso-toolbox/1.0/users/<username>?directoryId=<directoryId>" -u '<sysadmin>:<password>'
    BASH

    In the above, replace:
    <base_url> with your Confluence base URL
    <username> with the username of the user
    <directoryId> with the directory ID of the UserSync directory (you can get that from the connector settings)
    <sysadmin>:<password> with your local admin username & password

  • Once you validate that the above returns the user that you want to delete, run the following delete command: 

    curl -X "DELETE" "https://<base_url>/rest/samlsso-toolbox/1.0/users/<username>?directoryId=<directoryId>" -u '<sysadmin>:<password>'
    BASH

    Please note that the delete operation is not reversible, so make sure to delete the correct user.

Run a sync

Once the user is deleted, the full sync should run without any problem, and the existing (kept) user should be updated with the correct username.

(Extra) Find all other users not having a unique azure_ID

If you fixed the issue for that one user in the error message but got a similar error for another user when you ran the new sync, then it might help that you find all the users who are affected by that, and fix them all, instead of finding that one by one only from the error message which only shows the first user with that issue.

To find all the duplicate users who have the same azure_ID, execute the following query: 

SELECT (SELECT lower_user_name 
        FROM cwd_user 
        WHERE cwd_user.id = cwd_user_attribute.user_id) AS username, 
        directory_id, 
        attribute_value AS azure_ID
FROM cwd_user_attribute 
WHERE attribute_value IN (SELECT attribute_value 
                          FROM cwd_user_attribute
                          WHERE attribute_name = 'azure_ID'
                          GROUP BY attribute_value
                          HAVING COUNT("attribute_value")>1)
AND directory_id = 10000
ORDER BY attribute_value;
SQL

In the above, please change the directory_id = 10000 to the actual value of your UserSync directory ID, which you can find from the connector settings:

Bitbucket

Find the two duplicate users

To find the two users having the same azure_ID in Bitbucket, run the following query on your Bitbucket database, but make sure to replace the value of the ua.attribute_value (in the last line) to the actual value of the azure_ID from your error message: 

SELECT u.user_name, u.is_active, u.directory_id, d.directory_name, ua.attribute_value atttibute_value_azure_id, u.external_id 
FROM cwd_user u
JOIN cwd_user_attribute ua
ON u.id = ua.user_id
JOIN cwd_directory d
ON u.directory_id = d.id
WHERE ua.attribute_name = 'azure_ID'
AND ua.attribute_value = '0121b454-3e7a-4c64-a760-13a2bee9d387';
SQL

The above would get you the two usernames having that same azure_ID.

Analyze the two duplicate users and delete one

In Bitbucket, deactivated users are not shown on the Users page in Bitbucket UI. In most cases, you don't need to see the "old user" in the UI, because basically, you would need to delete the "new user". So if that's the case, just search for the user in the Users page, and delete it from the 'x' button. 

However, if you would like to also check the other user to validate which user you should delete, you would need to use our SAML Toolbox endpoint and run a REST API call for that:

  • Install the SAML Toolbox

  • Run the following cURL command for the user that you would like to get (this will only retrieve its data/attributes to validate before applying the delete command): 

    curl -X "GET" "https://<base_url>/rest/samlsso-toolbox/1.0/users/<username>?directoryId=<directoryId>" -u '<sysadmin>:<password>'
    BASH

    In the above, replace:
    <base_url> with your Bitbucket base URL
    <username> with the username of the user
    <directoryId> with the directory ID of the UserSync directory (you can get that from the connector settings)
    <sysadmin>:<password> with your local admin username & password

  • If you would like to delete a user via the Toolbox as well, run the following delete command: 

    curl -X "DELETE" "https://<base_url>/rest/samlsso-toolbox/1.0/users/<username>?directoryId=<directoryId>" -u '<sysadmin>:<password>'
    BASH

    Please note that the delete operation is not reversible, so make sure to delete the correct user.

Run a sync

Once the user is deleted, the full sync should run without any problem, and the existing (kept) user should be updated with the correct username.

(Extra) Find all other users not having a unique azure_ID

If you fixed the issue for that one user in the error message but got a similar error for another user when you ran the new sync, then it might help that you find all the users who are affected by that, and fix them all, instead of finding that one by one only from the error message which only shows the first user with that issue.

To find all the duplicate users who have the same azure_ID, execute the following query: 

SELECT (SELECT lower_user_name 
        FROM cwd_user 
        WHERE cwd_user.id = cwd_user_attribute.user_id) AS username, 
        directory_id, 
        attribute_value AS azure_ID
FROM cwd_user_attribute 
WHERE attribute_value IN (SELECT attribute_value 
                          FROM cwd_user_attribute
                          WHERE attribute_name = 'azure_ID'
                          GROUP BY attribute_value
                          HAVING COUNT("attribute_value")>1)
AND directory_id = 10000
ORDER BY attribute_value;
SQL

In the above, please change the directory_id = 10000 to the actual value of your UserSync directory ID, which you can find from the connector settings:

Bamboo

Find the two duplicate users

To find the two users having the same azure_ID in Bamboo, run the following query on your Confluence database, but make sure to replace the value of the ua.attribute_value (in the last line) to the actual value of the azure_ID from your error message: 

SELECT u.user_name, u.active, u.directory_id, d.directory_name, ua.attribute_value atttibute_value_azure_id, u.external_id 
FROM cwd_user u
JOIN cwd_user_attribute ua
ON u.id = ua.user_id
JOIN cwd_directory d
ON u.directory_id = d.id
WHERE ua.attribute_name = 'azure_ID'
AND ua.attribute_value = '0121b454-3e7a-4c64-a760-13a2bee9d387';
SQL

The above would get you the two usernames having that same azure_ID.

Analyze the two duplicate users

Search for them on the Users page in Confluence UI, and delete the one that doesn't have history. You need to keep the one that has historical data, even if it doesn't have the updated/current username.

Delete the user

To delete the user from the UI, just click on the 'x' button.

Please note that the delete operation is not reversible, so make sure to delete the correct user.

Run a sync

Once the user is deleted, the full sync should run without any problem, and the existing (kept) user should be updated with the correct username.

(Extra) Find all other users not having a unique azure_ID

If you fixed the issue for that one user in the error message but got a similar error for another user when you ran the new sync, then it might help that you find all the users who are affected by that, and fix them all, instead of finding that one by one only from the error message which only shows the first user with that issue.

To find all the duplicate users who have the same azure_ID, execute the following query: 

SELECT (SELECT lower_user_name 
        FROM cwd_user 
        WHERE cwd_user.id = cwd_user_attribute.user_id) AS username, 
        directory_id, 
        attribute_value AS azure_ID
FROM cwd_user_attribute 
WHERE attribute_value IN (SELECT attribute_value 
                          FROM cwd_user_attribute
                          WHERE attribute_name = 'azure_ID'
                          GROUP BY attribute_value
                          HAVING COUNT("attribute_value")>1)
AND directory_id = 10000
ORDER BY attribute_value;
SQL

In the above, please change the directory_id = 10000 to the actual value of your UserSync directory ID, which you can find from the connector settings: