You are currently offline, waiting for your internet to reconnect

Error message when you use a PowerPivot for Excel workbook as a data source in SQL Server 2008 R2 Analysis Services: "HTTP Error 401"

SYMPTOMS
Consider the following scenario:
  • You configure Microsoft PowerPivot for Excel on a middle-tier server.
  • You configure the server to use Kerberos authentication and then connect to the server.
  • You try to use a PowerPivot for Excel workbook as a data source in Microsoft SQL Server 2008 R2 Analysis Services.
In this scenario, you may receive an error message that resembles the following:

HTTP Error 401.


CAUSE
This issue occurs because the custom bindings for the Redirector service are configured to use Microsoft NTLM authentication. Additionally, the custom bindings are configured not to negotiate.


RESOLUTION
To resolve this issue, enable Kerberos authentication for the Redirector service. To do this, follow these steps:
  1. Back up the Web.config file for the Redirector service.

    Note By default, the Web.config file is located in the following folder:
    %SystemDrive%\program files\common files\web service extensions\14\ISAPI\powerpivot
  2. Open the Web.config file for the Redirector service in Notepad.
  3. Locate the <binding name="RedirectorBinding"> tag, and then change the authenticationScheme value as follows:

    Original

    <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>
    Updated

    <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>
  4. Locate the <binding name="RedirectorSecureBinding"> tag, and then change the authenticationScheme value as follows:

    Original

    <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>
    Updated

    <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>
  5. On the File menu, click Save.
  6. Exit Notepad.
PowerPivot Mid-Tier Kerberos
Properties

Article ID: 2607106 - Last Review: 05/25/2012 21:50:00 - Revision: 2.0

  • Microsoft SQL Server 2008 R2 Analysis Services
  • kbsurveynew kbprb kbtshoot KB2607106
Feedback
t> tml>/c.microsoft.com/ms.js'" + "'><\/script>");