This endpoint: https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queriesCan i use the identities to set a role and username?

Hello, this documentation is not clear about Rls identities. Can you send a body example?I tested this and not filter the rls, return all rows.{ "queries": [ { "query": "EVALUATE VALUES(table)" } ], "identities": [ { "username": "xxx@xxx.com", "roles": [ "myrole" ], "datasets": [ "xxx-xxx-xxx-xxxx" ] } ] }

Hi, sorry for the long time to response. None of those solutions worked for me 😞 Into my embedded report, the RLS works fine, the problem is just about the execute queries. My steps:1 - Generate a authentication token by service principal and master user (the both not work)2 - Get the token from the step 1 and call the api generate token:POST:https://api.powerbi.com/v1.0/myorg/GenerateToken { "datasets": [ { "id": "xxx-xx-xxxx" } ], "targetWorkspaces": [ { "id": "xxx-xxx-xxxx-xxx" } ], "identities": [ { "username": "usernamexxx", "roles": ["rolexxx"], "datasets": ["xxxx-xxx-xxxx"] } ] }3 - Get the token from step 2 and execute querie:POST: POST https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/executeQueries BODY example: { "queries": [ { "query": "EVALUATE VALUES('mytable')" } ],

Hi marcio_fornari, Thank you for reaching out to the Microsoft fabric community forum regarding the use of the execute Queries endpoint with Row-Level Security (RLS) in Power BI. Yes, you can use the identities field in the Execute Queries API to apply Row-Level Security (RLS). You can set both the username and roles in your API call to simulate the security context. Make sure the role you're passing matches one defined in the dataset, and that the username aligns with what your DAX security rules expect (USERPRINCIPALNAME() or USERNAME()). You’re following correct documentation, to implement RLS effectively using the identities field, please refer to the official Microsoft documentation, which provides comprehensive guidance on structuring your request: Execute Queries – Power BI REST API Defining RLS Roles: Ensure that RLS roles are properly configured in your Power BI dataset. Constructing the identities Array: Your request body should include the identities array with the appropriate username, roles, and datasets fields. Authentication: Use a valid Azure AD bearer token with the necessary permissions to access the dataset. If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.Thank you for using Microsoft Community Forum.

Hi marcio_fornari, Thank you for the follow-up and for sharing the request body you tested. You are correct in using the identities field in the Execute Queries endpoint to simulate RLS. However, RLS filtering only applies under specific conditions. Here are the steps to ensure it is configured correctly. Requirements for RLS to Work: Premium or Fabric Capacity: The dataset must be in a workspace supported by Premium capacity (P SKU or Embedded A SKU) or Microsoft Fabric capacity. Defined RLS Roles: Ensure that the role name (such as "myrole" in your example) is precisely defined in the Power BI dataset under Manage roles in Power BI Desktop or service. Matching Identity: The username must align with what your DAX RLS expressions evaluate using USERNAME() or USERPRINCIPALNAME(). For instance, if your DAX rule filters based on email, the username must be the user's email. Example Request Body (Correct Format): { "queries": [ { "query": "EVALUATE VALUES('YourTableName')" } ], "identities": [ { "username": "user@yourdomain.com", "roles": [ "SalesRegionRole" ], "datasets": [ "your-dataset-guid" ] } ] } Required Headers: Ensure you are using an Azure AD bearer token with permissions to read from the dataset. The request must include: Authorization: Bearer <your-access-token> Content-Type: application/json If you are still retrieving all rows, it typically indicates one of the following issues: The RLS role name does not match exactly. The dataset is not located in a Premium/Fabric workspace. Your dataset does not utilize USERNAME() or USERPRINCIPALNAME() in the DAX security rule. The token does not impersonate the user (though the identities field should simulate this if Premium capacity is enabled).If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.Thank you for using Microsoft Community Forum.

Hi marcio_fornari, May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster. Thank you.

Datasets - Execute Query endpoint with RLS | Microsoft Fabric Community

12 Replies

v-kpoloju-msft
Community Support
1 year ago
Hi marcio_fornari,

Thank you for reaching out to the Microsoft fabric community forum regarding the use of the execute Queries endpoint with Row-Level Security (RLS) in Power BI.

Yes, you can use the identities field in the Execute Queries API to apply Row-Level Security (RLS). You can set both the username and roles in your API call to simulate the security context. Make sure the role you're passing matches one defined in the dataset, and that the username aligns with what your DAX security rules expect (USERPRINCIPALNAME() or USERNAME()).

You’re following correct documentation, to implement RLS effectively using the identities field, please refer to the official Microsoft documentation, which provides comprehensive guidance on structuring your request: Execute Queries – Power BI REST API

Defining RLS Roles: Ensure that RLS roles are properly configured in your Power BI dataset.

Constructing the identities Array: Your request body should include the identities array with the appropriate username, roles, and datasets fields.

Authentication: Use a valid Azure AD bearer token with the necessary permissions to access the dataset.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.
- marcio_fornari
  Resolver I
  1 year ago
  Hello, this documentation is not clear about Rls identities. Can you send a body example?
  I tested this and not filter the rls, return all rows.
  { "queries": [ { "query": "EVALUATE VALUES(table)" } ], "identities": [ { "username": "[email protected]", "roles": [ "myrole" ], "datasets": [ "xxx-xxx-xxx-xxxx" ] } ] }
  - v-kpoloju-msft
    Community Support
    1 year ago
    Hi marcio_fornari,
    
    Thank you for the follow-up and for sharing the request body you tested. You are correct in using the identities field in the Execute Queries endpoint to simulate RLS. However, RLS filtering only applies under specific conditions. Here are the steps to ensure it is configured correctly.
    
    Requirements for RLS to Work:
    
    Premium or Fabric Capacity: The dataset must be in a workspace supported by Premium capacity (P SKU or Embedded A SKU) or Microsoft Fabric capacity.
    
    Defined RLS Roles: Ensure that the role name (such as "myrole" in your example) is precisely defined in the Power BI dataset under Manage roles in Power BI Desktop or service.
    
    Matching Identity: The username must align with what your DAX RLS expressions evaluate using USERNAME() or USERPRINCIPALNAME(). For instance, if your DAX rule filters based on email, the username must be the user's email.
    
    Example Request Body (Correct Format):
    
    { "queries": [ { "query": "EVALUATE VALUES('YourTableName')" } ], "identities": [ { "username": "[email protected]", "roles": [ "SalesRegionRole" ], "datasets": [ "your-dataset-guid" ] } ] }
    
    Required Headers: Ensure you are using an Azure AD bearer token with permissions to read from the dataset. The request must include:
    
    Authorization: Bearer <your-access-token> Content-Type: application/json
    
    If you are still retrieving all rows, it typically indicates one of the following issues: The RLS role name does not match exactly. The dataset is not located in a Premium/Fabric workspace. Your dataset does not utilize USERNAME() or USERPRINCIPALNAME() in the DAX security rule. The token does not impersonate the user (though the identities field should simulate this if Premium capacity is enabled).
    
    If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
    
    Thank you for using Microsoft Community Forum.

Forum Discussion

Datasets - Execute Query endpoint with RLS

12 Replies

Related content

Custom Endpoints - Eventstream

Refresh SQL Endpoint API

Datasets - execute queries REST API

sql endpoint connection

Lakehouse SQL endpoint