Share via

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

  • Article

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.

Comments

  • Anonymous
    April 23, 2012
    Is it for PowerPivot v1 for SQL Server 2008 R2 or for PowerPivot v2 for SQL Server 2012?

  • Anonymous
    April 24, 2012
    This is for both SQL Server 2008 R2 Analysis Services PowerPivot and SQL Server 2012 Analysis Services PowerPivot

  • Anonymous
    October 05, 2012
    Does this need to be changed on each server in the farm or just on the web front end and app servers running powerpivot.

  • Anonymous
    October 17, 2012
    You need to update just the Web front ends as that is where the Redictor service is running.

  • Anonymous
    May 30, 2013
    How would it work if you have one server setup and NTLM authentication setup. It still doens't work any ideas about that configuration?

  • Anonymous
    May 31, 2013
    We are experiencing the same issue on SharePoint 2013 with sql 2013, and cannot find the items in the web config file, any thoughts?

  • Anonymous
    July 08, 2013
    This fix works great when connecting over http.  However when connecting over https it fails.  It is as if the RedirectorSecureBinding isn't being used.  Any suggestions as I am at a loss?

  • Anonymous
    July 31, 2013
    Karim, If you're using NTLM authentication, then you are not going to be able to connect in a scenario where the connection involves multiple hops. In multi-hop scenarios, Kerberos (Negotiate) is an absolute must.

  • Anonymous
    October 02, 2013
    is it possible to instead of having the redirector service to point directly to the Analysis Services PowerPivot instance of the powerpivot.

  • Anonymous
    April 16, 2014
    the web.config file structure of PowerPivot 2013 is different. How to change that?

  • Anonymous
    September 24, 2015
    Default location for the  Redirector.svc's web.config file is: %SystemDrive%Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14ISAPIPowerPivot instead of the posted: %SystemDrive%program filescommon filesweb service extensions14ISAPIpowerpivot