SharePoint Query authentication guide

This is a step-by-step guide to acquiring credentials for authorizing the SharePoint Query component for use in Matillion ETL.

Overview

• The SharePoint Query connector uses OAuth for third-party authentication.
• While connector properties may differ between cloud data warehouses, the authentication process remains the same.
• Most third-party apps and services that connect to Microsoft data can be set up for use in Matillion ETL through the Microsoft Azure Portal using much of the same process.

---

Prerequisites

Begin by creating an OAuth entry in Matillion ETL, as described in Manage OAuth. You should then configure this OAuth entry using the SharePoint credentials, obtained as described below.

Follow the steps in Acquiring Azure credentials to access the Azure portal and create credentials. Then continue as per the instructions below.

---

Configure SharePoint API

1. In the Request API permissions panel, click SharePoint in the list of Microsoft APIs.

2. The SharePoint panel then opens. You can select either Delegated permissions or Application permissions here.

   Note

   When creating the OAuth connection, the following permissions are created as standard.

   • API Name: Office365 SharePoint Online | Claim Value: AllSites.Read | Type: Delegated
   • API Name: Microsoft Graph | Claim Value: offline_access | Type: Delegated

   Only add additional permissions if necessary.

3. If necessary, expand the lists and select permissions as required, then click Add permissions to add additional permissions.

4. Next, navigate to the Office 365 Home page, passing your login credentials if requested. Click the SharePoint tile to open your SharePoint dashboard. You can use the Search bar at the top of the page if the SharePoint tile is not immediately visible. The page's URL contains your SharePoint account URL and is required in authorizing for use in Matillion ETL. Copy the URL up to and including sharepoint.com, for example: https://companyname.sharepoint.com.
5. Now return to the Manage OAuth dialog in Matillion ETL to complete the OAuth configuration.

---

Troubleshooting SharePoint Query authentication

Error message: "The user or administrator has not consented to use the application with ID"

Solution: You can grant admin consent in the application by going to Enterprise Applications → Click Your App → Permissions → Grant Admin Consent. Alternatively, you can run the following command, replacing <tenant_id>, <client_id>, <redirect_URL> and <sharepoint_domain> with your own values:

https://login.microsoftonline.com/%3C<tenant_id>%3E/oauth2/v2.0/authorize?<client_id>=%3Cclientid%3E&response_type=code&redirect_uri=%3C<redirect_URL>%3E&response_mode=query&scope=https%3A%2F%2F%3C<sharepoint_domain>%3E%2FAllSites.Manage+offline_access&state=12345

For more information, read our corresponding Support article.

Columns in Sharepoint Query component have changed since the driver was last upgraded

Solution: In the Connection Options property of your SharePoint Query component, set the UseDisplayNames parameter value to False.