Forum Discussion
Dataset credential is missing while publishing using Rest Api for report based on SharePoint Excel
This reads like buzzword bingo - "Excel and Sharepoint and Service Principal". I think you picked a rather very complex scenario here. Please clarify if this is SPO, and if a gateway is involved.
Why would they restrict manual access to the workspaces for workspace administrators or developers?
We have a portal. We are showing Power BI report through that portal (Embedding Power BI report).
As per the requirement, user is developing Power BI reports and publishing the reports through that portal. We are using Power BI rest api for publishing the report. Currently all the reports based on Azure SQL DB. Means user's are connecting Azure SQL DB, designing the report and publishing the same using our portal. So, far it is working properly.
There is no manual intervention. So, access to the Power BI service workspace is not possible.
Recently, we get a new requirement where user will design report based on excel file from SharePoint. During development we stuck of with above problem.
I don't know whether gateway is involved or not.
HI nilanjanpalMol,
Can you please share some more details about your report settings? (e.g. data connector type, query table operation steps, data source credentials that you configured) They should help us clarify your scenarios.
How to Get Your Question Answered Quickly
In addition, you can also take a look at the following link to work with SharePoint files with a web connector, they can be refreshed with 'onedrive refresh' and not required to manage with data gateway.
Use OneDrive for Business links in Power BI Desktop - Power BI | Microsoft Docs
Regards,
Xiaoxin Sheng
Thank you very much for your reply.
I have created a sample report based on a sample excel data. Excel file is in Microsoft 365 SharePoint. I have followed the step which you mentioned in 2nd link. Data source type is "Web". File path I had taken as it was mentioned in that link.
My SharePoint Credential and Power BI Service credential are different.
While publishing the report under lining data source is losing credential. Of course, I am able to add credential manually from Power BI service. I have checked that if it is set manually refresh operation is working properly. But I want to retain the old credential as it happened for the report based on database data.
Also, It will be ok if I can set OAuth2 credential using Power BI Rest Api. I have tried already using below code:var datasource = await client.Datasets.GetGatewayDatasourcesInGroupAsync(Guid.Parse(workspaceid), report.DatasetId);
var delta = new UpdateDatasourceRequest
{
CredentialDetails = new CredentialDetails
{
CredentialType = "OAuth2",
Credentials= "{\"credentialData\":[{\"name\":\"accessToken\", \"value\":\"" + t.AccessToken + "\"}]}",
EncryptedConnection= "Encrypted",
EncryptionAlgorithm= "None",
PrivacyLevel= "None",
UseEndUserOAuth2Credentials = true
}
};
await client.Gateways.UpdateDatasourceAsync(datasource.Value[0].GatewayId, datasource.Value[0].Id, delta);I am finding below error:
"{\"error\":{\"code\":\"InvalidRequest\",\"message\":\"Property useEndUserOAuth2Credentials is only supported with credentials of type OAuth2 and datasource type of Web\"}}"
I have checked datasource type is showing as "Web" and Credential type is "OAuth2"
HI nilanjanpalMol,
>>My SharePoint Credential and Power BI Service credential are different.For power bi service report dataset settings, it required you use the same credentials used in the power bi desktop.
If you are confused about using rest API with OAuth authorization, you can refer to the following link:
Solved: Pull data from a REST API Authentication - Microsoft Power BI Community
If you are not sure about the data source connections in the rest API, you can try to use rest API 'get datasource' to get the result from a similar type of data source as a template to modify and use in your request.
Datasets - Get Datasources - REST API (Power BI Power BI REST APIs) | Microsoft Docs
Datasets - Get Datasources In Group - REST API (Power BI Power BI REST APIs) | Microsoft Docs
Notice: web type data source normally used 'anonymous' authorizations, the data source credentials and verifications operations will be attached to the query table steps.
Regards,Xiaoxin Sheng