Power View Can’t Load Power Pivot Models
Scenario: You have created an SSRS application in SharePoint and specified that the application pool would use a specific domain service account that is designated to SSRS. When you attempt to create a Power View report from a deployed Power Pivot model, you get the following error:
An error occurred while loading the model for the item or data source ‘<path to Excel file>’. Verify that the connection information is correct and that you have permissions to access the data source.
Here is the full stack:
<detail><ErrorCode xmlns=”http://www.microsoft.com/sql/reportingservices“>rsCannotRetrieveModel</ErrorCode><HttpStatus xmlns=”http://www.microsoft.com/sql/reportingservices“>400</HttpStatus><Message xmlns=”http://www.microsoft.com/sql/reportingservices“>An error occurred while loading the model for the item or data source ‘<path>’. Verify that the connection information is correct and that you have permissions to access the data source.</Message><HelpLink xmlns=”http://www.microsoft.com/sql/reportingservices“>http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsCannotRetrieveModel&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=11.0.5058.0</HelpLink><ProductName xmlns=”http://www.microsoft.com/sql/reportingservices“>Microsoft SQL Server Reporting Services</ProductName><ProductVersion xmlns=”http://www.microsoft.com/sql/reportingservices“>11.0.5058.0</ProductVersion><ProductLocaleId xmlns=”http://www.microsoft.com/sql/reportingservices“>127</ProductLocaleId><OperatingSystem xmlns=”http://www.microsoft.com/sql/reportingservices“>OsIndependent</OperatingSystem><CountryLocaleId xmlns=”http://www.microsoft.com/sql/reportingservices“>1033</CountryLocaleId><MoreInformation xmlns=”http://www.microsoft.com/sql/reportingservices“><Source>ReportingServicesLibrary</Source><Message msrs:ErrorCode=”rsCannotRetrieveModel” msrs:HelpLink=”http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsCannotRetrieveModel&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=11.0.5058.0” xmlns:msrs=”http://www.microsoft.com/sql/reportingservices“>An error occurred while loading the model for the item or data source ‘<path>’. Verify that the connection information is correct and that you have permissions to access the data source.</Message><MoreInformation><Source>Microsoft.ReportingServices.ProcessingCore</Source><Message msrs:ErrorCode=”rsErrorOpeningConnection” msrs:HelpLink=”http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsErrorOpeningConnection&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=11.0.5058.0” xmlns:msrs=”http://www.microsoft.com/sql/reportingservices“>Cannot create a connection to data source ‘TemporaryDataSource’.</Message><MoreInformation><Source>Microsoft SQL Server 2012 Analysis Services</Source><Message>SetAuthContext need to be run as sysadmin.</Message></MoreInformation></MoreInformation></MoreInformation><Warnings xmlns=”http://www.microsoft.com/sql/reportingservices” /></detail>
Resolution: The most important information is the message “SetAuthContext need to be run as sysadmin“. This tells us that the SSRS account doesn’t have admin access to the SSAS instance configured in SharePoint mode. Another way to confirm this is to run the SQL Profiler connected to the SSAS instance, e.g. SERVER\POWERPIVOT, and attempt to create a Power View report. You would see the same error message in the Profiler.
How do we fix this horrible issue? Connect to the SSAS instance in SSMS, right-click on the server node and then click Security. Then, add the SSRS account to the list to grant it admin rights to SSAS. This is something SharePoint should have done during the setup of the SSRS application since the SharePoint farm account has admin rights to the SSAS instance but apparently this step has been omitted.
