Hello We are trying to setup the flow which will copy files from sharepoint online site to azure datalake storage. As per my understanding there are 2 options : Using ADF to pull the files as mentioned in the link below : https://zcusa.951200.xyz/en-us/azure/data-factory/connector-sharepoint-online-list?tabs=data-factory Here I have few questions : We are not sure when the files would arrive in sharepoint and sometimes the files may be delayed by 60 days so it may not make sense to schedule it in ADF. Is that correct understanding or is there a way ? If we use service principal and secret. Do we need any license ? Also, is the service principal required specific API permission in ENTRA/AD ? Apart from sharepoint permission mentioned do we need any permission for the storage account ? If yes, how can we set this permission set to specific container/folder and what would be the least privilege permission ? Using Power Automate flow via 2 way connection - Sharepoint and Azure Storage connector. Can we use service principal and secret to authenticate sharepoint online and azure storage ? Do we need any licenses - sharepoint, AD/Entra, power automate ? Can we setup the pipeline using first option as ADF and just trigger the ADF pipeline from power automate. What are the permissions required - AD/Entra( API permission for sharepoint etc), power automate, sharepoint site, azure storage. Thanks in advance Regards

Hi Sourav , Welcome to Microsoft Q&A Forum, thank you for posting your query here! When setting up a flow to copy files from SharePoint Online to Azure Data Lake Storage, you can go through two approaches, each with its own considerations. 1. Using Azure Data Factory (ADF) Scheduling and File Arrival: If files can be delayed by up to 60 days, scheduling a regular ADF pipeline might not be efficient. Instead, you can use event-driven triggers or a combination of ADF with Azure Logic Apps to check for new files periodically and trigger the pipeline only when new files are detected. For Reference: https://zcusa.951200.xyz/en-us/answers/questions/1513111/how-to-copy-folders-and-files-from-a-sharepoint-si Licensing and Permissions: Service Principal and Secret: You do not need additional licenses for using a service principal. However, the service principal will need specific API permissions in Azure Active Directory (AD) to access SharePoint Online. These permissions include Sites.Read.All for reading files from SharePoint. Storage Account Permissions: You will need to grant the service principal access to the Azure Data Lake Storage account. The least privilege permission required would be Storage Blob DataContributor at the container or folder level. You can set these permissions in the Azure portal under the Access Control (IAM) section of your storage account. 2. Using Power Automate Authentication: Service Principal and Secret: Power Automate can use service principal authentication for both SharePoint Online and Azure Storage. You will need to register an app in Azure AD and grant it the necessary permissions for both services. Licensing: SharePoint and AD/Entra: No additional licenses are required beyond what you already have for SharePoint and Azure AD. Depending on the complexity and volume of your flows, you might need a premium Power Automate license. Triggering ADF from Power Automate: you can set up a Power Automate flow to trigger an ADF pipeline. This can be useful if you want to combine the flexibility of Power Automate with the powerful data integration capabilities of ADF. If you have any other questions or are still running into more issues, let me know in the "comments" and I would be happy to help you, Please do not forget to "Accept the answer” and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.

Copy Files from sharepoint online site to azure datalake storage

Sourav 105

Hello

We are trying to setup the flow which will copy files from sharepoint online site to azure datalake storage.

As per my understanding there are 2 options :

Using ADF to pull the files as mentioned in the link below :

https://zcusa.951200.xyz/en-us/azure/data-factory/connector-sharepoint-online-list?tabs=data-factory

Here I have few questions :

We are not sure when the files would arrive in sharepoint and sometimes the files may be delayed by 60 days so it may not make sense to schedule it in ADF. Is that correct understanding or is there a way ?
If we use service principal and secret. Do we need any license ? Also, is the service principal required specific API permission in ENTRA/AD ?
Apart from sharepoint permission mentioned do we need any permission for the storage account ? If yes, how can we set this permission set to specific container/folder and what would be the least privilege permission ?

Using Power Automate flow via 2 way connection - Sharepoint and Azure Storage connector.
- Can we use service principal and secret to authenticate sharepoint online and azure storage ?
- Do we need any licenses - sharepoint, AD/Entra, power automate ?
- Can we setup the pipeline using first option as ADF and just trigger the ADF pipeline from power automate.
- What are the permissions required - AD/Entra( API permission for sharepoint etc), power automate, sharepoint site, azure storage.

Thanks in advance

Regards

Keshavulu Dasari 1,730 Reputation points Microsoft Vendor

2024-11-06T23:34:12.36+00:00

Hi Sourav,
If you have any other questions or are still running into more issues, let me know in the "comments" and I would be happy to help you,

Please do not forget to "Accept the answer” and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.
Keshavulu Dasari 1,730 Reputation points Microsoft Vendor

2024-11-12T17:47:07.4233333+00:00

Hi Sourav,
If you have any other questions or are still running into more issues, let me know in the "comments" and I would be happy to help you,

Please do not forget to "Accept the answer” and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.

1 answer

Keshavulu Dasari 1,730 Reputation points Microsoft Vendor

2024-11-06T23:31:00.34+00:00

Hi Sourav,
Welcome to Microsoft Q&A Forum, thank you for posting your query here!
When setting up a flow to copy files from SharePoint Online to Azure Data Lake Storage, you can go through two approaches, each with its own considerations.
1. Using Azure Data Factory (ADF)

Scheduling and File Arrival: If files can be delayed by up to 60 days, scheduling a regular ADF pipeline might not be efficient. Instead, you can use event-driven triggers or a combination of ADF with Azure Logic Apps to check for new files periodically and trigger the pipeline only when new files are detected.
For Reference:
https://zcusa.951200.xyz/en-us/answers/questions/1513111/how-to-copy-folders-and-files-from-a-sharepoint-si

Licensing and Permissions:
Service Principal and Secret: You do not need additional licenses for using a service principal. However, the service principal will need specific API permissions in Azure Active Directory (AD) to access SharePoint Online. These permissions include Sites.Read.All for reading files from SharePoint.

Storage Account Permissions: You will need to grant the service principal access to the Azure Data Lake Storage account. The least privilege permission required would be Storage Blob DataContributor at the container or folder level. You can set these permissions in the Azure portal under the Access Control (IAM) section of your storage account.

2. Using Power Automate

Authentication:

Service Principal and Secret: Power Automate can use service principal authentication for both SharePoint Online and Azure Storage. You will need to register an app in Azure AD and grant it the necessary permissions for both services.

Licensing:

SharePoint and AD/Entra: No additional licenses are required beyond what you already have for SharePoint and Azure AD.
Depending on the complexity and volume of your flows, you might need a premium Power Automate license.

Triggering ADF from Power Automate: you can set up a Power Automate flow to trigger an ADF pipeline. This can be useful if you want to combine the flexibility of Power Automate with the powerful data integration capabilities of ADF.

If you have any other questions or are still running into more issues, let me know in the "comments" and I would be happy to help you,

Please do not forget to "Accept the answer” and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.
Please sign in to rate this answer.

1 person found this answer helpful.
Sourav 105 Reputation points

2024-11-07T06:51:55.2333333+00:00

Hello-

On option 2 ,When you say grant necessary permission for both the services, can you explain where and what permissions are required?

Also, what connector we can use for power automate trigger the ADF pipeline and is the understanding correct that we follow the below link and just trigger the pipeline from power automate instead of ADF schedule ?

https://zcusa.951200.xyz/en-us/azure/data-factory/connector-sharepoint-online-list?tabs=data-factory

What are the permissions required - AD/Entra( API permission for sharepoint etc), power automate, sharepoint site, azure storage and ADF ?

Keshavulu Dasari 1,730 Reputation points Microsoft Vendor

2024-11-07T19:00:55.1566667+00:00

Hi Sourav,
The necessary permissions and steps for using Power Automate to trigger an Azure (ADF) pipeline.

Permissions Required

1. Azure Active Directory (AD/Entra)

Service Principal Permissions:

SharePoint Online: Grant the service principal Sites.Read.All and Sites.ReadWrite.All permissions. These permissions allow the service principal to read and write to all site collections.

Azure Data Factory: Grant the service principal Data Factory Contributor role to allow it to create and manage ADF pipelines.

Azure Storage: Grant the service principal Storage Blob Data Contributor role for the specific container or folder in your Azure Storage account.

2. Power Automate

Connector Permissions: Ensure that the service principal has the necessary permissions to use the SharePoint and Azure Storage connectors. This typically involves having the appropriate licenses for Power Automate, which supports premium connectors.

3. SharePoint Site

Site Permissions: The service principal needs at least Read permissions on the SharePoint site to access the files. If the flow needs to move or delete files, contribute permissions are required.

4. Azure Storage

Storage Account Permissions: As mentioned, the service principal should have Storage Blob Data Contributor permissions for the specific container or folder where the files will be stored.

5. Azure Data Factory

Pipeline Permissions: The service principal should have Data Factory Contributor role to create and manage pipelines within the ADF instance.

Setting Up Power Automate to Trigger ADF Pipeline

1.Granting Permissions:

Azure AD: Navigate to Azure AD, select App registrations, find your service principal, and assign the necessary API permissions for SharePoint and Azure Storage.

Azure Storage: Go to your storage account, select Access Control (IAM), and assign the Storage Blob Data Contributor role to the service principal.

ADF: In the ADF instance, assign the Data Factory Contributor role to the service principal.

2.Using Power Automate to Trigger ADF Pipeline:

Connector: Use the HTTP connector in Power Automate to trigger the ADF pipeline. You can create a custom connector if needed.

Triggering the Pipeline: Follow the steps in the Azure Data Factory documentation to set up the pipeline. Instead of scheduling it in ADF, you can use Power Automate to trigger the pipeline by making an HTTP POST request to the ADF REST API.

Steps to Trigger ADF Pipeline from Power Automate

Create a Power Automate Flow:

Trigger: Use a trigger like When a file is created or modified in SharePoint.

HTTP Action: Add an HTTP action to call the ADF pipeline. Configure it with the ADF REST API endpoint to trigger the pipeline.

Configure HTTP Action:

Method: POST

Headers: Include the Authorization header with the Bearer token.

Body: Include any necessary parameters for the pipeline run.

By following these steps, you can effectively use Power Automate to trigger an ADF pipeline based on events in SharePoint, ensuring that your data flow is responsive to file arrivals without relying on a fixed schedule.

If you have any other questions or are still running into more issues, let me know in the "comments" and I would be happy to help you.

Keshavulu Dasari 1,730 Reputation points Microsoft Vendor

2024-11-08T16:25:16.5366667+00:00

Hi Sourav,
Checking in to see if the response helped. If you have any questions, let me know in the "comments" and I would be happy to help you.

Sourav 105 Reputation points

2024-11-11T06:44:04.5533333+00:00

The above comment says appropriate permission. I want to know how to connect SharePoint from powerautomate to read the files on SharePoint online. I want to know the exact connector, can we use service principal if yes would there be any licence requirement? What type of license is required by powerautomate and SharePoint if we use service principal ? Please give exact and clear information instead of generic comment or chat gpt generated.

Keshavulu Dasari 1,730 Reputation points Microsoft Vendor

2024-11-11T13:46:40.4233333+00:00

Hi Sourav,
The specifics of connecting SharePoint Online to Power Automate, using a service principal, and the licensing requirements.

In Power Automate, you can use the SharePoint connector to interact with SharePoint Online. This connector allows you to create flows that can read, create, update, and delete items in SharePoint lists and libraries Triggers: Some common triggers include “When a file is created (properties only)” and "When a file is created or modified (properties only)"s only)".

Actions: Actions include “Get file content,” “Get file metadata,” and "Create file" Permissions: The service principal needs Sites.Read.All permissionto read files from SharePoint Online, If you are triggering ADF pipelines, the service principal should have the Data Factory Contributor role, For accessing Azure Storage, the service principal needs the Storage Blob Data Contributor role.

Licensing Requirements

Power Automate:

License for Service Principal: A service principal itself does not require a user license, if your flow uses premium connectors (like the SharePoint connector), you will need a Power Automate per-flow license or a Power Automate Process license.

User License: If the flow is owned by a user, that user needs a Power Automate Premium license.

SharePoint:

License: You need a valid Microsoft 365 license that includes SharePoint Online. This could be an E3, E5, or similar license

If you have any other questions or are still running into more issues, let me know in the "comments" and I would be happy to help you
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Copy Files from sharepoint online site to azure datalake storage

1 answer

Your answer