Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

How to set up SharePoint Sites.Selected

VojtechT
Alteryx
Alteryx
Created

How to set up SharePoint Sites.Selected


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.
 

Prerequisites

 
  • 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
 
 

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 collectionEdit collection → switch to the Variables tab.

    image.gifimage.gif

    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

      image.gifimage.gif

      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>>”

      image.gifimage.gif
    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)

      image.gifimage.gif
  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

      image.gifimage.gif

      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
    2. Add permissions: API permissions →  click + Add permission → Microsoft Graph → Application permissions → Sites → select Sites.Selected → click Add permissions → in the same way add also these Delegated permissions: Files.Read.All, Files.ReadWrite.All, offline_access, openid, Sites.Read.All, User.Read → click Grant admin consent for <<your tenant name>> → click Yes → verify the Status says “Granted for <<your tenant name>>”

      image.gifimage.gif
    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

      image.gifimage.gif
       
    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

      image.gifimage.gif
       
    3. Get the Site ID from the workflow XML: XML View → locate <SiteGroupId> tag

      image.gifimage.gif

      copy SiteGroupId tag value and paste it as Site to assign to the app - Site Group ID
       
  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:

    image.pngimage.png

    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

      image.gifimage.gif

      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

      image.gifimage.gif
       
  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

      image.gifimage.gif

      The user is still required to log in in order 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. 
       
    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.
       


Attachments
Comments
Amol_Telore
11 - Bolide

It is very helpful. Thanks for sharing this.! 

NatSnook
8 - Asteroid

This would be extremely useful as we have a roadblock getting sites.all.permission granted. Can you tell me would this work from the gallery or would we face the issue of token expiring after 1hr?

Bill_Richardson
7 - Meteor

@VojtechT ,

 

Edit: I did get things to work.  Problem turned out to be the "full access" app had not been set with Application permission Sites.FullControl.All.  I'll leave my original question below and the error message it gives in case someone else runs into this.

 

Thanks for the article.  I got everything set up as you describe in the article.  I was able to get a token for the Full Scope app, but when I run the update for sites.selected, I get an error:

 

{
    "error": {
        "code""AccessDenied",
        "message""Either scp or roles claim need to be present in the token.",
        "innerError": {
            "date""2023-08-17T17:36:37",
            "request-id""f2a6f8a7-5108-41b7-864d-35bb1ce3ea90",
            "client-request-id""f2a6f8a7-5108-41b7-864d-35bb1ce3ea90"
        }
    }
}
 
I have double-checked all my settings and they seem to match what you describe in the article.
 
Any ideas?