0

I am trying to move files between Azure Data Factory and SharePoint Online.

I have some test data files in a SharePoint location and I have a pipeline in Data factory containing a Web Activity and a Copy data activity. The copy activity then links to a Data Lake as its sink.

My desired outcome is for the Copy Data activity to be granted access to the SharePoint Online API and then proceed to move the retrieved data into the data lake. As is stands it has the bearer token but cant access the data through the SharePoint Online API.

I have followed the Microsoft guide here, including the prerequisites.

I am getting the following error in Data Factory: Error code 2200 "Failure happened on 'Sink' side. ErrorCode=UserErrorInvalidCredentialToReadHttpFile,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The credential to read http file is invalid.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Net.WebException,Message=The remote server returned an error: (401) Unauthorized.,Source=System,

The error is present on the Copy data activity (Which uses a HTTP linked service), not the web activity. Therefore I am successfully getting a bearer token which is passed to the Copy data activity, only to be denied.

Troubleshooting:

  • I have assigned Owner role access to the data factory for the data lake.

  • I have registered the service principal App as per the prerequisites in the Microsoft guide above, successfully using the Application ID, Key and Tenant Id to access the bearer token

  • I have granted SharePoint online fullControl permission to the registered service app, successfully, as I can see it listed in the trusted apps in SharePoint. I did so not only here: https://my_company.sharepoint.com/_layouts/15/appinv.aspx but here https://my_company.sharepoint.com/sites/folder/_layouts/15/appinv.aspx

  • I have granted full API permissions to SharePoint and Microsoft Graph in the registered Service app.

  • I have replicated the process in Postman. Only to have the same results - I retrieve a bearer token and then get 401 error when trying calling a GET request for the files, using the bearer token. The error in Postman is: {"error_description":"Exception of type 'Microsoft.IdentityModel.Tokens.AudienceUriValidationFailedException' was thrown."}

  • I have tried this article using AAD service principal with cert and could access metadata, including file names in my chosen SharePoint directory but not the files themselves. The same was true when using SPO linked activity, I assume because neither of these use the generated bearer token.

Most relevant posts I've used:

https://stackoverflow.com/questions/39512605/how-to-move-sharepoint-list-or-excel-file-to-azure-sql-dw

https://docs.microsoft.com/en-us/answers/questions/109275/copy-file-from-sharepoint-not-working.html

https://stackoverflow.com/questions/54868998/azure-data-factory-and-sharepoint

Calling Sharepoint Online API using Azure AD Registered App

0

It could be that the SharePoint app-only permissions is disabled for you tenant.

Please try to run the below cmdlet to enable it: Set-SPOTenant -DisableCustomAppAuthentication $false

1
  • Thanks for the suggestion, I have tried this and the command passed without error after I connected to my SharePoint online from PowerShell, but the error in data factory persists. Is there anything else I can try/look into? Thanks a lot – Aless Apr 6 at 9:13
0

So I found the solution to my question. Using an Azure Logic app to move the files from SharePoint Online to Azure storage.

Reference found here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.