Solving security issue in PowerPivot for SharePoint and Power View

I just installed a brand new server (well, a virtual machine) with SharePoint 2010 SP1 and SQL Server 2012 RC0, including PowerPivot and Reporting Services / Power View. The server is joined to the domain I use in our development environment.

I published a workbook in the PowerPivot Gallery and my user was immediately able to connect, browse and navigate data of the Excel workbook published by SharePoint. Moreover, I was able to open it in Power View. However, other users failed the connection. After double check of permission, it was evident that the problem was caused by something outside SharePoint.

Let’s see what happened to “bad” users. Trying to open the workbook in SharePoint apparently worked, but as soon as the user clicked on a slicer, this error appears:

The data connection uses Windows Authentication and user credentials could not be delegated. The following connections failed to refresh:

PowerPivot Data

Moreover, trying to open the same workbook with Power View generated this error:

An error occurred while loading the model for the item or data source ‘http://sp2012/PowerPivot%20Gallery/HelloWorldPicnicDenaliCTP3.xlsx’. Verify that the connection information is correct and that you have permissions to access the data source.

I cannot explain why this type of errors was produced only by some users. Below I attached the detailed description of the error in PowerView, that says Cannot convert claims identity to windows token.

I see the same error in Windows Event Log (XML description below): Audit Failure with reason “Unknown user name or bad password”

After a couple of hours and some hint from some good friend, I’ve found the solution. Because my domain has been originally created with Windows 2000 and over the years migrated to Windows 2003 and then Windows 2008 R2, there is some setting that needs to be fixed. To solve the issue I had to add, to all the user that didn’t worked, a particular security permission (that was present in working accounts). The Authenticated User group needs Read permission on the user account that wants to connect to PowerPivot for SharePoint and Power View. A step by step description about how you can do that is available here (read the “Ok, so what is the fix?”). A more complete explanation is included in this post by Denny Lee.

I hope this will help someone else looking for the same issue in the future (this is the reason while I’m going to include all details about this error).

***** Error in PowerView

<ErrorCode xmlns=”http://www.microsoft.com/sql/reportingservices”>rsCannotRetrieveModel</ErrorCode>

<Message xmlns=”http://www.microsoft.com/sql/reportingservices”>An error occurred while loading the model for the item or data source ‘http://sp2012/PowerPivot%20Gallery/HelloWorldPicnicDenaliCTP3.xlsx’. 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.1750.32</HelpLink>

<ProductName xmlns=”http://www.microsoft.com/sql/reportingservices”>Microsoft SQL Server Reporting Services</ProductName>

<OperatingSystem xmlns=”http://www.microsoft.com/sql/reportingservices”>OsIndependent</OperatingSystem>

<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.1750.32″ xmlns:msrs=”http://www.microsoft.com/sql/reportingservices”>An error occurred while loading the model for the item or data source ‘http://sp2012/PowerPivot%20Gallery/HelloWorldPicnicDenaliCTP3.xlsx’. Verify that the connection information is correct and that you have permissions to access the data source.</Message>

<Source>Microsoft.ReportingServices.ProcessingCore</Source>

<Message msrs:ErrorCode=”rsErrorImpersonatingUser” msrs:HelpLink=”http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsErrorImpersonatingUser&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=1.0” xmlns:msrs=”http://www.microsoft.com/sql/reportingservices”>Cannot impersonate user for data source ‘TemporaryDataSource’.</Message>

<Source>Microsoft.ReportingServices.ServiceRuntime</Source>

<Message msrs:ErrorCode=”rsClaimsToWindowsTokenError” msrs:HelpLink=”http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsClaimsToWindowsTokenError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=1.0″ xmlns:msrs=”http://www.microsoft.com/sql/reportingservices”>Cannot convert claims identity to windows token.</Message>

<Message>For more information about this error navigate to the report server on the local server machine, or enable remote errors</Message>

</MoreInformation>

</detail>

***** Error in Windows Event Log