• Alteryx Designer
  • 2021.4.2 Patch 4, 2022.1 Patch 2 and later
• Microsoft SharePoint
  • SharePoint 365, SharePoint On-Premise (2013 or 2016) 
• SharePoint Tools
  • 2.2.0+
• Postman

The SharePoint connector allows you to use Service Principal for downloading and uploading files, but it requires the Sites.All permission, which gives the user access to all the Sites in the tenant. To avoid this, SharePoint 2.2.0 is able to work with Sites.Selected permission, i.e. the Azure admin is able to limit the scope of Sites the app has access to. 

This page describes how to set it up.

Because there is no UI to assign the App to the Site, this might be a little bit complicated. So grab a coffee or Coke (or mix them together, it’s awesome!) and let’s get started.

Important: This process includes the creation of a temporary Full Scope App with FullControl.All permissions. This is used to apply the necessary permissions to another app which will be used within Alteryx. This Full Scope App can be deleted at the end of this process.

Important: Sharepoint Connectors 2.6.0 and above removed user verification in authentication to better align with Microsoft's defined Service Principal authentication. This leads to an unexpected UI limitation that users will run into after setting up the app registration as described here. There is another KB with instructions to work around the issue, found here: 

https://knowledge.alteryx.com/index/s/article/SharePoint-version-260-Using-Service-Principal-will-not-load-Sites

Get Ready

The App is assigned to the Site via API. To make this as simple as possible, we have prepared a Postman collection at the bottom of this article for you to download. Please rename the extension back to .json if you download this file.  You’ll also need Postman if you don’t have it yet.

This guide also requires SharePoint connector of at least version 2.2.0 installed.

Procedure

1. Prepare Postman - Open the Collection and Its Variables
   Go to Import collection → Edit collection → switch to the Variables tab.
   
    
   
   The goal is to fill the CURRENT VALUE column with actual values. 

2. Create the Full-Scope App
   The sole purpose of this app is to assign the Service Principal app to the Site, so only the tenant admin needs to use it, not the Alteryx user.
    
   1. Create the app: http://Portal.azure.com → Azure Active Directory → App registrations → click + New registration → type in Name → select Register
      
       
      
      copy Application (client) ID value and paste it as Full scope app - Client ID variable
      
      copy Directory (tenant) ID value and paste it as Full scope app - Tenant ID
      
       
   2. Add permissions: API permissions →  click + Add permission → Microsoft Graph → Application permissions → Sites → select Sites.FullControll.All → click Add permissions → click Grant admin consent for <<your tenant name>> → click Yes → verify the Status says “Granted for <<your tenant name>>”
      
       

2. 3. Create a secret: Certificates & secrets → click + New client secret → provide any description → Add
      
      copy the Value and paste it as Full scope app - Secret Value (it won’t be available once you leave the page)
      
       
3. Create the Service Principal App
   This is the app that will be used by the Alteryx users. 
    
   1. Create the app: http://Portal.azure.com  → Azure Active Directory → App  registrations → click + New registration → Type in Name → Select platform set to Web → type in http://localhost → click Register
      
       
      
      copy Application (client) ID value and paste it as App to assign to Site - App Client ID
      
      copy the Name of the app value and paste it as App to assign to Site - App name

1. 2. Add permissions: API permissions →  click + Add permission → Microsoft Graph → Application permissions → Sites → select Sites.Selected then click Grant admin consent for <<your tenant name>> → click Yes → verify the Status says “Granted for <<your tenant name>>”
      1. Note: For Sharepoint Lists, add Sites.Selected under Sharepoint API as well as Microsoft Graph

NOTE: If you are using Sharepoint tools version 2.6.0 onwards, you do NOT need to add the delegated permissions. If you are using a version lower than 2.6.0, you will need to add the delegated permissions as the user account will be used to configure the tool.

3. 3. Create a secret: Certificates & secrets → click + New client secret → provide any description → Add
      
      This Secret will be used later when accessing the Site using the SharePoint connector in Alteryx Designer. Thus we recommend setting the Expires value to its maximum, i.e. 24 months. Also, don’t forget to copy the Value as it won’t be available anymore once you leave the page. 
      
       
4. Get the Site ID
   There are probably other more convenient ways how to get the Site ID, but this is Alteryx, so we will use Alteryx for this. 
    
   1. Select the Site: drop SharePoint Input on canvas → Connect in the regular way as user → Select the Site you want to have access to using the Service Principal → click on canvas to get it saved
      
       
       
   2. Enable Display XML: Options → User Settings → Edit User Settings → Advanced → enable Display XML in Properties Window → Save → select SharePoint Input tool → click on canvas
      
       
       
   3. Get the Site ID from the workflow XML: XML View → locate <SiteGroupId> tag
      
       
      
      copy SiteGroupId tag value and paste it as Site to assign to the app - Site Group ID.
      
      
   4. If you are unable to get the SiteGroupID from within Designer, it can be found by populating the following URL in the address bar of your browser:
      
      https://[SharePoint URL]/sites/[Site Name]/_api/site/id
      (i.e. https://company,SharePoint.com/sites/CompanySite/_api/site/id)
      
      After pressing enter, you should see a webpage similar to this:
      
      
      
      The SiteGroupID is the last value found after the "m:type="Edm.Guid">" label. In the screenshot above. It's the value in the red rectangle.
       
5. Assign the App to the Site
   Let’s switch back to Postman and assign the App to the Site. For that, we need to obtain auth token first.
   
   After all the previous steps, the Variables should look similar to this:
   
    
   
   At this point, it is important to click the Save button, otherwise, the values won’t be available.
    
   1. Obtain access token: expand the Collection → open Get token item → press Send button
      
       
      
      copy the access_token value and paste it as Full scope app - Access token variable value (and press Save)
       
   2. Assign the App to the Site: open the Grant restricted item → press Send button
      
       
       
6. Use the App in Alteryx
   1. Log in with the Service Principal app: log out from the SharePoint Input tool → select the 2nd auth method → check the Use as Service Principal → click Connect → log in as user
      
       

For SharePoint tools version < 2.6.0

The user is still required to log in to be able to configure the tool, since the Service Principal is used only during the workflow execution. Even though the user is logging in, no user-related data is saved in the workflow. 
 

1. 2. Read from the assigned Site: select the Site the app was assigned to in the previous step (should be pre-selected) → select a Document library → select a csv or xlsx File → Run workflow
      
      expected result: Designer is able to read the file.
       
   3. Read from a non-assigned Site: select any other Site and a file in it and Run the workflow
      
      expected result: Designer is not able to read the file. The log says Error: SharePoint Input (1): Request forbidden -- authorization will not help.
       

For SharePoint version >= 2.6.0

You will not be able to select the available sites from the drop-down. Please refer to the following article to get the site and documents you need:

https://knowledge.alteryx.com/index/s/article/SharePoint-version-260-Using-Service-Principal-will-not-load-Sites