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:
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.