通过

Recovering from cryptic errors thrown when importing from PowerPivot

  • 项目

Sometimes people get an error message about “Access is Denied” when trying to import from a PowerPivot workbook and are perplexed by its cause. The user can open the PowerPivot workbook locally, but import fails with an error like this:

image

Here is the root cause of this error. In my previous post about importing from PowerPivot, I explained that the workspace database server does an ImageLoad  and restores the ABF embedded in the PowerPivot workbook. This restore operation only succeeds if the service account running the workspace database instance has sufficient privileges to access the file location where the PowerPivot workbook is stored. So, if your service account is a low privileged user (eg the default NT Service\MSSQLServerOLAPService account) and you try to import a PowerPivot workbook from your desktop, your documents folder, or a network share, the service account fails to access the file location of the ABF and provides the above error.

There are two solutions to this problem:

  • Copy the PowerPivot workbook to a place in the file system that the low privileged service account can access (eg, OLAP\Backup)
  • Use a higher privileged user (such as yourself) as the service account for the workspace database service instance. This is not secure. It violates the principle of giving least possible privilege to service account users. That said, it is a very practical solution for many people. 

We are changing this error message after CTP3 so hopefully the experience will be less cryptic. However, the core of the experience will remain the same – both you and the service account must have file system access to the PowerPivot workbook for both the metadata and the data to be imported.

PS – you will see a similar error message when you try to import from PowerPivot when you are using a remote workspace database server. Because it is unlikely that the service account user on the remote server will have access to the likely location of your PowerPivot workbooks (your local machine), we simply block data import at all times when you are modeling remotely. You can still import the metadata in that scenario.

Comments

  • Anonymous
    February 21, 2015
    In Visual Studio 2013, you also need to ensure that the service account has access to the Projects folder.
  • Anonymous
    January 20, 2016
    Thank you @Stuart Corrigall! You've provided the missing detail here. The SSAS account needs access to BOTH the source xslx file AND the VS project folder. P.S. This continues to be a problem in VS 2013, VS 2015, on both SQL 2014 and SQL 2016 CTP3. So, still not resolved by a long shot.