Creating Linked Services for SharePoint Online List in Azure Data Factory and Synapse Analytics

By - Bipul

Overview: This blog outlines how to build a connection from the SharePoint Online List in the Azure Data Factory (ADF). Here we will go through the  tools, subscription, and configuration required to connect share points online to Azure using ADF/SynapsePre-Requisites:

1. Azure Data Factory (ADF)/Synapse instance setup should be ready.

2. ”App Registration” with the necessary permission. It's required for each application to read and extract the data from the source. The process is explained below.

3. ”Azure Key Vault” with the necessary permission. It's

4. required to store the client's secret value in a secure way. The process explained below.

Follow the step configuration below:

• First, we would be creating an “App Registration“ in the “Azure AD” and take a note below value, it will be useful for upcoming steps.  

•    Tenant ID (known as Directory [Tenant] ID).

•    Service Principal ID (known as Application [Client] ID).

•    Service Principal Key (Known as Application Key or Client Secret).
  

  

• Store a ”Client Secret (Service Principal Key)" value in the ”Azure Key Vault” to build a secure connection between the applications. Please follow the steps below to configure “Azure Key Vault”.

• Navigate to the “Key Vault” in the Azure portal then click on the secret option which is under objects as seen in the snapshot below.
  

  

• To create a new secret, please click on the “Generate/import” option as seen in the snapshot below.
  

  

•  Enter your “Service Principal Key (Client Secret)” and configure the expiration date. Then click on Save.
  

  

• Now, we need to give a grant permission in SharePoint as follows steps below.

• Go to the SharePoint URL mentioned below. http://rayminds.sharepoint.com/site/sitename/_layouts/15/appinv.aspx Note:- Replace with your site URL before the “/_layouts”.

• Enter the “Principal ID (Application ID)” to grant the necessary permission. Then click on the Lookup option to retrieve the application detail from the azure and configuration with the required permission (e.g.: - Read, Write) as seen in the snapshot below.
  

  

    Image reference: - Microsoft

• Open “Azure Data Factory (ADF)/Azure Synapse Analytics” to create “SharePoint” and “Azure Key Vault” Linked services. Follow the steps below.

• Create “Azure Key Vault” Linked services. 

• Go to Manage >>>> Linked services >>>> click on New as seen in the below snapshot. Search “Azure Key Vault” and select it then click on continue.
  

  

  Configure the Linked service with “Azure Key Vault” information like below i). Name:- “Azure_Key_Vault”; Replace with your name. ii). BaseURL:- “https://EUWE01DEV.vault.azure.net/ “; Replace with your URL. iii). Two types of authentications will be available:- 

 a). System Assigned Managed Identity.    b). User Assigned Managed Identity.

Here we choose the “User Assigned Managed Identity”. You will use it as per your configuration/requirement.

iv). We need to select the credential.

v). Click on “Test connection”. If successful, then click on “Create”.

• Create a “SharePoint” Linked service. Go to Manage >>>> Linked services >>>> click on New as seen in the below snapshot.
  

  

• Search the “SharePoint” and select it then click on continue.
  

  

  
  

• Configure the Linked service with “SharePoint” information like below. i). Enter a Name: “ls_sharepointonline”; Replace it with your name. ii). Select the Integration runtime; Replace it with your integration runtime. iii). Site URL: - http://rayminds.sharepoint/global/arrival/ iv). Enter your “Tenant ID”. v). Enter your “Principal ID”
  

  

  .vi). We have two methods to authenticate the ”Principal Key”.     a). Choose the ”Service Principal Key” (By Default). Enter the principal key directly.  b). Choose the ”Azure Key Vault” to make a secure connection and configure the ”Azure Key Vault”. Generally, we would be going to this option for security purposes.
  

  

vii). Click on “Test connection”. If successful, then click on “Create”.

Additional Notes

• Authentication: Depending on your organization's security policies, you may need to coordinate with your Azure AD administrator to register an Azure AD application and grant it the necessary permissions to access SharePoint Online.

• Permissions: Ensure that the service account or application used for authentication has sufficient permissions in SharePoint Online to access the lists or folders you intend to read from or write to.

• Dataset:- Configure the dataset with details like:

• Linked Service: Select the SharePoint Online linked service you created.

• Folder or List URL: URL to the SharePoint Online folder or list you want to copy data from.

Pipelines: - Configure the Source and Sink settings of the Copy Data activity:

• Source dataset: Select the dataset being your SharePoint Online data.

• Sink dataset: Choose where you want to copy the data to (e.g., Azure Blob Storage, Azure SQL Database, etc.).