AccessUnauthorized on Mashup engine using Gateways - Update Datasource API

[giost]

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.

[giost · Accepted 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

[V-lianl-msft]

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.

[giost]

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-exa...