Disable Run Native Query Dialog

[WishAskedSooner]

Hi Experts!

I have an Excel workbook that pulls down data from an Azure dB via PQ. Technically, the SQL in the M code selects from a user-defined function in Azure using parameters from named ranges in the workbook. I only mention it in the off chance it is relevant.

Frequently, but not always, when I refresh the queries I get the dreaded dialog asking if I want to Run Native Query. Technically, I am refreshing the queries via VBA.

This is causing automation problems because the user has to manually click Run. Otherwise, Excel just hangs.

How do I configure PQ to not pop up that dialog?

Thanks!

[WishAskedSooner]

I think I found the solution which wasn't easy.

Go to Data - Get Data - Query Options. Uncheck the "Require user approval for new native database queries".

Mine was checked. I won't know for sure if it works properly for a few days. But, hopefully, that does the trick! I will try to remember to come back here and mark this as the solution if it works.

[Nasif_Azam]

Hey @WishAskedSooner ,

To disable the “Run Native Query” prompt in Power Query (PQ) when using native SQL (such as a call to a SQL function), you need to explicitly approve the native query for each data source in your workbook's data source privacy settings.  To Approve Native Query Execution Permanently, Follow:

Follow these steps in Excel:

1. Manually Approve the Query Once

• Open Power Query Editor (Data tab → Get Data → Launch Power Query Editor).

• Open the query that triggers the dialog.

• Run the query once.

• When the “Run Native Query” dialog appears, check the box that says: "Don't show this warning again for this database."

• Then click Run.

That setting is stored in the global permissions for the workbook.

 

2. Verify/Set Permissions Manually

If the dialog still appears or if you want to preconfigure it:

• Go to Data tab → Get Data → Data Source Settings.

• In the Data Source Settings window:

  • Select your Azure SQL data source.

  • Click Edit Permissions.

  • Ensure:

    • The privacy level is appropriate (e.g., set to "Organizational" or "None" to avoid isolation).

    • Credential type is saved correctly (Windows, database, or OAuth).

    • Check if there's an advanced option regarding native query approval (depending on Excel version).

• Click Clear Permissions, then reconfigure, checking “Don’t show again” during refresh.

3. Use VBA Safely (Workaround)

Since you’re using VBA, the dialog can block execution. If Excel doesn’t remember the setting across sessions or machines, there’s no official VBA method to suppress the dialog directly.

However, two workarounds:

• Use Power Query Parameters (from named ranges) but avoid Value.NativeQuery where possible, as it triggers this prompt.

• Instead, construct your SQL query inside Power Query using Text.Combine() or Table.SelectRows() so Excel doesn't treat it as a native query.

For Detailed Infromation:

https://learn.microsoft.com/en-us/power-query/manage-data-source-settingshttps://community.fabric.microsoft.com/t5/Desktop/How-to-disable-quot-Run-Native-Query-quot-warning/...

https://stackoverflow.com/questions/63144468/how-to-disable-run-native-query-warning-in-excel

https://www.fourmoo.com/2020/09/28/disabling-the-run-native-query-dialog-in-power-bi/

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[WishAskedSooner]

@Nasif_Azam,

I am testing your suggestions but one problem I am encountering is that I cannot find the options "Don't show this warning again for this database." or "Don't show again".

Here is what I get:

Similarly, when I click on the permissions in the PQ Editor I just see this:

I am using Microsoft® Excel® for Microsoft 365 MSO (Version 2505 Build 16.0.18827.20102) 64-bit which I believe is the most up to date version.

Your thoughts?

[Nasif_Azam]

Hey @WishAskedSooner ,

Thank you for sharing the screenshots very helpful! You're absolutely right: in some versions of Excel for Microsoft 365, especially those on the Monthly Enterprise Channel or Preview builds, the checkbox “Don’t show this warning again for this database” is missing, even though documentation and older versions might show it. This appears to be a regression or UI limitation in certain builds, and unfortunately, Microsoft hasn’t exposed a setting in the Excel UI to globally bypass this dialog (unlike Power BI Desktop).

Workaround-1: Pre-approve Native Queries via Global Permissions

Though the checkbox is not visible, your approval is still saved once you run the query. You can confirm this by:

• Going to Data > Get Data > Data Source Settings

• Select your Azure SQL source > Edit Permissions

• If it says: “You have approved 1 Native Query for this source,” as shown in your second screenshot, it means approval was saved.

But here’s the catch: Power Query in Excel might not reuse this approval reliably when refreshing via VBA, especially if: You are switching environments, Using workbook copies, and Changing connection strings.

Workaround-2: Avoid Native Queries via Value.NativeQuery

You can restructure your M code to avoid using Value.NativeQuery.

Instead of this:

Value.NativeQuery(conn, "SELECT * FROM myFunction('" & param & "')")

Use:

let
    Source = Sql.Database(...),
    Output = Source{[Schema="dbo", Item="myFunction"]}[Data],
    Filtered = Table.SelectRows(Output, each [Column] = param)
in
    Filtered

This keeps everything within Power Query, avoiding native query prompts.

Workaround-3: Alternative VBA Refresh Strategy

Instead of Workbook.RefreshAll, try refreshing each query individually using:

ThisWorkbook.Queries("QueryName").Refresh

Or use:

ActiveWorkbook.Connections("Query - YourQueryName").Refresh

Sometimes this can prevent triggering dialogs for each query.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[WishAskedSooner]

@Nasif_Azam,

More great and very informative suggestions! Thank you so much!

I am hoping unchecking the box in my post works, but if it doesn't, I will definitely be back and try your suggestions.

Thanks!

[WishAskedSooner]

@Nasif_Azam,

Wow! That is a much more thorough reply than I expected, and I truly appreciate it!

I will definitely test the solutions you have suggested and post back my results.