Excel Services Delegation Tips to Configure Page and Slicer Refresh with Power Pivot

The core of the problem is that unless you’ve set the connection to refresh when you first open the workbook, Excel Services uses its pivot cache to construct the pivot table and slicers and it is only if you manually refresh the connection, or click on a slicer, that you make an actual connection to the embedded data. Until then you are just looking at cached information. Until you click on a slicer, you don’t really know if Excel Services is working – so a strong recommendation that I make to any person doing a validating their installation is to “ALWAYS CLICK ON A SLICER” if you want to make sure that your installation is working properly.

image

The issue is that when accessing data PowerPivot looks like just another data source to both Excel desktop and Excel Services. Prior to accessing the data, if using Windows authentication, Excel Services needs to impersonate the user on the calling thread. But, in a claims-aware world, the only ‘identity’ that Excel Services has is the claims token. When the user connects (in whatever authentication method the SharePoint Web Application allows), the first thing that SharePoint does on the web front end is to translate the authentication method’s user identity to a claims token. And it is that claims token that is passed around within the farm. Remember this dialog box within Excel desktop:

image

The setting tells Excel Services what kind of lookup to perform when a new connection is started:

  • ‘None’ means to use the Unattended Execution Account specified for the Excel Services service application being used by this connection. The username and password are retrieved from Secure Store. Using these credentials, Excel Services does a Windows logon and then it calls the data provider (the msolap OLEDB provider in the case of PowerPivot).
  • ‘SSS’ means that Excel Services should access its Secure Store service application. The username and password are retrieved from Secure Store using this Application ID. Using these credentials, Excel Services does a Windows logon and then it calls the data provider Using and Excel Services does a Windows logon and then calls the data provider.
  • ‘Windows Authentication’ (which is both the default and our case today) means that Excel Services should use the interactive user’s Windows identity. The original Windows token for the user is looked up, Excel Services impersonates that user on the calling thread and then calls the data provider.

     

    Quick Solution if you do not want to use claims

    Set up a Secure Store Application ID using a Windows Account that has read access to the data

    Set Application ID field to the above Secure Store ID viA Central Admin > Excel Services > Global Settings

    Set the Workbook PowerPivot connection to SSID and enter the above Application ID  under authentications setting via the Connections button in Excel

     

     

    Dave Wickert advises how to fix (in order based on his experience of likelihood to be the root cause):

    Is the Claims to Windows Token Service started on the server running Excel Calculation Service (ECS)? For detailed information on the Claims to Windows Token Service (c2wts), you can read the msdn article. This service is turned on when doing a “New Farm” installation of PowerPivot, but if you do an “Existing Farm” installation, or have Excel Calculation Service (ECS) running on a different machine, the Claims to Windows Token Service might not be started. Check to make sure that this service is running on every server on which ECS is running. You can validate this via the “Services on Server” option in SharePoint’s Central Administration web site but it is also important to make sure that this is running from Service Control Manager (SCM accessed via services.msc). There is a known issue after reboot where the c2wts fails to start because of an unexpressed startup dependency on the crypto service. I will add a link to the KB when it is available but until then you can add the dependency manually from SCM or from an administrative command prompt with “sc.exe config c2wts depend= cryptsvc”.This will prevent the problem from reappearing after your next reboot. As per http://powerpivotgeek.com/2010/01/18/why-you-shouldnt-stop-start-analysis-services-from-scm-when-running-in-sharepoint-integration-mode/, you should not manage SharePoint services from SCM, however SCM is the truth when it comes to whether a service is running and so you should always double check by looking at the state in SCM. If SharePoint indicates that the service is started but it is not actually running per SCM, it is safe to start it from SCM (alternatively you could stop and start it from SharePoint Central Administration). As well as simply managing this service, SharePoint configures the security permissions for this service automatically as part of their setup so that all SharePoint Shared Services (which includes ECS) can use it. If you find that the service is stopped on the machine running ECS, start it. After starting this service, you should not need to do any type of  IisReset to see the system start working.

    Is your machine connected to the network? Dave has written a good blog on this also (http://powerpivotgeek.com/2009/11/06/taking-your-server-off-the-network/). If you are actually trying to run a PowerPivot demo with a machine which is not on the network, follow the steps in Dave’s blog to configure ECS to use a set of stored credentials for the PowerPivot datasource. If you are not doing this on purpose, then connect back to the network.

    The final cause would be that for some reason other than network connectivity, the Claims to Windows Token Service is not able to convert the SAML claims token to a Windows User security token. Dave provides a lot of details on these potential issues. Some quick questions to ask yourself:

  • Is the client user account (the logged in user who is browsing the workbook in IE) a domain account? If the account is a local machine account, then the Claims to Windows Token service will not be able to retrieve a Windows user security token. We do not support this scenario in V1 of PowerPivot. Interactive users must be domain users. For demo purposes in a bind you might try the workaround Dave provided for taking the server off of the network, but I have not personally tested it in this case.
  • Is the client user account in a different domain than the SharePoint servers? This is completely supported, but there must be a trust relationship established between the two domains. You could verify if a missing trust issue is causing your problems by logging in as a user account in the same domain as the SharePoint servers and try interacting with the workbooks (note that you had to have given that user access to the workbook). If it works for users in the same domain but not for users in other domains, it might be an issue with cross domain trust. Contact your domain admin to figure out what the relationships are setup as.
  • What account is the Claims to Windows Token Service running as? By default it is configured to run as Local System, and I am not aware of the reasons for changing this configuration (the msdn article also refers to the fact that it should be running as Local System). While there might be a good reason for trying to change it, it is possible that the person who altered it did not understand the implications of this change. You should probably track down the person who changed it and get an understanding of why. If you have permissions, switch it back to Local System and try the scenario again. If it works, you will need to determine why it was changed in the first place.
  • If you have gotten this far and none of the above have solved your issue, then there is the possibility that you have some custom AD configuration which is causing the issue. Dave points out one possibility:

    The account being used as the Excel Services service account must have AD rights to be able to query the object. One place where we know this restriction comes into play is if you have configured your domain controller to have a subgroup under “Users”, e.g. “Service Accounts”, which is a separate AD group that derives from “Users” –> but I am sure that there are more. AD rights for service accounts is a common problem across all of SharePoint.

    What Dave describes is one possibility where an AD configuration could cause this issue. As we discover more potential AD configurations that could cause this issue, we will try to update this list. If you are comfortable building your own test application and have gotten this far without figuring it out (and feel very confident that it is not #1), you can try running the test application we have posted here to manually test your ability to acquire a Windows Identity.   UPDATE: Okay we have started to get some feedback from CSS on AD related issues that have caused this failure. Here is the beginning of a list

  • “The given key was not present in the dictionary” – Apparently this error is caused by a change in defaults in Win2008 R2. Note that I have never seen this error in any trace associated with the delegated error which I am discussing in this post as of yet but apparently it can be the root cause even if you can’t see it. I will try to add more as I get more information. Here is a link to an additional thread on this issue specifically relating to SP2010.

     

    The following is beta info and needs to be updated.

    In the case of Windows Authentication, to perform the lookup, Excel Services uses the “Geneva to Windows Token Service” (GTS) provided by SharePoint. GTS takes the claims token of the caller and translates it to the Windows identity of the caller (the underlying Windows API that is uses for this is S4U (see here: xx). Unfortunately S4U does have its restrictions, and those restrictions are the heart of the “Cannot Delegate” error message that we are seeing. GTS requires:

  • A domain controller must be available to validate the logon. GTS cannot use cached credentials. It has to validate the login token on every connection. This obviously has performance implications, but fortunately it isn’t on very query; but just when the connection is established. This is easy to see (and it was the way that I generated the error message box above) –> just unplug the network from your laptop. You see that you can use SharePoint and Excel Services for everything using cached credentials until you go to Excel Services and try to connect to any data source (PowerPivot included) using Windows authentication.
  • The server must be a member of the same domain as the caller; or there must be a two-way trust relationship between the domains. This means that a common Windows 2000 domain architecture cannot be used by GTS.
  • The caller cannot be a local machine account. GTS only understands how to talk to domain controllers.
  • The account being used as the Excel Services service account must have AD rights to be able to query the object. One place where we know this restriction comes into play is if you have configured your domain controller to have a subgroup under “Users”, e.g. “Service Accounts”, which is a separate AD group that derives from “Users” –> but I am sure that there are more. AD rights for service accounts is a common problem across all of SharePoint.

    Denny Says to check this as well

  • Ensure that the services like “SQL Browser” service is running under LOCAL SERVICE account.  If the domain controller is on the same box as your server, then you may need to have the browser running under the same account as your PowerPivot service accounts (i.e.. Analysis Services)
  • Open up the firewall ports for Excel Services – which is 32843.  Often the connections failing due to data refresh are due to the firewall inadvertently being closed (or not opened).
  • Allow access within the firewall for the %Program Files%\\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin\msmdsrv.exe. For all of you Analysis Services folks, you know its the Analysis Services engine.
  • Note, even at this point, you may not be able to get SSMS to connect to the Analysis Services service to determine if the database (extracted from the workbook) was ever attached to the Analysis Services (IMBI) instance.  To do this, make sure you open ports 2382 and 2383.
  • Advertisements