Forum Discussion
Connect to Excel file in SharePoint library using Service Principal (SPN)
All
I have been trying to connect to an excel file hosted in a Sharepoint library to my Power BI Report using Service Principal rather than individual's account/credential. This is to replace the individual accounts/credentials for dataset ownership and to run dataset refreshes using the Service principal.
I have registered an azure app and set up API permissions to the Sharepoint site and Power BI service.
Under API Permissions , chosen Graph API Permissions - Sites.Selected and Files.Read.All for Sharepoint and Tenant.ReadWrite.All and Workspace.ReadWrite.All and Dataset.ReadWrite.All under Power BI.
Also, enabled usage of Service principal in the Power BI Admin center.
The service principal has been given admin access to the workspace where the Power BI report is published .
The app registration and permissions are done in the Sharepoint site where the data resides.
When attempting to replace dataset credentials in Power BI service , by choosing the 'Service Principal' option, and if I provide the Tenant ID, App Id and Client Secret, it accepts the credentials if the data is coming from a Sharepoint list. However, if the dataset is an Excel file hosted in a Sharepoint document library, it throws an error that says, " Failed to update data source credentials: The credentials provided for the Web source are invalid..... Status code: 400".
Interestingly, I can takeover dataset ownership with Power Shell and run data refresh successfully using Power Shell. Why am I not able to edit dataset credentials from the Power BI service's Data set settings menu when the data set is in a Sharepoint library?
This is a puzzle now, as the list and the library are located within the same Sharepoint site.
What am I missing here? Any pointers would be appreciated.
Thanks
Short answer: service principals don’t work with Web data sources in the Service. Your Excel is being accessed with the Web connector, so the “Service principal” option fails.
What to do
In Power BI Desktop, change the query to use SharePoint Folder (v2) (or “SharePoint”/“SharePoint Online”) – connect with the site URL only, then filter to the file (Name/FolderPath).
Publish. In the Service → Dataset settings → Data source credentials, pick OAuth2 → Service principal and enter Tenant ID / App ID / Secret.
Permissions: use Sites.Selected and grant the app access to that site (Graph or PnP):
Grant-PnPAzureADAppSitePermission -AppId <appId> -Site <siteUrl> -Permissions Read
(Files.Read.All alone won’t grant site access when using Sites.Selected.)If you must keep the Web connector, use a gateway or user OAuth—SPN is not supported there.
Also clear any old credentials in Desktop/Service before switching.
Hi sb991986,
Thank you for reaching out to the Microsoft Fabric Community Forum and providing all the details. Also, thanks to VahidDM, for his inputs on this thread. It makes the picture very clear. I can see why this is puzzling, since your service principal works with SharePoint lists but not with Excel files in a document library.
This behaviour is expected. Here is why: The SharePoint List connector in Power BI supports service principal authentication. However, when connecting to an Excel file in a SharePoint library, Power BI uses the Web/SharePoint Folder connectors, which currently have limited support for service principal (app-only) authentication. That is why you see the 400 invalid credentials error when trying to update credentials in the Power BI Service.Grant site/document library access: If you are using Sites.Selected, the service principal needs to be explicitly granted access to the site/document library so it can read the actual file content. https://learn.microsoft.com/en-in/graph/api/site-post-permissions?view=graph-rest-1.0&tabs=http
Use Graph API instead of the Web connector: Instead of connecting directly to the Excel file via Web, use Microsoft Graph API in Power Query (Web.Contents) to fetch the file content. This pattern is fully supported with service principals.
1. https://learn.microsoft.com/en-us/graph/use-the-api
2. https://learn.microsoft.com/en-us/graph/Your setup is correct, but the connector limitation is the reason lists work while Excel files in libraries don’t. The supported path is to grant the service principal content access and switch to a Graph API-based connection for the Excel file.
Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
5 Replies
Short answer: service principals don’t work with Web data sources in the Service. Your Excel is being accessed with the Web connector, so the “Service principal” option fails.
What to do
In Power BI Desktop, change the query to use SharePoint Folder (v2) (or “SharePoint”/“SharePoint Online”) – connect with the site URL only, then filter to the file (Name/FolderPath).
Publish. In the Service → Dataset settings → Data source credentials, pick OAuth2 → Service principal and enter Tenant ID / App ID / Secret.
Permissions: use Sites.Selected and grant the app access to that site (Graph or PnP):
Grant-PnPAzureADAppSitePermission -AppId <appId> -Site <siteUrl> -Permissions Read
(Files.Read.All alone won’t grant site access when using Sites.Selected.)If you must keep the Web connector, use a gateway or user OAuth—SPN is not supported there.
Also clear any old credentials in Desktop/Service before switching.
Hi sb991986,
Thank you for reaching out to the Microsoft Fabric Community Forum and providing all the details. Also, thanks to VahidDM, for his inputs on this thread. It makes the picture very clear. I can see why this is puzzling, since your service principal works with SharePoint lists but not with Excel files in a document library.
This behaviour is expected. Here is why: The SharePoint List connector in Power BI supports service principal authentication. However, when connecting to an Excel file in a SharePoint library, Power BI uses the Web/SharePoint Folder connectors, which currently have limited support for service principal (app-only) authentication. That is why you see the 400 invalid credentials error when trying to update credentials in the Power BI Service.Grant site/document library access: If you are using Sites.Selected, the service principal needs to be explicitly granted access to the site/document library so it can read the actual file content. https://learn.microsoft.com/en-in/graph/api/site-post-permissions?view=graph-rest-1.0&tabs=http
Use Graph API instead of the Web connector: Instead of connecting directly to the Excel file via Web, use Microsoft Graph API in Power Query (Web.Contents) to fetch the file content. This pattern is fully supported with service principals.
1. https://learn.microsoft.com/en-us/graph/use-the-api
2. https://learn.microsoft.com/en-us/graph/Your setup is correct, but the connector limitation is the reason lists work while Excel files in libraries don’t. The supported path is to grant the service principal content access and switch to a Graph API-based connection for the Excel file.
Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
Hi sb991986,
Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.Thank you.
Hi sb991986,
Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.Thank you.