Forum Discussion
Connect to Excel file in SharePoint library using Service Principal
Hi Anjan
Thank you for sharing this information. Based on your response, I did a test with the Sharepoint Folder connector and connected using the site url, then filtered down to the excel sheet that was the data source for the report. Then extracted the Binary content and used that in the report. Power BI added additional function/query tables in the data source Queries space.
Following this I published the report into a workspace. Using Semantic model settings , edited the data source credentials to that of the Service Principal. The credentials were accepted without any error. However, once I proceeded with a manual refresh of the semantic model , I received the following error for the Data refresh again!
"There was an error when processing the data in the dataset. Please try again later or contact support. If you contact support, please provide these details.
- Data source errorThe credentials provided for the SharePoint source are invalid. (Source at (sharepoint site url listed here). The exception was raised by the IDbCommand interface. Table: Query1......."
Here , Query1 is the table generated by Power BI once I filtered down to the binary content and used in the report.
What am I doind wrong here?
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.