How to find Inactive Active Directory users in Alteryx server

[miteshnarottam]

How to find inactive AD Users in Alteyx Server using the gallery logs

Starting 2020.3.9 (Stable Release), Alteyx Server can log Inactive AD users security identifiers (SID) in the gallery logs when an API call is made to users collection in MongoDB using the various functions gallery (for example when opening a collection, adding users to a collection, or viewing all users in the admin users console). This article provides instructions on how to use these SIDs to find which user in MongoDB can no longer be validated against Active Directory and is particularly useful for Defect Id: TCPE-30 Users unable to change the collection owner.

The  SID error is shown below. Notice the SID and API call highlighted in the snippet below: 

ERROR,40,WindowsIdentityContextWrapper,GetAuthorizedGroupSids,00e68a320cfd4bce93ca12f90a68462f,58a1c781f6367328705f2799,10.191.50.52,AMRMDALTRX1W,Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; Touch;" rv:11.0) like Gecko,GET,/gallery/api/admin/users/,,-1,,""System.Security.Principal.IdentityNotMappedException: Some or all identity references could not be translated.->   at System.Security.Principal.SecurityIdentifier.Translate(IdentityReferenceCollection sourceSids, Type targetType, Boolean forceSuccess)->   at System.Security.Principal.SecurityIdentifier.Translate(Type targetType)->   at Alteryx.Server.Common.Utility.ActiveDirectory.GetForestGroupsSids(Int32 adCacheTimeout, String sid)->   at Alteryx.Server.Api.WindowsIdentityContextWrapper.GetAuthorizedGroupSids(String sid)"""
2021-10-14 

ERROR,40,WindowsIdentityContextWrapper,GetAuthorizedGroupSids,00e68a320cfd4bce93ca12f90a68462f,58a1c781f6367328705f2799,10.191.50.52,AMRMDALTRX1W,Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; Touch; rv:11.0) like Gecko,GET,/gallery/api/admin/users/,,-1,Cannot Find user with SID S-1-5-21-839522115-1532298954-1801674531-805066,

 

Prerequisites

 

• Alteryx Server
  • Version 2020.3.9 Stable Release
  • Version 2020.4 Stable Release
  • Version(s) 2021.1.4 +
  • MongoDB (Embedded)
• Authentication Method
  • Windows Authentication

 

Procedure

 
For the Inactive SIDs to appear in the gallery logs you must first replicate the API calls made to the User collection. For defect TCPE-30 the invalidated SIDs will automatically appear in the gallery logs when the issue is replicated. For a more comprehensive list, you can use the admin/v1/users API call to query all users and list those that are not validated in the gallery logs.
 

Step 1: Log missing SIDs in gallery logs

 

1. For Defect ID: TCPE-30 (Unable to Change the collection owner) Simply replicate the behavior by attempting to change a collection owner.
2. To query all users: 
   1. Open the API Documentation Page from the gallery.  (Note: to use the admin/v1/user API you must have API Access and be a Curator. (see API documentation for more information. Click here )
   2. Select Admin V1 Tab, and insert the  API Access Key and Secret (The API key and Secret can be found under My Profile, Keys (Review the API Documentation in step ii
   3. Expand the /admin/v1/users/ call and Click on Try It Out

image.png

Please Note: This process can time some time to complete depending on how many users there are, so please be patient

Step 2: Using a workflow to Extract SIDs and match to Users Collection

 

1. Download the attached workflow(SID to User Match.yxmd) and open in designer on the Server
2. Copy the gallery logs during the time frame when you replicated the behavior or ran the Admin/V1/User API and copy them to a new folder. (Article: How to find Gallery logs )
3. Open the workflow SID to User Match.yxmd and fill in the MongoDB Input tool to connect to your DB(Instructions in the workflow)
4. Replace the file path in the Input tool with gallery logs location in step 2, followed by \*.csv (For Example: C:\Users\Username\Downloads\QA Gallery Log (2)\QA Gallery Log\*.csv). The *.csv will read all CSV files in the folder

image.png

5. Run the workflow. The output will provide you with a list of Invalidated AD users
6. Do a simple sense check for a few users by reaching out to your IT Team to confirm if the users still exist/are disabled in Active Directory. You can also run WMIC in the cmd line to confirm if this user still exists. (Note: This does not check for disabled accounts, and assumes the name is entered how AD sees it)
   1. Open cmd prompt and enter the following. replace the "user" with the SamAccountName from the list. 
      1. wmic useraccount where name="USER" get sid (Example: wmic useraccount where name="mitesh.narottam" get sid)

image.png

2. Check the SID and the username match against your extracted list.

 

Step 3: Disable User Access in Gallery

1. Armed with a list of inactive users, you can now start to disable access to the gallery. Open the gallery admin console
2. Select the Users Tab and search for each user and change the following:
   1. Set the Role to No Access
   2. Set all permissions within the profile to No (i.e. Can Schedule, Can create Collections, etc)
3. This should prevent the user from being validated against Active Directory
4. Finally, check if the collection owner can be changed for TCPE-30 (Remember, this is only a workaround and the Issue may reappear if another user is disabled in AD). You may also want to run the Admin/V1/User API call again to confirm no missing SID appear in the gallery logs (Steps 1 and 2)

 

Common Issues

 

Admin/V1/Users API call is failing Unauthorised Exception 

image.png

The 401 Response code means you are not authorized to make this call. The most common reason is the API Key and Secret have not been copied correctly from Step 1 Section ii. Go back to your Profile section, and ensure the API Access key and secret have been inserted correctly. You may wish to paste into Notepad to confirm the details are correct and that no trailing spaces are inserted.

 

Additional Resources

• TCPE-30: Unable to Change Collection Owner
• Gallery API Overview
• Private Gallery API Key and Secret