Cannot connect to PowerPivot document from Report Builder or
Excel.
Indicators
With PowerPivot documents residing in a PowerPivot library
on a SharePoint 2010 site, you cannot connect to any PowerPivot document as a
data source in SSRS 2008 R2 or Excel 2010 with the PowerPivot add in installed.
The PowerPivot document in SharePoint 2010 works fine and you have access to
it. When connecting from Report Builder, the connection test succeeds, but when trying to create a query in a
dataset, it throws the error: The remote server returned an error: (401)
Unauthorized. When connecting from Excel, Excel launches the
"Multidimensional Connection 10.0" dialog, which it usually does when
it can't connect to the source.
Research
Unable to connect to data
source ….The remote server returned an error: (401) Unauthorized.
This error seems to be coming
from IIS on the WFE. After researching this error a possible
reason for this error is the network configuration we have setup. We are using
Kerberose to authenticate over mutilple
endpoints. Both of the above clients use SharePoint PowerPivot and Excel
services to access the data residing in the excel document. The image below
illustrates the technology stack.
Figure 1 PowerPivot Life Cycle
As can be seen by the above illustration, multiple hops are
needed to get through the different service providers along the way.
Currently NTLM is the default configuration for the
PowerPivot Service. NTLM is not a recommend authentication mechanism as it has
been replaced by Kerberos. Our current environment is Kerberos enabled and is
accessible from our Farm servers.
Solution
NTLM authentication is failing to provide credentials all the way
through the technology stack. Analysis serverice is never provided the
nessesary identification. This is what is called a double- hop failure [1] Analysis
services must be used because it is what does that actual grunt work of
creating the data cube. After this cube has been created, it is then handed
back to the calling services and finally to the user.
To solve this problem, we
must change the authentication mechanism to reply on our Kerberos
environment. To do this we must change
the Power Pivot web.config http bindings and custom configs.[2]
Procedure
1. Navigate to “ C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\isapi\PowerPivot\web.config”
2. Within the basicHttpBinding node change the follow attribute:
Before:
After:
3. Within customBinding nodes change the httpsTrasport node’s attribute:
Before: authenticationScheme= “NTLM”
After: authenticationScheme= “Negotiate”
4. Repeat steps 2 & 3 for each web server.
