Skip to content

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.

  • 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 valid SharePoint credentials, obtained as described below.

Acquire SharePoint credentials

This section explains how to complete the following fields in the Configure OAuth dialog when following the instructions in Manage OAuth for SharePoint Query:

  • Client ID
  • Client Secret
  • URL
  • Scope

Client ID

  1. Log in to the Microsoft Azure Portal.
  2. From the Azure services menu, click App registrations. If App registrations is not visible, click More services on the right of the menu for a longer list of options.
  3. On the App registrations page, click + New registration.
  4. On the Register an application page, provide details for the following fields:
    • Name: A name for the app.
    • Supported account types: Select Accounts in any organizational directory (Any Azure AD directory - Multitenant).
    • Redirect URI: Select Web in the drop-down field and paste the Callback URL copied from the Manage OAuth window in Matillion ETL earlier. Note that although the page states this field is optional, you must complete it.
  5. Click Register.
  6. Your browser will redirect to the Overview page for your new app. From here, copy the credentials to the right of Application (client) ID and (if also required) Directory (tenant) ID, as these credentials will be required later when authorizing for use in Matillion ETL.
    • When copying the credentials, some browsers may add a space to the end of the string. This will cause the credentials to fail.

Client Secret

  1. From the Azure Portal homepage, click App registrations.
  2. Select your app.
  3. In your app's overview page, expand the Manage item in the left sidebar and then click Authentication.
  4. Scroll down to the Implicit grant and hybrid flows section, and select the checkbox next to ID tokens (used for implicit and hybrid flows), then click Save.
  5. Click Certificates & secrets, also located in the Manage item in the left sidebar.
  6. Click + New client secret.
  7. The Add a client secret dialog will display on the right of your screen. Provide details for the following fields:
    • Description: Provide a description of the client secret.
    • Expires: Use the Expires dropdown to select when the client secret should expire.
  8. Click Add.
  9. You will be returned to the Certificates and secrets page, where the new client secret will appear in the list in the Client secrets tab. Copy the client secret Value, as it will be required when authorizing for use in Matillion ETL.
    • Make sure to copy the client secret right away as it may appear only once.
    • Additionally, when copying the client secret, some browsers may add a space to the end of the string. This will cause the credentials to fail.

URL

  1. Navigate to the Office 365 Home page, passing your login credentials if requested.
  2. 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.
  3. The page's URL contains your SharePoint account URL and is required when authorizing SharePoint for use with Matillion ETL. Copy the URL up to and including sharepoint.com, for example: https://companyname.sharepoint.com.

Scope

The Scope parameter lets you specify the permissions that will be requested when authenticating with SharePoint. This determines what actions your connection can perform within SharePoint.

You can set the Scope field in Configure OAuth to any of these values:

  • NONE
  • AllSites.Manage
  • AllSites.Read
  • AllSites.Write
  • .default

To set multiple scopes, separate each with spaces.

The default value is AllSites.Read, not .default or NONE. The field is pre-populated with AllSites.Read. Read Scope for more information about possible values.

You must also set permissions in Azure following these instructions:

  1. From the Azure Portal homepage, click App registrations.
  2. Select your app.
  3. Click API permissions, located in the Manage item in the left sidebar of your app.
  4. Click + Add a permission to open the Request API permissions dialog on the right of your screen.
  5. In the Request API permissions dialog, click SharePoint in the list of Microsoft APIs.
  6. The SharePoint dialog will open. Select Application permissions.

  7. Select a permission such as Sites.Read.All to match the AllSites.Read Scope in Matillion ETL.

    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.

  8. Click Add permissions.

Return to Matillion ETL and add your Client ID, Client Secret, URL, and Scopes if you haven't already to the Configure OAuth dialog when following the instructions in Manage OAuth for SharePoint Query.

Troubleshooting SharePoint Query authentication

This section offers troubleshooting guidance for common errors found when authenticating to SharePoint.

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 the 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.

© Copyright 2025 Matillion Ltd | Legal | Privacy Policy | Disclaimer | Modern Slavery Statement