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

[Ray_Minds]

This blog outlines how to build a connection from 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. Process explained below.

3. ”Azure Key Vault” with the necessary permission. Its 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 step 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.

  

• 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 step below.

• Go to SharePoint URL like mentioned in the 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 necessary permission. Then click on 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: - https://learn.microsoft.com/en-us/azure/data- factory/media/connector-sharepoint-online-list/sharepoint-online-grant- permission-admin.png

• Open “Azure Data Factory (ADF)/Azure Synapse Analytics” to create “SharePoint” and “Azure Key Vault” Linked service. 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.i ii). 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”.

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

  For more information or if you have any questions, please visit our website
  https://www.rayminds.com/post/creating_linkedservices_for_sharepoint_onlinelist_in_azuredatafactory_...

[Anonymous]

Hi @Ray_Minds,

Thank you for providing detailed steps on creating Linked Services for SharePoint Online List in Azure Data Factory and Synapse Analytics.
Please continue using Microsoft community forum.

Regards,
Pavan.