Powerpivot and the Unattended Service Account

  • Article

My customer had a tough time understanding how the Unattended Service account relates to the actual PowerPivot workbooks. This specific scenario was Powerpivot 2012 and SharePoint 2013. Quick shout out to my colleague Rick Andring for all the help he provided in helping my customer understand all of this stuff. His teams' blog can be found here: https://blogs.technet.microsoft.com/excel\_services\_\_powerpivot\_for\_sharepoint\_support\_blog/

Here are some best practices/gotchas to keep in mind:

  • If upgrading Data models from Excel 2010 to Excel 2016, which is what my customer was doing, you must download it to your laptop/desktop, open in Excel 2013, upgrade and then upload it back into the SharePoint document library. It is a best practice to open it and upgrade in Excel 2013 first before going straight to 2016.NOTE: Once upgraded you CANNOT roll back the upgrade, it remains upgraded. Make a copy in case you think you may need to go back to the previous data model
  • After granting the Unattended Service account read rights to the datasource you wish to query within your workbook, AND after setting up a Target Application in your Secure Store Service AND after referencing it in your Excel Services Service Application, you now have to set your workbook Connection in your workbook to NONE so that it references the Unattended Service Account. WARNING: NEVER EVER EVER modify the default datasource ThisWorkbookDataModel. THIS IS NOT SUPPORTED!! In this example, notice how the datasource called SqlServer PEVMFORKAY1POWERPIVOT Test is selected. This is the connection that will be set to None.
    workbook
  • Now if for whatever reason, you or one of your users manages to modify the ThisWorkbookDataModel setting to something other than its default setting, the only way to change it back to its rightful setting of 'Authenticated User account' is to open it in Excel 2013 OR 2016 on a 32-bit machine. If you try to do this on a 64-bit machine the options to change this will be greyed out.

Additional Resources