Using PowerPivot Workbooks from a Mid-Tier Server Configured for Kerberos Authentication

I'm not entirely certain how widely known, but it is quite possible to use a PowerPivot Workbook as a data source for other client applications. For example, both Excel and Reporting Services can use a PowerPivot workbook named AdventureWorks.xlsx that resides on a PowerPivot Mid-Tier server by specifying the data source as something like:

https://my_server/powerpivot/AdventureWorks.xlsx.

As a result, we would get a connection to a database with a name something like "AdventureWorks Sandboc 3de9890a-fa74-4fb9-be04-858b75366b68" and a cube named "Model". What really happens behind the scenes is that when the client application connects to the PowerPivot workbook, the connection is routed through a Redirector service to the Analysis Services server running in PowerPivot mode. The client then actually connects to the Analysis Services server. That's all well and good assuming that the connection to the Analysis Services server involves a single hop or that the Analysis Services PowerPivot server and SharePoint are not configured for Kerberos authentication.

Obviously if the connection involves a double hop, as would be the case in a client workstation (machine A) connecting to a Reporting Services server (machine B) that uses a PowerPivot Workbook on a PowerPivot Mid-Tier Server (machine c), is going to require Kerberos authentication. Likewise, if the PowerPivot mid-tier server is configured for Kerberos authentication then it is going to expect a Kerberos ticket as part of the authentication process. In either of these scenarios, out of the box, the likely result is going to be an authentication failure with error code 401 being returned from IIS.

Remember that when the data source is the PowerPivot Mid-Tier hosted PowerPivot workbook that the connection goes through a Redirector service and is ultimately routed to the Analysis Services PowerPivot instance. The 401 authentication failure error will be returned because the out-of-the-box customBindings for the Redirector service are configured to use "Ntlm" authentication which results in an Ntlm token rather than a kerberos token being used for authentication. In order to access the data source in this scenario, it is necessary to modify the web.config file associated with the Redirector.svc. To enable use of Kerberos authentication, it is necessary to change both of the CubsomBindings for the Redirector.svc service.

Before going further, I'll point out that enabling use of Kerberos authentication necessitates manually editing the web.config file that is associated with the Redirector.svc. Before editing the web.config file, it would be an extremely good idea to create a backup of the file and store it in a separate directory so that it can be restored in the event that the web.config file becomes corrupted while being edited.

The default location for the file is %SystemDrive%\program files\common files\web service extensions\14\ISAPI\powerpivot. Open the web.config file with a text editor and locate the <binding name="RedirectorBinding"> tag and the <binding name="RedirectorSecurBinding"> tags and change the authenticationScheme value from "Ntlm" to "Negotiage", as shown below:

<binding name="RedirectorBinding">
    <webMessageEncoding webContentTypeMapperType="Microsoft.AnalysisServices.SharePoint.Integration.Redirector.RawContentTypeMapper, Microsoft.AnalysisServices.SharePoint.Integration" />
    <httpTransport manualAddressing="true" authenticationScheme="Ntlm" transferMode="Streamed" maxReceivedMessageSize="9223372036854775807"/>
</binding>
<binding name="RedirectorSecureBinding">
    <webMessageEncoding webContentTypeMapperType="Microsoft.AnalysisServices.SharePoint.Integration.Redirector.RawContentTypeMapper, Microsoft.AnalysisServices.SharePoint.Integration" />
    <httpsTransport manualAddressing="true" authenticationScheme="Ntlm" transferMode="Streamed" maxReceivedMessageSize="9223372036854775807"/>
</binding>

 to

<binding name="RedirectorBinding">
    <webMessageEncoding webContentTypeMapperType="Microsoft.AnalysisServices.SharePoint.Integration.Redirector.RawContentTypeMapper, Microsoft.AnalysisServices.SharePoint.Integration" />
    <httpTransport manualAddressing="true" authenticationScheme="Negotiate" transferMode="Streamed" maxReceivedMessageSize="9223372036854775807"/>
</binding>
<binding name="RedirectorSecureBinding">
    <webMessageEncoding webContentTypeMapperType="Microsoft.AnalysisServices.SharePoint.Integration.Redirector.RawContentTypeMapper, Microsoft.AnalysisServices.SharePoint.Integration" />
    <httpsTransport manualAddressing="true" authenticationScheme="Negotiate" transferMode="Streamed" maxReceivedMessageSize="9223372036854775807"/>
</binding>

That should resolve the 401 error when attempting to use a PowerPivot Workbook, hosted on the PowerPivot Mid-Tier server, as a data source.