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.
- Edited by Mark Weisman Wednesday, July 02, 2014 2:46 PM
Got it - Thanks for the feedback again. We'll keep this in mind as we think about improvements on this area.
- Marked as answer by Miguel.LlopisMicrosoft employee, Owner Wednesday, July 02, 2014 8:51 PM
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 !
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.
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.
- Edited by Mike Honey - Manga Solutions Thursday, January 22, 2015 3:15 AM
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.
- Edited by Curt HagenlocherModerator Thursday, January 22, 2015 2:14 PM
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:
- Keep current product behavior as the default.
- 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.
- 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.
That new "global option" sounds like a good way out.
Also thanks Curt and Miguel for sharing your thoughts on this in such depth.
Can you give a smaller window on this ETA? This is causing issues within my organization.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:
- Keep current product behavior as the default.
- 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.
- 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.
It will be available in early March or early April.
Thanks,
M.
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