none
Power Query from SharePoint list shows results to people who do not have access RRS feed

  • Question

  • I have connected Power Query to a SharePoint list and sent the Excel file to a person who does not have access to the SharePoint list, when the user opens the document there is not log-in or authentication question but the data from the SharePoint list is shown. Is there any way to secure that the data is only shown to users that have access to the SharePoint list?

    (We do not have Power BI, only Office 365 E3)

    Friday, September 25, 2015 7:20 AM

Answers

All replies

  • If you check "Refresh data when opening the file" in the connections properties of your respective queries, the prompt for credentials should pop up.

    Also the "old data" will not be shown.


    Imke Feldmann TheBIccountant.com

    Friday, September 25, 2015 11:53 AM
    Moderator
  • Thank you for taking the time to answer. I did actually try this but when the user opened the file he still saw the data in the read only mode.

    I find it weird that the data would be displayed without credential prompt, quite the potential for security issues with data in SharePoint or other sources.

    Edit: Could it possibly have to do with the security setting for external data? Upon opening the document the user first sees all the "Old data" and a notification that external data connections have been disabled. I guess then the data connection does not update upon opening the document since it is automatically disabled. The problem is that this setting is the default setting and allowing all external data is not recommended.
    • Edited by Anders99 Monday, September 28, 2015 7:07 AM
    Monday, September 28, 2015 6:43 AM
  • To followup we have tested a lot and also been in contact with Microsoft Technical support. It seems that you can get the data to not show up for people who do not have access if you only use normal tables and charts. If you use PowerPivot the data will be shown regardless of access (Have tried it with data from both data-model and directly from power query connection).

    I will keep digging into this but if anyone knows how you can make PowerPivot not show data regardless of access to external data connection it would be much appreciated.

    Friday, October 2, 2015 10:50 AM
  • Sorry, completely missed your response from Monday.

    Please check out if this works for you: This should be the setting of the PQ connection that retrieves the data from SP.


    Imke Feldmann TheBIccountant.com


    Friday, October 2, 2015 7:39 PM
    Moderator
  • Yes this is what i used and it works great for normal tables and charts, but as soon as you involve a power pivot chart or table it will show that data to the user regardless of access. Really strange and I am working with Microsoft Support now to get an answer on why this is happening. Just as a note I have set in the PowerPivot that I do not want to save data as well but it doesnt matter, the PowerPivot charts and tables show up for users with the data I had when I saved the document regardless.
    Wednesday, October 7, 2015 2:39 PM
  • Wow - so the refresh on close will not erase the PP data and the refresh on open will fail because of lacking credentials?

    Sorry, no idea here.

    Please share if/how this is solved.

    Possible workaround meanwhile if performance allows, stick to Power Query only: Create a consolidated denormalized table in "Only create Connection" mode and build you reports from it like described here: http://www.thebiccountant.com/2015/09/19/perfect-analysis-services-ssas-reports-in-excel-using-power-query/#more-492


    Imke Feldmann TheBIccountant.com

    Wednesday, October 7, 2015 4:33 PM
    Moderator