Contents x
    SharePoint Query Authentication Guide
      • Dark
        Light
      Contents

      SharePoint Query Authentication Guide

        • Dark
          Light
        Article Summary

        Overview

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

        Acquiring third-party credentials

        1. Open the Microsoft Azure Portal, and enter valid login credentials to continue. On the Microsoft Azure dashboard, click App registrations on the Azure services menu at the top. If App registrations isn't visible, click More services, on the right of the menu, for a longer list of options.
        2. On the App registrations page, click + New registration on the menu at the top of the page.
        3. On the Register an application page, give details for the following fields:
          • Name: Name the app.
          • Supported account types: Select Accounts in any organizational directory (Any Azure AD directory - multitenant) and personal Microsoft accounts (for example, Skype, Xbox).
          • Redirect URI: Select Web in the drop-down field and paste the Callback URL copied from the Manage OAuth dialog in Matillion ETL earlier. Note that although the page states this field is optional, you must complete it.
        4. Click Register.
        5. Your browser will redirect to the Overview page on the app's newly created dashboard. From here, copy the credentials to the right of Application (client) ID and Directory (tenant) ID for use later in authorizing for use in Matillion ETL.
        Warning

        When copying the credentials, some browsers may add a space to the end of the string. Watch out for this as it will cause the credentials to fail.

        1. Click Certificates & secrets on the menu on the left, and on the Certificates & secrets page click + New client secret.
        2. Edit the Add a client secret page by giving details for the following fields:
          • Description: Describe the client secret.
          • Expires: Use the Expires drop-down to select when the client secret should expire, then click Add.
        3. The Certificates & secrets menu then reloads, and the new client secret is visible in the list in the Client secrets tab. Copy the client secret Value, as you need it for authorizing for use in Matillion ETL.
        Warning
        • 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.

        1. Click API permissions on the menu on the left, then click + Add a permission to open the Request API permissions panel on the right of the screen.

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

        3. The SharePoint panel then opens. You can select either Delegated permissions or Application permissions here. Expand the lists and select permissions as required, then click Add permissions.

        4. Next, navigate to the Office 365 Home page, giving your sign-in 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

        1. Now return to the Manage OAuth dialog in Matillion ETL to complete the OAuth configuration.
        What's Next