Revoke Approvals for native database queries in power bi dataflows

[Ogueddaa]

I am working on automation scripts for deploying Power BI components, but I am encountering an issue during the deployment of dataflows. This process requires manual approval to execute native SQL queries, which prevents full automation.

I am looking for a way to disable this native query approval directly in the dataflow, but I can't find an option similar to the one in Power BI Desktop, where you can allow the execution of native queries without manual validation.

[rohit1991]

Currently, Power BI Dataflows does not offer a direct option to disable or bypass the manual approval required for native SQL queries, as it does in Power BI Desktop. This behavior is by design to ensure security and prevent the execution of potentially harmful queries.

Workarounds

1. Set Up Gateway Credentials in Advance:

   • Ensure that the On-premises Data Gateway is configured with the required data source and credentials before deploying the dataflow.
   • When the gateway credentials are pre-approved, the manual approval prompt for native queries is typically bypassed.

2. Use Parameters:

   • Avoid using hardcoded SQL queries directly in the dataflow. Instead, use parameters for query parts (e.g., table names or filters).
   • Define these parameters in the dataflow and pass values dynamically through automation.

3. Power BI REST API:

   • Automate the deployment of dataflows using the Power BI REST API.
   • While the approval prompt cannot be bypassed directly, predefining and deploying queries through the API may help mitigate interruptions.

4. Set Data Source Privacy Levels:

   • If possible, configure data source privacy levels in the Power BI Service to ensure compatibility and eliminate unnecessary manual checks.

5. Use a Controlled Deployment Workflow:

   • Create the dataflow in Power BI Desktop or a template with approved queries, then deploy it programmatically to avoid needing approval during runtime.

Hi Hope this helps.

[v-tangjie-msft]

Hi @Ogueddaa ,

Whether the advice given by Poojara_D12 has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Best Regards,

Neeko Tang

[Poojara_D12]

Hi @Ogueddaa 

To bypass native query approval during dataflow deployment:

1. Use Power BI REST APIs: Deploy dataflows programmatically and ensure data sources are pre-approved in the Power BI Service.
2. Trusted Connections: Configure a gateway with credentials to avoid manual approval.
3. Pre-approve Data Sources: Approve native queries in the Power BI Admin portal.
4. Use JSON Templates: Deploy dataflows with metadata that avoids manual steps.

These methods streamline automation by pre-validating queries and configurations.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS