Protect Power Queries

How you protect power queries is a question that will come up after you've built a solution that relies heavily on Power Query, especially if you're going to release it to other users.

(This is a quick post, as I'm in Australia at the Unlock Excel conference, but still wanted to get a post out this week.)

Can you Protect Power Queries?

The answer to this is yes, you can.  It’s actually very easy, and prevents your users from not only modifying your queries, but adding new queries to the workbook as well. Essentially, it shuts the door on any additions or modifications to query logic, while still allowing queries to be refreshed… at least, it should.

So how do we Protect Power Queries?

To protect Power Queries we simply need to take advantage of the Protect Workbook Structure settings:

  • In Excel (not Power Query), go to the Review tab
  • Choose Protect Workbook
  • Ensure that Structure is checked
  • Provide a password (optional)
  • Confirm the password (if provided)

Once you’ve done this, the Power Query toolsets will be greyed out, and there is no way for the user to get into the editor.

image

Does refresh work when you Protect Power Queries?

This part kills me.  Seriously.

The answer to this question depends on whether or not you use Power Pivot.  If you don't, then yes, you're good to go.  As long as all your tables land on worksheets or as connections, then a refresh will work even when you protect Power Queries via the Protect Workbook method.

If, however, you have a single Power Query that lands in the data model, your stuffed.  If Power Pivot is involved, then the refresh seems to silently fail when you protect Power Queries using this method (and I don't know of another short of employing VBA, which is a non-starter for a lot of people).

It's my feeling that this is a bug, and I've sent it off to Microsoft, hoping that they agree and will fix it.  We need a method to protect both Power Query and Power Pivot solutions, and this would do it, as long as the refresh will consistently work.

Caveats about locking your workbook structure:

Some caveats that are pretty standard with protection:

  • Losing your password can be detrimental to your solution long-term. Make sure you have some kind of independent system to log your passwords so this doesn’t happen to you. And if your team is doing this, make sure you audit them so you don’t get locked out when as staff member leaves for any reason.
  • Be aware that locking the workbook structure also locks the ability for users to get into Power Pivot.
  • Workbook security is hackable with brute force macro code available on the internet for free. (Please don’t bother emailing me asking for copies or links to this code. I don’t help in disseminating code which can be used to hack security.) While protecting the workbook structure will stop the majority of users from accessing your queries, it should not be mistaken for perfect security.

15 thoughts on “Protect Power Queries

  1. Does workbook security differ from the regular protect file/sheet? I thought Microsoft fixed this in Excel 2013 (they switched to a more secure algorithm so the standard binary hack didn't work).

  2. Sorry about that Wyn. For reference, I got an agreement from someone that it sounds like a bug... Still waiting on an answer from the Dev team on it though.

    Phil, there are four kinds of security in Excel; Workbook, Workbook Structure, Worksheet and VBA project.

    While there have been improvements made, each is still vulnerable to hacking if the user is bound and determined to get in.

  3. Pingback: Power BI Premium, Apps and Report Server | Guy in a Cube

  4. It should also be noted that the queries can still be displayed by using the "Send Feedback" button (at least in Excel 2013) which opens an email document with all of the query code in the workbook exposed.

  5. Interesting... the Send Feeback buttons have changed in non-insider builds of Office 2016, so you don't see the email. Again though, one should not assume that you won't be able to see the queries, only that a novice user wouldn't be able to edit them.

  6. Hi ken,

    Excitement, disappointment and now excitement again! It works for me when using Power Update.

    For PU a query must be loaded into Power Pivot for updating the data to SQL server. So, I have Power Query's to refresh and a datamodel in Power Pivot to refresh. When locking the workbook the password can be omitted in a PU task and during refreshing PU unlocks the file, does its job and locks the file again before closing it.

    For me this seems to work fine and I can finally use some sort of protection. Do you agree or am I missing something?

  7. Ken,
    Nice article ! I was wondering can you lock power query in the Power BI workbook as well ? Havent found a way to do it.

  8. Hi there. I've attempted to limit access to PowerQuery using the Protect Structure mechanism, but the query fails when returning the data to the worksheet (I can see that the query is in fact running, as it shows the output in the popup when I hover over my query). I'm not using PowerPivot. Why would this be the case?

  9. Hi Abhi,

    The pbix file isn't intended to be secured except by publishing it to Power BI. So no, and I doubt they'll ever give you a way to do so.

  10. Was this capability added very recently in PQ? When I enable Protect Workbook it greys out the PQ options, but trying to refresh the queries from the Data ribbon just fails with a "Download did not complete." error. I've tried on stock 2016 and 2013 using PQ last updated about a year ago...

  11. Hi Paul,

    This post is from May, so it's not recent. In fact, I think it goes back a long way. And even in the Insider built of Excel 2016 today, it's still not working.

    Keep in mind, this only happens if you load your power queries into the data model (power pivot). If you land them to tables, they can still refresh. Sadly, that pretty much makes the entire feature useless to me, as I load virtually everything into the data model.

  12. Hi ?Ken, Good article, thanks. Do you have any advice on how to protect a power query App if you are loaded it to a external customers systems or making the App distributable? You mention VBA, or should I take another route, such as transferring some of the core logic to an add-in.
    Many thanks

  13. Hi Peter,

    If you're copying a Power Query script to an external customer system, there's not much you can do. The only thing I can think of is to maybe build your own custom connector package, but I haven't actually doe that myself. not sure if it would work as a method to protect IP alone. If you can move your logic to an addin so that it runs the script locally then copies and pastes the data to the customer workbook, that could work, but they won't be able to update it without your addin.

Leave a Reply

Your email address will not be published. Required fields are marked *