Recieving (403) Forbidden when Accessing Dataset via API

[MikeFleming]

I am getting an error when accessing dataset via API.

This was previously working but I suspect someone made some changes to that dataset.  However, I know that both the service principle and the impersonating user have admin on that dataset.  I have also tried to create another service principle, following the guidance in

Embed Power BI content in an embedded analytics application with service principal and an applicatio...

And I get the same result.

I have confirmed the setting 'Allow service principals to use Power BI APIs' remains enabled.

I am able to access the dataset OK with the impersonating user.

I have tried granting the service principal API access to datasets in Azure AD (even though I dont think that is required)

$clientId = "0bXXXXc6"
$clientSecret = "GSXXXXaE"

# Function to get the access token
function Get-AccessToken {
    param (
        $tenantId,
        $clientId,
        $clientSecret
    )

    $tokenEndpoint = "https://login.microsoftonline.com/$tenantId/oauth2/token"
    $body = @{
        "grant_type"    = "client_credentials"
        "client_id"     = $clientId
        "client_secret" = $clientSecret
        "resource"      = "https://analysis.windows.net/powerbi/api"
    }

    $response = Invoke-RestMethod -Uri $tokenEndpoint -Method Post -Body $body -ContentType "application/x-www-form-urlencoded"
    return $response.access_token
}

# Get access token
$accessToken = Get-AccessToken -tenantId $tenantId -clientId $clientId -clientSecret $clientSecret

$headers = @{
    "Authorization" = "Bearer $accessToken"
}


$url = "https://api.powerbi.com/v1.0/myorg/datasets/403358d8-a148-4d52-a33f-4d39630ddb78/executeQueries"
$body = @"
{
    "queries": [
        {
           "query": "EVALUATE SUMMARIZECOLUMNS('Geography'[Store Number], 'Geography'[Store Opening Hours 1.Monday], 'Geography'[Store Opening Hours 2.Tuesday], 'Geography'[Store Opening Hours 3.Wednesday], 'Geography'[Store Opening Hours 4.Thursday], 'Geography'[Store Opening Hours 5.Friday], 'Geography'[Store Opening Hours 6.Saturday], 'Geography'[Store Opening Hours 7.Sunday], 'Geography'[Store Opening Hours 8.Special])"
        }
    ],
    "serializerSettings": {
        "includeNulls": true
    },
    "impersonatedUserName": "example@example.com"
}
"@

$result = Invoke-WebRequest -Uri $url -Method Post -Body $body -Headers $headers -ContentType "application/json" -UseBasicParsing -Verbose -Debug

Invoke-WebRequest : The remote server returned an error: (403) Forbidden.
At line:59 char:11
+ $result = Invoke-WebRequest -Uri $url -Method Post -Body $body -Heade ...
+           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand

[MikeFleming]

Invoke-WebRequest : The remote server returned an error: (401) Unauthorized.
At line:1 char:2
+  Invoke-WebRequest -Uri $url -Method Post -Body $body -Headers $heade ...
+  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand

[ibarrau]

Alright 401 might be different. Make sure the permissions of the registered app at azure have "Dataset.ReadWrite.All" or "Dataset.Read.All".

If you can see the Admin portal or ask for IT department. Please check this setting:

I hope that helps,

[MikeFleming]

Hi, yes,

[ibarrau]

This might sound weird but please remove the Grant admin consent to the permissions. That's a permission for tenant only that can produce error for regular api requests.

Also add Dataset.ReadWrite.All to keep both permissions for dataset.

Just in case, let's sync something. You are using executeQueries. The request is asking for DAX Query in "My Workspace". If you want to query a dataset in a workspace you must use executeQueries in Group.

Please test "Get Datasets in Group" and let us know if that one works.

Make sure you are not breaking a limitation: https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries-in-group#limitations

Regards.

[MikeFleming]

Hi,

I removed the consent, but same

I wasnt aware that was asking for queries in My Workspace.  This has worked before with the same code.  But I will check that out, and also try the other call you mentioned.  I will also revisit the limitations.

Thanks

[MikeFleming]

It is a limitation!  RLS has been enabled.

"Service Principals aren't supported for datasets with RLS per RLS limitations  or with SSO enabled."

I will need to find another way to connect.

Thanks for pointing that out.

[ibarrau]

Alright! it's good to know you find it. Remember you can use the API with username and password instead of secret. I think that might work for RLS.

I hope that helps,

[MikeFleming]

OK, I thought I have tried doing that in the past, but the account I was using would have needed a license.  This is a Premium capacity though.  

Thanks again

[MikeFleming]

Correction to original post, I am getting 401 Unauthorized

[ibarrau]

Hi. Usually the 403 for Power Bi Rest API means the token hasn't priviledges for the operation because of a missing permissiong. 403 is the forbidden action. Please check that the Registered App (Service principal) is member of the workspace where the dataset is hosted. Otherwise, the Service Principal can't read the dataset.

In addition, remember that the App registered should have Dataset.ReadWrite.All or Dataset.Read.All permissions at its permission properties in Azure.

I hope that helps,

[MikeFleming]

Thanks.  The service principal is Admin on that workspace.  As is the impersonating user.