Forum Discussion
Connect to Excel file in SharePoint library using Service Principal
Hi sujaza,
The error here during refresh is due to a runtime access issue with the binary content of the Excel file in SharePoint.When using the SharePoint Folder connector, power bi first accesses the document library and then reads the binary content of the excel file. This binary access step requires explicit permissions to read file contents, not just metadata.
While Sites.Selected and Files.Read.All permissions allow to see the metadata but the service principal also needs explicitly read access to the document library or file itself.
- Grant explicit read access to the service principal at the site or document library level using Microsoft Graph API. Assign read role to the app, this is mandatory when using Sites.Selected, without it power bi cannot read the file content.
- And also the root SharePoint site URL should be used in the SharePoint folder connector. Using a full file path may cause power bi to treat it as a Web connector internally, which does not support SPNs.
Thanks and regards,
Anjan Kumar Chippa
Hi v-achippa
Thank you again for the response!
I have since amended the API permissions on the Service principal to have Sites.Read.All and Files.Read.all for Graph API - Application permissions.
Can you please elaborate on "Grant explicit read access to the service principal at the site or document library level using Microsoft Graph API".
I have used root Sharepoint url of the site to access the site in the Folder connector.
The error remains!
Hi sujaza,
- Thank you for the response. Now that you have added Sites.Read.All and Files.Read.All permissions to the Service Principal, in most cases these should generally be sufficient for accessing the SharePoint content.
- When you were using Sites.Selected before, microsoft requires to manually grant access to specific SharePoint sites. Which means explicitly telling SharePoint like allow this app (Service Principal) to read this site.
This can be done by using a Microsoft Graph API POST request:
Endpoint: POST https://graph.microsoft.com/v1.0/sites/{site-id}/permissions
Request body:
{
"roles": [ "read" ],
"grantedToIdentities": [
{
"application": {
"id": "client-id",
"displayName": "app-name"
}
}
]
}
Replace ‘site-id’ with your SharePoint Site ID, ‘client-id’ with your Azure App (Service Principal) Client ID and ‘app-name’ with the display name of your registered app. This will explicitly grants read access to the SharePoint site for the Service Principal.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
I have since created a Microsoft support ticket and as per the MS Support team, authentication using Service principal is not supported for 'Sharepoint Folder Connector' as per the MS documentation below.
Power Query SharePoint folder connector - Power Query | Microsoft Learn
Interestingly, documentation on Web connector shows that this auth type is supported.
Power Query Web connector - Power Query | Microsoft Learn
However , I had no luck with both the connectors with Sharepoint.