THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

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

<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 '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&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsCannotRetrieveModel&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=11.0.1750.32</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.1750.32</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&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsCannotRetrieveModel&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;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>

    <MoreInformation>

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

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

      <MoreInformation>

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

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

        <MoreInformation>

          <Source></Source>

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

        </MoreInformation>

      </MoreInformation>

    </MoreInformation>

  </MoreInformation>

  <Warnings xmlns="http://www.microsoft.com/sql/reportingservices" />

</detail>

 

 

 

***** Error in Windows Event Log

<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">

  <System>

    <Provider Name="Microsoft-Windows-Security-Auditing" Guid="{54849625-5478-4994-A5BA-3E3B0328C30D}" />

    <EventID>4625</EventID>

    <Version>0</Version>

    <Level>0</Level>

    <Task>12544</Task>

    <Opcode>0</Opcode>

    <Keywords>0x8010000000000000</Keywords>

    <TimeCreated SystemTime="2011-11-18T16:41:24.093624000Z" />

    <EventRecordID>5953</EventRecordID>

    <Correlation />

    <Execution ProcessID="572" ThreadID="2780" />

    <Channel>Security</Channel>

    <Computer>SP2012.DOMAINNAME.com</Computer>

    <Security />

  </System>

  <EventData>

    <Data Name="SubjectUserSid">S-1-5-18</Data>

    <Data Name="SubjectUserName">SP2012$</Data>

    <Data Name="SubjectDomainName">DOMAINNAME</Data>

    <Data Name="SubjectLogonId">0x3e7</Data>

    <Data Name="TargetUserSid">S-1-0-0</Data>

    <Data Name="TargetUserName" />

    <Data Name="TargetDomainName" />

    <Data Name="Status">0xc000006d</Data>

    <Data Name="FailureReason">%%2313</Data>

    <Data Name="SubStatus">0xc0000064</Data>

    <Data Name="LogonType">3</Data>

    <Data Name="LogonProcessName">C</Data>

    <Data Name="AuthenticationPackageName">Kerberos</Data>

    <Data Name="WorkstationName">SP2012</Data>

    <Data Name="TransmittedServices">-</Data>

    <Data Name="LmPackageName">-</Data>

    <Data Name="KeyLength">0</Data>

    <Data Name="ProcessId">0x514</Data>

    <Data Name="ProcessName">C:\Program Files\Windows Identity Foundation\v3.5\c2wtshost.exe</Data>

    <Data Name="IpAddress">-</Data>

    <Data Name="IpPort">-</Data>

  </EventData>

</Event>

Published Saturday, November 19, 2011 12:26 AM by Marco Russo (SQLBI)
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Marco Russo (SQLBI) said:

Very good Nick - thanks!

Marco

November 21, 2011 4:32 PM
 

Sarpreet Verma said:

I also suufered for 2 days with this problem . To solve it go to services.msc and check all SQL and Sharepoint services should be running with one user acoount by whom all the configurations are done . Don't change if any services is running with Local System .Restart all SQL and Sharepoint services and then restart the system. Hope it will solve your problem :).

November 26, 2012 5:39 AM
 

Fred Z. said:

I had this same issue. For some users on the domain, I noticed when they tried accessing data from a backend data historian through Excel Calculation Services, c2wts.exe reported "Unknown user name or bad password" in the Sharepoint server security event log. We had Kerberos configured correctly but we had to add the Read permissions for Authenticated users on the user account.

February 26, 2014 5:53 PM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement