Access SharePoint file from Azure Data Factory

Thank you Vinodh247 for the detailed debug instructions. This is the first time I try anything with Azure Data Factory, so please bear with me. First let me tell you what I have been able to confirm. I also I some questions about how to check some other things.

• I assigned the Sites.ReadWrite.All permission and admin consented. As the error seems to indicate that SharePoint was looking for read-only access, I also tried the Sites.Read.All permission which resulted in the same error.
• I decoded the token and everything looks in order. I tried to change the scope used to generate the token from .default to Sites.Read.All, but this did not make a difference.
• I copy and pasted the URL directly into the browser and it successfully downloaded the CSV file.

Some questions and comments on the other advice.

• The SharePoint site collections is a Teams SharePoint site collections and the Teams owners / members have access to the site collection. I know how to add users to the Teams site collections, either through Teams member management, or directly through the SharePoint admin portal, but I am not sure about adding an Enterprise Application.
• I think when I started out I was looking at a linked service, but I didn't pursue it because I vaguely remember it didn't support service principal authentication. I need to have another look at whether it supports OAuth.
• I was unable to find any logs which gave me information on the 401 error. The audit log only seems to give log entries for successful access to a file.
• The file is very small so I don't expect timeout to be a problem. I also tried a Web activity which uses the Service Principal authentication with a client id / client secret, but that didn't seem to make a difference.

Thanks for all the advice.

@TYO Based on your response, it seems that you have already confirmed that the Sites.ReadWrite.All permission is granted and admin-consented, and that the access token you receive using the service principal authentication has the correct permissions.

Regarding your questions and comments on the other advice:

• To add an Enterprise Application to the Teams SharePoint site collection, you can follow these steps:
  1. Go to the SharePoint admin center.
     1. Select the site collection where you want to add the Enterprise Application.
        1. Click on "Access control" and then "Add a user".
           1. Enter the name or email address of the Enterprise Application and select the appropriate permission level.
           • Regarding the HTTP Linked Service in ADF, it does support OAuth2 authentication. You can configure the Linked Service to use OAuth2 and provide the necessary details for the service principal.
           • If you were unable to find any logs that gave you information on the 401 error, you can try turning on detailed logging in your ADF pipeline to capture more information about the failure. You can also check the AAD logs for more details on the 401 error.

Based on the information you have provided, it seems that the issue might be related to the SharePoint URL or the way the Web Activity is configured. You can try the following steps to further debug the issue:

• Double-check the SharePoint URL and ensure that it's correct and accessible.
• Try creating an HTTP Linked Service in ADF for connecting to the SharePoint site and configure it to use OAuth2 authentication.
• Turn on detailed logging in your ADF pipeline to capture more information about the failure.
• Check the AAD logs for more details on the 401 error.

I hope this helps you resolve the issue and successfully load the CSV file stored in SharePoint into an Azure SQL Server table. Let me know if you have any further questions or concerns.