Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm using the Gateways - Update Datasource API to programmatically change the credentials of a dataset that connects to a PBI dataflow.
I use the following call:
PATCH https://api.powerbi.com/v1.0/myorg/gateways/fb6ff.../datasources/f580...
I'm using the following payload:
{
"credentialDetails": {
"credentialType": "OAuth2",
"credentials": "{\"credentialData\":[{\"name\":\"accessToken\", \"value\":\"eyJ0...\"}]}",
"encryptedConnection": "Encrypted",
"encryptionAlgorithm": "None",
"privacyLevel": "None"
}
}
Here my discoveries:
1. The API goes in error if in the Bearer I use a SP token. I must use a user token obtained via OAUTH2 password grant if I want the API to succeed.
2. I'm unable to let the API succeed if I use the user password grant token in the payload or the SP token. The unique way to make it functioning is using a token obtained via interactive authentication.
Any clue how to make it works in the above scenario?
Here the results I obtain:
API call using a SP in the Authorization header (regardless of what token I use in the payload):
{
"error": {
"code": "PowerBINotAuthorizedException",
"pbi.error": {
"code": "PowerBINotAuthorizedException",
"parameters": {},
"details": [],
"exceptionCulprit": 1
}
}
}
API call using a password grant token in the Authorization Bearer and in the payoad:
{
"error": {
"code": "DM_GWPipeline_Gateway_InvalidConnectionCredentials",
"pbi.error": {
"code": "DM_GWPipeline_Gateway_InvalidConnectionCredentials",
"parameters": {},
"details": [
{
"code": "DM_ErrorDetailNameCode_UnderlyingErrorCode",
"detail": {
"type": 1,
"value": "-2147467259"
}
},
{
"code": "DM_ErrorDetailNameCode_UnderlyingErrorMessage",
"detail": {
"type": 1,
"value": "The credentials provided for the PowerBI source are invalid. (Source at PowerBI.)"
}
},
{
"code": "DM_ErrorDetailNameCode_UnderlyingHResult",
"detail": {
"type": 1,
"value": "-2147467259"
}
},
{
"code": "Microsoft.Data.Mashup.CredentialError.DataSourceKind",
"detail": {
"type": 1,
"value": "PowerBI"
}
},
{
"code": "Microsoft.Data.Mashup.CredentialError.DataSourceOriginKind",
"detail": {
"type": 1,
"value": "Web"
}
},
{
"code": "Microsoft.Data.Mashup.CredentialError.DataSourceOriginPath",
"detail": {
"type": 1,
"value": "https://wabi-north-europe-k-primary-redirect.analysis.windows.net/metadata/v201606/cdsa/workspaces"
}
},
{
"code": "Microsoft.Data.Mashup.CredentialError.DataSourcePath",
"detail": {
"type": 1,
"value": "PowerBI"
}
},
{
"code": "Microsoft.Data.Mashup.CredentialError.Reason",
"detail": {
"type": 1,
"value": "AccessUnauthorized"
}
},
{
"code": "Microsoft.Data.Mashup.MashupSecurityException.DataSources",
"detail": {
"type": 1,
"value": "[{\"kind\":\"PowerBI\",\"path\":\"PowerBI\"}]"
}
},
{
"code": "Microsoft.Data.Mashup.MashupSecurityException.Reason",
"detail": {
"type": 1,
"value": "AccessUnauthorized"
}
}
],
"exceptionCulprit": 1
}
}
}
API call using a password grant token in the Authorization Bearer and an interactive user token in the payload (same user as of the password grant token but obtained interactively) :
200 OK
Any clue? Obviously I need to obtain the token programmatically I cannot obtain it interactively since I'm working an an CI/CD powershell script.
Solved! Go to Solution.
Finally got an answer from the PBI product group.
The correct payload to use (totally undocumented) is:
{
"credentialDetails": {
"credentialType": "OAuth2",
"encryptedConnection": "Encrypted",
"encryptionAlgorithm": "None",
"privacyLevel": "Organizational",
"useCallerAADIdentity": true
}
}
I call it using a password grant token in the Authorization Bearer.
This undocumented payload does not require a token in the payload thanks to the property:
"useCallerAADIdentity": true
Finally got an answer from the PBI product group.
The correct payload to use (totally undocumented) is:
{
"credentialDetails": {
"credentialType": "OAuth2",
"encryptedConnection": "Encrypted",
"encryptionAlgorithm": "None",
"privacyLevel": "Organizational",
"useCallerAADIdentity": true
}
}
I call it using a password grant token in the Authorization Bearer.
This undocumented payload does not require a token in the payload thanks to the property:
"useCallerAADIdentity": true
Hi @giost ,
You could try OAuth 2.0 resource owner password credentials grant
// Line breaks and spaces are for legibility only. This is a public client, so no secret is required.
POST {tenant}/oauth2/v2.0/token
Host: login.microsoftonline.com
Content-Type: application/x-www-form-urlencoded
client_id=6731de76-14a6-49ae-97bc-6eba6914391e
&scope=user.read%20openid%20profile%20offline_access
&username=MyUsername@myTenant.com
&password=SuperS3cret
&grant_type=password
Make sure your Application's Grant Types include Password.
You can also refer to:
https://stackoverflow.com/questions/63321532/sharepoint-rest-api-how-to-get-access-token
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @V-lianl-msft ,
thank you for your reply but your suggessted method definitively doesn't work for many reasons:
1. The appid has not the correct scope
2. The application is not consented by the user (this one you can fix but the 1 not
Here the issue is another:
the API described here https://docs.microsoft.com/en-us/rest/api/power-bi/gateways/update-datasource
for the OAUTH2 credentials (the unique valid for datasource of type dataflow) requires a token in the payload and a token in the authorization header.
The token in the authorization header I think I provided it correctly in fact the first authorization layer of the API succeed and I can call also other APIs with the same token.
The issue is in the token in the payload, I'm not able to make it succeed with any token I provide except a token generated using interactive login (i.e. copy/paste the token from the power bi portal with the user logged in), in all the other cases (token generated programmatically using password grant) I always end up with an internal error coming from the Mash-up engine (this is an additional proof that the API authorization pass correctly but is the backend that does not accept the token I pass in the payload).
I also compared with jwt.io the 2 tokens (the one that works coming from the portal and the one generated programmatically and the unique differnce i found is the scope. In the portal is user_impersonation, programmatically is Dataset.ReadWrite.All and all the other API permissions). The problem is that I will never be able to generate programmatically a token with scope user_impersonation or I do not know how to make it.
In conclusion: how to generate a token that is valid in the PAYLOAD of this API?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
1 | |
1 | |
1 | |
1 |
User | Count |
---|---|
25 | |
3 | |
2 | |
2 | |
2 |