topic Re: AccessUnauthorized on Mashup engine using Gateways - Update Datasource API in Developer

AccessUnauthorized on Mashup engine using Gateways - Update Datasource API

Anonymous — Wed, 27 Oct 2021 11:36:02 GMT

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.

Re: AccessUnauthorized on Mashup engine using Gateways - Update Datasource API

V-lianl-msft — Mon, 01 Nov 2021 08:09:48 GMT

Hi @Anonymous ,

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.

Re: AccessUnauthorized on Mashup engine using Gateways - Update Datasource API

Anonymous — Wed, 03 Nov 2021 09:52:11 GMT

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?

https://docs.microsoft.com/en-us/rest/api/power-bi/gateways/update-datasource#oauth2-credentials-example

Re: AccessUnauthorized on Mashup engine using Gateways - Update Datasource API

Anonymous — Thu, 09 Dec 2021 11:01:41 GMT

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