Configure OAuth M2M for SharePoint ingestion

For most users, Databricks recommends OAuth U2M: Databricks-managed, which requires no Azure app registration and is managed entirely by Databricks. Use M2M for fully automated production pipelines that run without user interaction.

Which permission model should I use?

M2M authentication supports the following permission models in Microsoft Azure:

  • Sites.Read.All: Grants access to all SharePoint sites in your organization. This option has fewer setup steps but provides broader access.
  • Sites.Selected: Grants access only to specific SharePoint sites. This requires additional configuration but follows the principle of least privilege.

Databricks recommends using Sites.Selected when possible to limit the service principal's access to only the sites you need to ingest.

Which credential type should I use?

M2M authentication supports the following credential types for the Microsoft Entra ID application:

  • Certificate: An X.509 certificate and its private key.
  • Client secret: A secret string that you generate in the Azure portal.

Databricks recommends using a certificate. A connection uses one credential type, either a certificate or a client secret.

Prerequisites

  • Admin privileges in your Microsoft Entra ID tenant.

Sites.Read.All permissions

This option grants the service principal access to all SharePoint sites in your organization.

Step 1: Get the SharePoint site ID

  1. Visit the desired SharePoint site in your browser.
  2. Append /_api/site/id to the URL.
  3. Type Enter.

Step 2: Get SharePoint drive names (optional)

If you want to ingest all drives and documents in your SharePoint site, skip this step. If you only want to ingest a subset of the drives, you must collect their names.

The drive names are listed in the left-hand menu. There's a default drive called Documents in each site. However, your organization might have additional drives. For example, the drives in the following screenshot include doclib1, subsite1doclib1, and more.

View SharePoint drives

Some drives might be hidden from the list. The drive creator can configure this in the drive settings. In this case, hidden drives might be visible in the Site contents section.

View hidden SharePoint drives

Step 3: Create a Microsoft Entra ID application

This step creates an application registration that can access the SharePoint files using a service principal.

  1. In the Microsoft Azure portal (https://portal.azure.com), click Microsoft Entra ID. You might have to search for "Microsoft Entra ID".

    Azure portal: Entra ID card

  2. In the left sidebar, under the Manage section, click App Registrations.

  3. Click New registration.

    New registration button for Entra ID app

  4. In the Register an application form, specify the following:

    • A name for your application (for example, "Databricks SharePoint Ingestion").
    • Whether you want other tenants to access this application.

    You don't need to specify a redirect URL for M2M authentication.

  5. Click Register. You're redirected to the app details page.

  6. Make a note of the following values:

    • Application (client) ID
    • Directory (tenant) ID

Next, add a client credential to the application. Follow the steps for the credential type that you chose. See Which credential type should I use?.

Certificate

The connection requires an X.509 certificate in PEM format and an unencrypted RSA private key in PKCS #8 PEM format. Encrypted private keys are not supported.

  1. Generate a certificate and a private key. For example, the following OpenSSL command creates a self-signed certificate that is valid for two years:

    openssl req -x509 -newkey rsa:2048 -sha256 -days 730 -nodes \
  -keyout ~/databricks_key.pem -out ~/databricks_cert.pem \
  -subj "/CN=databricks-sharepoint"

    The command creates databricks_cert.pem (the public certificate) and databricks_key.pem (the private key) in your home directory. You can also use a certificate issued by your organization's certificate authority.

  2. If you use a private key from another source, confirm that the file starts with the BEGIN PRIVATE KEY delimiter. If it starts with the BEGIN RSA PRIVATE KEY delimiter, convert it to PKCS #8 format and use the converted file as the private key:

    openssl pkcs8 -topk8 -nocrypt -in <private-key-file> -out ~/databricks_key.pem

  3. In the left sidebar of the app registration page, under Manage, click Certificates & secrets.

  4. Click the Certificates tab.

  5. Click Upload certificate.

  6. Select databricks_cert.pem, add a description, and click Add.

    Upload certificate panel on the Certificates & secrets page of the app registration

  7. Store the private key file securely. You enter the certificate and the private key when you create the connection.

Certificates expire. Before the certificate expires, upload a new certificate to the application and update the connection with the new certificate and private key.

Client secret

  1. Click Client credentials : Add a certificate or secret.

  2. Click + New client secret.

    + New client secret button

  3. Add a description.

  4. Click Add. The updated list of client secrets displays.

  5. Copy the client secret value and store it securely. After you leave the page, you can't access the client secret.

Step 4: Configure API permissions

Grant the application the necessary permissions to read SharePoint files.

  1. In the app registration page, click API permissions in the left-hand menu.

  2. Click + Add a permission.

  3. In the Request API permissions panel, click Microsoft Graph.

  4. Click Application permissions.

  5. Search for and select the following permissions:

    • Sites.Read.All
    • Files.Read.All

  6. Click Add permissions.

  7. Click Grant admin consent for [your organization].

  8. Click Yes to confirm.

    The permissions list shows a green checkmark in the Status column indicating admin consent has been granted.

Step 5: Create a connection in Azure Databricks

  1. In Catalog Explorer, click External data in the left-hand menu.

  2. Click Create connection.

  3. In the Create connection dialog, specify the following:

    • Connection name: A unique name for your connection
    • Connection type: Microsoft SharePoint
    • Authentication type: OAuth Machine to Machine
    • Client ID: The Application (client) ID from Step 3
    • Use certificate: Select this checkbox to authenticate with a certificate. Leave it cleared to authenticate with a client secret.
    • Client secret: The client secret value from Step 3. This field appears only when Use certificate is cleared.
    • Client certificate pem: The contents of databricks_cert.pem from Step 3, including the BEGIN CERTIFICATE and END CERTIFICATE delimiters. Required when Use certificate is selected.
    • Client private key pem: The contents of databricks_key.pem from Step 3, including the BEGIN PRIVATE KEY and END PRIVATE KEY delimiters. Required when Use certificate is selected.
    • Domain: Your SharePoint domain in the format https://YOURINSTANCE.sharepoint.com
    • Tenant ID: The Directory (tenant) ID from Step 3

  4. Click Create.

Sites.Selected permissions

This option restricts the service principal's access to specific SharePoint sites only.

Steps 1-3: Complete the basic setup

Follow Steps 1-3 in the Sites.Read.All section. These steps are the same for both permission models.

  1. Get the SharePoint site ID.
  2. Get SharePoint drive names (optional).
  3. Create a Microsoft Entra ID application.

Step 4: Configure API permissions for Sites.Selected

Grant the application restricted permissions that require additional site-specific authorization.

  1. In the app registration page, click API permissions in the left-hand menu.

  2. Click + Add a permission.

  3. In the Request API permissions panel, click Microsoft Graph.

  4. Click Application permissions.

  5. Search for and select Sites.Selected.

  6. Click Add permissions.

  7. Click Grant admin consent for [your organization].

    This step requires admin privileges in your Microsoft Entra ID tenant.

  8. Click Yes to confirm.

    The permissions list shows a green checkmark in the Status column indicating admin consent has been granted.

Step 4b: Grant site-specific permissions

After configuring Sites.Selected in Azure, you must explicitly grant the application access to specific SharePoint sites. You can do this using either Microsoft Graph Explorer or a Python notebook.

Microsoft Graph Explorer

  1. Go to Microsoft Graph Explorer.

  2. Sign in with an account that has admin permissions for your SharePoint site.

  3. Click Modify permissions and consent to the required permissions (Sites.FullControl.All).

  4. Change the HTTP method to POST.

  5. Enter the following URL, replacing {site_id} with your SharePoint site ID from Step 1:

    https://graph.microsoft.com/v1.0/sites/{site_id}/permissions

  6. In the Request body section, paste the following JSON, replacing the placeholder values:

    {
  "roles": ["read"],
  "grantedToIdentities": [
    {
      "application": {
        "id": "<YOUR_CLIENT_ID>",
        "displayName": "<YOUR_APP_NAME>"
      }
    }
  ]
}

    Replace:

    • <YOUR_CLIENT_ID>: The Application (client) ID from Step 3
    • <YOUR_APP_NAME>: The name of your application registration

  7. Click Run query.

    A successful response indicates the permission has been granted.

Python notebook

  1. Create a new notebook in your Azure Databricks workspace.

  2. Install the MSAL library by running the following command in a cell:

    %pip install msal

  3. In a new cell, paste the following code and replace the placeholder values:

    import msal
import requests

# Configuration - Replace these values
TENANT_ID = "<YOUR_TENANT_ID>"  # Directory (tenant) ID from Step 3
ADMIN_CLIENT_ID = "<ADMIN_CLIENT_ID>"  # Client ID of an app with admin consent
ADMIN_CLIENT_SECRET = "<ADMIN_CLIENT_SECRET>"  # Client secret of admin app
TARGET_APP_CLIENT_ID = "<TARGET_CLIENT_ID>"  # Client ID from Step 3
TARGET_APP_DISPLAY_NAME = "<TARGET_APP_NAME>"  # Name of your app
SITE_ID = "<YOUR_SITE_ID>"  # SharePoint site ID from Step 1

# Authenticate using MSAL
authority = f"https://login.microsoftonline.com/{TENANT_ID}"
app = msal.ConfidentialClientApplication(
    ADMIN_CLIENT_ID,
    authority=authority,
    client_credential=ADMIN_CLIENT_SECRET
)

# Acquire token
token_result = app.acquire_token_for_client(
    scopes=["https://graph.microsoft.com/.default"]
)

if "access_token" in token_result:
    access_token = token_result["access_token"]

    # Grant site permissions
    grant_url = f"https://graph.microsoft.com/v1.0/sites/{SITE_ID}/permissions"
    headers = {
        "Authorization": f"Bearer {access_token}",
        "Content-Type": "application/json"
    }
    grant_payload = {
        "roles": ["read"],
        "grantedToIdentities": [
            {
                "application": {
                    "id": TARGET_APP_CLIENT_ID,
                    "displayName": TARGET_APP_DISPLAY_NAME
                }
            }
        ]
    }

    response = requests.post(grant_url, headers=headers, json=grant_payload)

    if response.status_code in [200, 201]:
        print("Successfully granted site permissions")
        print(response.json())
    else:
        print(f"Error: {response.status_code}")
        print(response.text)
else:
    print("Failed to acquire token")
    print(token_result.get("error"))
    print(token_result.get("error_description"))

  4. Run the cell. A successful response indicates the permission has been granted.

Step 5: Create a connection in Azure Databricks

Follow the Step 5: Create a connection in Azure Databricks steps to create the connection in Azure Databricks using the credentials from your application registration.

Next steps

  1. Ingest data from SharePoint
  2. Common pipeline maintenance tasks
  • Last updated on