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

Article translations Article translations
Article ID: 2607106 - View products that this article applies to.
Expand all | Collapse all

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.

Properties

Article ID: 2607106 - Last Review: May 25, 2012 - Revision: 2.0
APPLIES TO
  • Microsoft SQL Server 2008 R2 Analysis Services
Keywords: 
kbsurveynew kbprb kbtshoot KB2607106

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com