Azure Synapse Web Activity 'GetFolderByServerRelativeUrl' Unauthorized Access

Question

I'm trying to get a list of files from my sharepoint site in Azure Synapse Pipeline Activity.

I'm using 2 web activities, the first one grabs the access token and the second one grabs the relative url: https://testsite.sharepoint.com/sites/Repository/_api/web/GetFolderByServerRelativeUrl('/sites/Repository/Shared%20Documents/General')/Files

The second web activity is failing with this error: { "errorCode": "2108", "message": "{"odata.error":{"code":"-2147024891, System.UnauthorizedAccessException","message":{"lang":"en-US","value":"Attempted to perform an unauthorized operation."}}}", "failureType": "UserError", "target": "Web2", "details": [] }

Also, if anyone knows any other way to grab a list of files inside a sharepoint folder in Azure Synapse, please let me know. I tried using a Sharepoint Online List, but it only shows me the Document Libraries and the Lists, it won't go into subfolders inside of the Document Library.

Answer 1

The first web activity successfully grabbed the access token, and the relative url in the second web activity was correct, the error was caused by my header which grabbed the access token "Authorization" : "Bearer", there is no space after the "Bearer", when there should be a space, such as "Authorization" : "Bearer "

Thanks for the help. I was able to successfully grab all the files from a subfolder in sharepoint and copy them into a data lake storage.

Answer 2

You can try using the Graph API to list files and folders in the SharePoint site. It's no different from the SharePoint REST API workflow, first you need to grant Graph API permissions to your application, then use the delegated authentication flow (such as auth code flow) to get a token, and finally use the token to call the Graph API endpoint.

1.List all files/folders under the root directory:

https://graph.microsoft.com/v1.0/sites/{site id}/drive/root/children

2.list files/subfolders under a specific folder:

https://graph.microsoft.com/v1.0/sites/{site id}/drive/root:/{folder name}:/children