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.

8 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?

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

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