Suppress Power Query's Native Database Query Warning
Is there a way to suppress the "Native Database Query" warning issued by Power Query when running a SQL query directly against a Teradata database?  This warning states, "You are about to run a native database query.  Native queries can make changes to the database.  Would you like to run the following query?"
July 2nd, 2014 12:55am

There is currently no way to suppress this warning. The warning though doesn't show up when you author this Native DB query via the "From ..." dialog.

Could you share some more details about your use case? Are you sharing queries with other users via Power BI Sharing/Search, are you refreshing a workbook with queries from somebody else, or are you authoring these queries yourself via custom formulas or similar mechanism?

Knowing more about your scenario, pain points with the current warning and expectations for what the right behavior should be would be extremely helpful for us. We're actively thinking about ways of improving this in the near future.

Thanks,
M.

Free Windows Admin Tool Kit Click here and download it now
July 2nd, 2014 1:12am

I'm invoking a function that supplies a value to the WHERE clause of a native query.  The warning serves no purpose, and requires an additional mouse click on the Run button to produce results.
July 2nd, 2014 1:46am

Thanks - Would you consider this warning helpful in any other context? For instance, when trying to refresh a query that you received from a different user?
Free Windows Admin Tool Kit Click here and download it now
July 2nd, 2014 2:00am

Not really.  Presumably, database privileges granted to me by my DBA are commiserate with my knowledge and skills.  If I don't have permissions to modify or create a table how much harm can I do with a SELECT?  If I do have such permissions then I better know what I'm doing.  The additional warning isn't going to increase my knowledge or prevent me from wreaking havoc.

July 2nd, 2014 4:51pm

Got it - Thanks for the feedback again. We'll keep this in mind as we think about improvements on this area.

Free Windows Admin Tool Kit Click here and download it now
July 2nd, 2014 11:51pm

Hi Miguel,

We have the same issue when trying to run an Sql query with a parameter (Like ID) from the sheet.

Is there a workaround ? What do you mean by "The warning though doesn't show up when you author this Native DB query via the "From ..." dialog."

Is there a plan to resolve this on the upcoming versions of PQ ?

Thanks !

August 20th, 2014 3:18pm

Hi Miguel,

Agree with Mark and Iban. This warning doesn't provide anything useful. In my case, I have a native query that is being dynamically constructed via a function called for each row in a table that could have tens of thousands of rows. The warning makes it unusable.

Could you update us on if / when you expect this to be fixed?

Thanks.

Free Windows Admin Tool Kit Click here and download it now
January 7th, 2015 4:42am

I also agree with Mark and Iban and Simon.  It's up to the database server to protect it's data - trying to restrict this in a particular client tool will never be effective, so this is actually "Security Theatre".

The usability of the current UI for is fairly awful - having to robotically click (with the mouse only) a repeated pop-up should never have got through.  The only option for management seems to be to blindly revoke all approvals - no-one would ever do that.

Apologies for the frustration but with my current task I have 4 pop-ups to dismiss on each refresh, but due to SQL complexity they take a few minutes each to appear.  If I don't sit watching the screen like a hawk, my process stalls.


January 22nd, 2015 6:14am

If we never prompted here, then I could send you a workbook with the embedded query "drop table foo". You'd open it, Power Query would run analysis and we'd execute the statement -- all before you had any idea what had happened. It's true that there are many potential users for whom showing the SQL text isn't useful. Hopefully, those users don't have the kinds of server permission that would let a query run with their credentials do lasting damage. But the prompt is to deal with the very possible risk of having even a sophisticated user run a query by accident that they shouldn't have.

The original intent of the feature was to support a scenario where a user says "I already have a SQL query whose output I want to consume into Power Query; why can't I just use that?" It did not take parameterization into account. We subsequently considered adding parameterization to this method, but didn't feel that we could sufficiently guarantee security. That is, if your query text is Sql.Database("server", "database", [Query="exec @sql", Parameters=[sql="drop table foo"]], the user would be prompted only with "exec @sql" and the malicious payload would be delivered invisibly.

We very much do not want to have the responsibility of parsing multiple different dialects of SQL to try to guess whether or not it contains potentially-malicious code.

Our current thinking on this is that we might let advanced users write "trusted code" which has to go through a separate verification step. Code of this type might be able to generate SQL statements without a user prompt. So a workbook might have a dependency on the "My Trusted Code" module, and simply won't run if that module hasn't been installed on the local machine. The user would then need a trustworthy (but convenient) way to obtain that module from a trustworthy source.

Free Windows Admin Tool Kit Click here and download it now
January 22nd, 2015 5:09pm

Mike, Simon et al.,

Thanks for your feedback on this area. We are very sensitive to the current poor experience for the scenario that you are facing, while at the same time need to balance optimizations in that scenario with the set of concerns that Curt is pointing out.

As mentioned in my previous reply on the thread, we are trying to improve this scenario and are currently working on a solution that should give you enough flexibility to achieve this scenario without having to go through these prompts but at the same time keeping the default behavior to guard against the issues that Curt highlights.

Our plan is to:

  1. Keep current product behavior as the default.
  2. Add a global option (i.e. under the PQ Options dialog) to control whether to require user approval for new native queries or not. This option is user/machine-wide and is also accessible via the registry, thus, enabling also the ability to set it via IT policy (to either prompt or not) if a company wants to do so and not let users change it on their own.
  3. Switching from "Not require user approval" to "Require user approval" (back from non-default to default) will cause all previously approved queries to be revoked. It will get the user back to a "fresh environment".

Expect to see this change in one of our monthly PQ updates in the next couple of months.

Thanks,
M.

January 22nd, 2015 8:11pm

Miguel, this seems like a reasonable, middle-ground approach.
Free Windows Admin Tool Kit Click here and download it now
January 22nd, 2015 8:17pm

That new "global option" sounds like a good way out.

Also thanks Curt and Miguel for sharing your thoughts on this in such depth.

January 23rd, 2015 6:13am

Mike, Simon et al.,

Thanks for your feedback on this area. We are very sensitive to the current poor experience for the scenario that you are facing, while at the same time need to balance optimizations in that scenario with the set of concerns that Curt is pointing out.

As mentioned in my previous reply on the thread, we are trying to improve this scenario and are currently working on a solution that should give you enough flexibility to achieve this scenario without having to go through these prompts but at the same time keeping the default behavior to guard against the issues that Curt highlights.

Our plan is to:

  1. Keep current product behavior as the default.
  2. Add a global option (i.e. under the PQ Options dialog) to control whether to require user approval for new native queries or not. This option is user/machine-wide and is also accessible via the registry, thus, enabling also the ability to set it via IT policy (to either prompt or not) if a company wants to do so and not let users change it on their own.
  3. Switching from "Not require user approval" to "Require user approval" (back from non-default to default) will cause all previously approved queries to be revoked. It will get the user back to a "fresh environment".

Expect to see this change in one of our monthly PQ updates in the next couple of months.

Thanks,
M.

Can you give a smaller window on this ETA? This is causing issues within my organization.
Free Windows Admin Tool Kit Click here and download it now
January 29th, 2015 8:57pm

It will be available in early March or early April.

Thanks,
M.

January 29th, 2015 9:05pm

I really need this too!  

Since the latest update I have over 30 dashboards asking me to approve the running of Native Queries before updating.

As these normally run via a macro before I even get into work this process is now failing so would really appreciate this release ASAP too Miguel.

Thanks,

Mike

Free Windows Admin Tool Kit Click here and download it now
February 3rd, 2015 6:33am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics