Setting up Azure Data Factory with SharePoint Lists (Linked Service)

Prerequisites

• Entra ID App Registration.
• .pfx for upload into ADF (self-signed or from a CA)
• .crt for upload into App Registration

?Topics

• I will show you how to create an app registration that will be used for authentication.
• Add site(s) to the app registration and permission level for the sites.
• Setup the ADF Linked Service for the SharePoint List(s)

?

Why use a service principal certificate vs service principal key

The service principal key is marked as legacy and probably won’t work by the time you’re reading this.

You should/need to use the service principal certificate for the linked service authentication.

?

Setup:

New Microsoft Entra ID App Registration

This will be needed to allow ADF access to select SharePoint Site List(s)

Add the API Permission - SharePoint Sites.Selected

Select SharePoint

Choose Application Permission

Add the Sites.Selected permission - This will limit permissions to only sites we add to this app registration.

To commit the changes - Grant admin consent

Next add .crt (public key) certificate file

Choose Certificates & secrets - Upload certificate

Use your .cer file that has the public key

Once complete you should see your uploaded certificate

Create Azure Data Factory linked server using .pfx certificate

We need to create the new linked service

Select SharePoint List as the type

We will be using the Service Principal Certificate Option in the New linked service dialog.

Upload .pfx (with key type) and add .pfx file password - test connection

For best practice use an Azure Key Vault to feed these values.

You now have the linked service setup

Adding Sites and permissions to our application registration

We need add sites and access level to this app registration

Get application id and tenant id

In PowerShell use PnP to add the sites and permission - This adds one site with read write permissions.

First connect to SharePoint with PnP

Second add the permissions to the app we registered (see PS below)

This bind your permission to the custom Entra ID application.

Connect-PnPOnline -Url $adminURL -ClientId $clientIDPNPDelegated -Tenant $tenant -Interactive

Grant-PnPAzureADAppSitePermission -AppId "yourappid" -DisplayName "SPO-ADF_SelectSites_Samples" -Site "https://myTenant.sharepoint.com/sites/Sample-AzureDataFactory" -Permissions Write