Several new SharePoint 2010 configuration issues will impacting some PowerPivot sites and I wanted to share them with you. These restrictions are with Excel Services and have to do with the way that Windows authentication is handled, i.e. you have set the Excel Services authentication set to “Windows”, not using Secure Store or “None”. This impacts PowerPivot because Excel Services treats PowerPivot as a data source. The restrictions are not limited to just PowerPivot – they apply across the board for all Excel Services data sources.
First, Excel Services requires that a domain controller be available for data access when using Windows authentication for a connection (see my earlier posting, http://powerpivotgeek.com/2009/11/06/taking-your-server-off-the-network/). Interestingly it isn’t Excel Services specifically that has this requirement, rather it is the SharePoint infrastructure component called the Geneva Token Service (GTS) that Excel Services uses requires access to the domain controller for a S4U logon to impersonate the Windows user. S4U cannot use cached credentials.
The second restriction is also a consequence of GTS. GTS requires that all domains have two-way trust relationships in order to perform its logons. A common old-style domain configuration is to have a single dedicated “account” domain (where users live) – and then multiple “resource” domains (where your servers, printers and other resources live). Typically the resource domains trust the account domain; but the account domain does not trust the resource domains (a so-called ‘one-way trust’). Service accounts live in the resource domains and thus do not have account-level access. This is a good thing. Many of the Microsoft lab domains work this way. Unfortunately if you install SharePoint in such an environment, you will find that Excel Services returns an error: “The data connection uses Windows Authentication and Excel Services is unable to delegate user credentials.“ SharePoint requires a two-way trust between domains if your machine lives in one domain and your users live in another.
Hi, can you tell me where I could find out how to set up the SharePoint two-way trust between domains? I’m not sure how to do this.
Thank you,
Croix
You have to go to your domain administrators to do this. It isn’t a “SharePoint” thing; it is a domain thing. If have one of the domain “forests” then the trust relationships are part of the forest structure. If you are on one of the old NT4-style domains, then there is a property page in your domain administration to establish additional trust relationships. The idea is that you go to one domain and indicate that you “trust” the other domain; and then go to the other domain and say that you “trust” the first domain. Typically this is a very static relationship that is setup for lots of other reasons — and SharePoint just sits upon that.
We are currently testing SharePoint 2010 is our environment and have deployed the PowerPivot for SharePoint solution to our farm. It seems that everything is work fine. The PP modified excel files uploaded open up in the PP gallery but when you try to do a query against them that works fine when opening the same file in excel we get this error.
Unable to refresh data for a data connection in the workbook.
Try again or contact your system administrator. The following connections failed to refresh:
PowerPivot Data
The SharePoint servers reside on a Windows 2003 Active Directory environment.
Any help would be appreciated.
Hi Simon:
Are you sure it is working? When you bring up a workbook, unless the workbook has been set to refresh the data on open, the initial viewing of the workbook is done using the pivot cache that is included in the workbook — you won’t be actually connecting to the embedded PowerPivot data. To do that you need to either refresh the data connection or (easier) click on a slicer. Then is when you actually exercise PowerPivot. Until then it is just the pivot cache (which is part of Excel Services; not PowerPivot).
Since you are getting an error on refresh the data connection, you need to look in the ULS to see what is happening under the covers. However, as a initial shot-in-the-dark, might this be the problem? It is a common initial error with Excel Services. See:
http://powerpivotgeek.com/2010/04/02/help-c2wts-has-fallen-and-it-cannot-get-up/
Hope that helps.
_-_-_ Dave
Hi,
The reply to Simon ( we work together ) is a good start but we still seem to have issues. ULS is showing no errors.
The issue. When we click a slicer we used to get the error Simon posted. No we get authentication errors (depending on what we configured in the excel PP doc.
The problems we are having is authencation problems. We have tried Windows Authentication (no joy), SSS with Security Token Service support (no joy), and None (again no joy). We have configured the Excel Trusted file locations. There seems to be a piece missing and we just can seem to find it.
Thanks for the help.
R/Scuba Steve
Hi Steve:
We need more info. It is very strange that you are not getting errors in the ULS. Any authentication failure will get ULS entries generated.
Let’s take a step back. First, what kind of installation are you doing? “New Server” or “Existing farm”??
_-_-_ Dave
Our topology is as follows:
1 Win 2k8 R2 Server with SQL 2008 R2 which hosts the Foundation DB
1 Win 2k8 R2 Server with SP 2010 installed which acts as the front end and hosts all services except Search & PowerPivot
1 Win 2k8 R2 Server with SP 2010 installed which acts as the Searching Services for SP
1 Win 2k8 R2 Server with SP 2010 installed which hosts PowerPivot Services which was added to an existing farm.
Steve and Simon:
Ahhh… Then it is likely that your installation is not complete. Did you follow all of the existing farm instructions outlined in Books-Online? If this is this the first server you are installing PowerPivot? If so, use: http://msdn.microsoft.com/en-us/library/ee210616.aspx Is this the 2nd or later server you are installing PowerPivot? If so, use: http://msdn.microsoft.com/en-us/library/ee210610.aspx
Hope that helps.
_-_-_ Dave
Hi,
I have the same case like Simon and Steve.
I have two sharepoint server (front-end and back-end) and one sql server.
Did you found a solution for that?
Thank you.
Hi Niki:
I am sorry. Could you explain more? I pointed Steve and Simon to the docs to ensure that they have a complete and fully deployed solution. Did you do that and still you have problems? If so, I need more info.
_-_-_ Dave