Service Principal Unable to refresh Sharepoint Cloud Connection

[JohnAPD]

We have setup a service principal to enable the removal of personal user credentials from datasets due to Mandatory MFA being put in place. Still, we get an error for the SharePoint data source "The credentials provided for the SharePoint source are invalid. (Source at https://xxxxx.sharepoint.com/sites/xxxxxx.). The exception was raised by the IDbCommand interface."

We have two data sources SQL and Sharepoint

we have created cloud connection data gateways for these so we can select the required connections when publishing the dataset

Using the service principal auth method.

The connections are tested ok at this stage

The workspace has the service principal assigned to it with Admin rights

We take over the dataset as the service principal with REST API, and the SQL connection works ok but not Sharepoint (we tested these separately, we were told this was a prerequisite to allow the refresh to happen via the SP

We have gone around a few circles with MS support and this was their latest advice:

We created the service principal profile but this had no effect

Any advice anyone can give would be appreciated

[JohnAPD]

Hi @aj1973 appreciate the help but sorry to say that Copilot shouldn't be trusted for this kind of information as no option to sign in exists as described and further research elsewhere proves its not possible:

[aj1973]

In your situation it might be better to create a Service account and use it to authenticate against SQL Server and SharePoint. 

[JohnAPD]

Additional info:

Service principal API permissions are configured as:

The SP is part of a group within Sharepoint aswell

[aj1973]

Hi @JohnAPD 

Do the SP have a Power BI Pro license assigned to it?

[JohnAPD]

Hi @aj1973 I don't think its possible to assign a license to the Service Principal (app registration) as you cannot "login" with it and as per this:

https://learn.microsoft.com/en-us/power-bi/developer/embedded/embed-sample-for-customers?tabs=net-co...

[aj1973]

Hi @JohnAPD 

Thanks to Copilot