Service principal issuing dodelete generates 403

[Anonymous]

Hello

I m a bit new to PowerBI,

I m trying to issue API Rest CALL to push dataset to PowerBI

Base on the regular sample application I've been able to achieve create/push/delete of dataSets using a token generated with a user / pwd credential, provided that the application is a public client. 

When trying to move to service principal (client secret) based authentication, all api call that are pushing/deleting data fails with a 403 error, whereas all listing calls (groups, datasets) are still working.

I suspect that it is an authorized API issue, but I can't see what's missing, since I've all PowerBI authorization delegated + Tenant.Read.All & Tenant.ReadWrite.All at application level, and nothing else is proposed in the Authorized API panel of the hosting application :

Here is the code (roughly, without the utilities)

            String groupName = "XXXXX";
            String dataSetName = "SalesMarketing";
            String groupId = null;
            boolean site = true;
            String token;
            /* */
            if(site){
                token=aut.getAccessTokenFromSecret(AUTHORITY, DIRECTORY_ID, APP_ID, CLIENT_SECRET);
            }else{
                token=aut.getAccessTokenFromUserPassword(AUTHORITY, DIRECTORY_ID, APP_ID, USER_ID, USER_PWD);
            }
            System.out.println("Access Token is " + token);
            Map reply = null;
            client = new BIClient(token);           
            client.initialize(); // Set up client
            // Get groups
            String[] groupsApi = {"groups"};
            reply = client.doGet(groupsApi, null);
            if(reply != null){
                groupId = this.getElement(reply, groupName, "id");
            }
            if(groupId == null){
                // Create it
                String createGoup = String.format("{ \"name\": \"%s\"}", groupName);
                reply = client.doPost("groups", createGoup);
                if(reply != null && reply instanceof Map && StringUtils.isNotEmpty((String)reply.get("id"))){
                    groupId = (String )reply.get("id");
                }
            }
            if(groupId == null){
                throw new Exception("Unable to create group " + groupName);
            }
            // Now try to get our dataSet
            HashMap<String, String> params = new HashMap<>();
            // Table
            String tableJson = "{\"name\" : \"Product\", \"columns\" : ["
                        + "{ \"name\": \"ProductID\", \"dataType\": \"Int64\"}, "
                        + "{ \"name\": \"Name\", \"dataType\": \"string\"}, "
                        + "{ \"name\": \"Category\", \"dataType\": \"string\"},"
                        + "{ \"name\": \"IsCompete\", \"dataType\": \"bool\"},"
                        + "{ \"name\": \"UpdatedOn\", \"dataType\": \"DateTime\"},"
                        + "{ \"name\": \"CreatedOn\", \"dataType\": \"DateTime\"}" + "]}";
            // DataSet
            String[] apis = { "groups", groupId, "datasets" };
            reply = client.doGet(apis, params);
            String datasetId = null;
            if (reply != null) {
                datasetId = this.getElement(reply, dataSetName, "id");
            }
            if (datasetId == null) {
                // Check if dataSet is here or not
                String datasetJson = "{\"name\": \"SalesMarketing\" , \"tables\" : ["+ tableJson + "]}";
                reply = client.doPost(String.format("%s/%s/%s","groups", groupId,"datasets"), datasetJson);
                if (reply != null && dataSetName.equals((String) reply.get("name"))) {
                    datasetId = (String) reply.get("id");
                }
            }
            if (datasetId == null) {
                throw new Exception("DataSet SalesMarketing not found");
            }

            // Now find table
            // Check about table
            String[] tablesGet = { "groups", groupId, "datasets", datasetId, "tables" };
            Map tables = client.doGet(tablesGet, null);
            List<Map> values;
            boolean found = false;
            if (tables != null && (values = (List<Map>) tables.get("value")) != null) {
                for (Map element : values) {
                    if ("Product".equalsIgnoreCase(element.get("name").toString())) {
                        found = true;
                        break;
                    }
                }
            }
            System.out.println(String.format("Table %s has %s been found in dataset", "Product", (found ? "" : "NOT")));
            if (!found) {
                
                // Update Table definition
                String[] tablePut = { "groups", groupId, "datasets", datasetId, "tables", "Product" };
                reply = client.doPut(StringUtils.join(tablePut, "/"), tableJson);
                System.out.println("put done");
            }
            String sUri = String.format("datasets/%s/tables/%s/rows", datasetId, "Product");
            // First clean ALL Previous rows
            reply = client.doDelete(sUri, null);
            // Then add

And the result is  :

Access Token is eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1..........QWv6GoiMjpJntM6_93rhhWNRg
doGet groups
Get using https://api.powerbi.com/v1.0/myOrg/groups
-----
Status is 200
+++++
{
  "@odata.context":"http://api.powerbi.com/v1.0/myOrg/$metadata#groups","@odata.count":2,"value":[
    {
      "id":"dea316d8-113a-4a03-b3e4-044785093c61","isReadOnly":false,"isOnDedicatedCapacity":false,"name":"XXXXXXXX"
    },{
      "id":"bbe6ff51-aebc-45cf-8f5c-6a94ecbbf733","isReadOnly":false,"isOnDedicatedCapacity":false,"name":"YYYYYYYY"
    }
  ]
}
-----
doGet groups/dea316d8-113a-4a03-b3e4-044785093c61/datasets
Get using https://api.powerbi.com/v1.0/myOrg/groups/dea316d8-113a-4a03-b3e4-044785093c61/datasets
-----
Status is 200
+++++
{
  "@odata.context":"http://api.powerbi.com/v1.0/myOrg/groups/dea316d8-113a-4a03-b3e4-044785093c61/$metadata#datasets","value":[
    {
      "id":"2b2d41bf-3ccb-4189-b676-a68f9ef74cfb","name":"SalesMarketing","addRowsAPIEnabled":true,"configuredBy":"1412c171-0a25-4aa2-935f-5b54b79fa8c0","isRefreshable":true,"isEffectiveIdentityRequired":false,"isEffectiveIdentityRolesRequired":false,"isOnPremGatewayRequired":false,"targetStorageMode":"Abf","createReportEmbedURL":"https://app.powerbi.com/reportEmbed?config=eyJjbHVzdGVyVXJsIjoiaHR0cHM6Ly9XQUJJLUZSQU5DRS1DRU5UUkFMLUEtUFJJTUFSWS1yZWRpcmVjdC5hbmFseXNpcy53aW5kb3dzL3d","qnaEmbedURL":"https://app.powerbi.com/qnaEmbed?configZWRpcmVldCIsImVtYmVkRmVhdHVyZXMiOnsibW9kZXJuRW1iZWQiOnRydWV9fQ%3d%3d"
    }
  ]
}
-----
doGet groups/dea316d8-113a-4a03-b3e4-044785093c61/datasets/2b2d41bf-3ccb-4189-b676-a68f9ef74cfb/tables
Get using https://api.powerbi.com/v1.0/myOrg/groups/dea316d8-113a-4a03-b3e4-044785093c61/datasets/2b2d41bf-3ccb-4189-b676-a68f9ef74cfb/tables
-----
Status is 200
+++++
{
  "@odata.context":"http://api.powerbi.com/v1.0/myOrg/groups/dea316d8-113a-4a03-b3e4-044785093c61/$metadata#tables","value":[
    {
      "name":"Product","source":[

      ]
    }
  ]
}
-----
Table Product has  been found in dataset
dodelete https://api.powerbi.com/v1.0/myOrg/datasets/2b2d41bf-3ccb-4189-b676-a68f9ef74cfb/tables/Product/rows
-----
Status is 403
+++++
{"Message":"API is not accessible for application"}
-----
dopost https://api.powerbi.com/v1.0/myOrg/datasets/2b2d41bf-3ccb-4189-b676-a68f9ef74cfb/tables/Product/rows
-----
Status is 403
+++++
{"Message":"API is not accessible for application"}
-----

Any help would be very much appreciated !!!!!

[v-shex-msft]

HI @Anonymous,

AFAIK, power bi does not allow you to use 'service principal' authorization with REST API. Have you enabled service principal support for REST API on power bi service side before the test?

Use Power BI API with service principal (Preview) 
Regards,

Xiaoxin Sheng

[Anonymous]

Hello, Sheng,

First of all thank you for your reply.

Indeed I did it :

At least, can you confirm that what I m doing is "expected" to work ?

My point is that I could stay with user / pwd credential for my application authorization, but I m afraid that at some point, there would be some 2FA actions (mail, sms ...) enforced by Azure that we can't cope with the application which is server side with no human interaction.

[v-shex-msft]

HI @Anonymous,

According to your description, it seems like you are worried about the abuse of 'service principal' verifications, right?

If that is the case, I think you can take a look at the limitations that the official document mentions.

#1, This credential only works for some REST APIs, it not globally available to all power bi service. (normally power bi service and datasets does not work for 'service principal' credential)

#2, This credentials usage ranges based on configure in tenant settings and permissions. You can configure the minimum version of permission and limit the usage to some special group members.

#3, As I said above, its usage has been limited to the specified API and contents. its permissions not able to affect or usage on other MFA actions.

Reference links:

Embed Power BI content with service principal and an application secret#considerations-and-limitations 

Enable service principal authentication for read-only admin APIs (preview) - Power BI | Microsoft Docs

BTW, if you still need some additional permission or management of this feature usage, you can consider submitting ideas to help improve this feature.

Power BI Ideas 

Regards,

Xiaoxin Sheng