Edit

Share via

Error message when you use a PowerPivot for Excel workbook as a data source in SQL Server Analysis Services

  • Article
  • 03/07/2023

This article helps you resolve the problem that occurs when you try to use a PowerPivot for Excel workbook as a data source in SQL Server Analysis Services.

Original product version:   SQL Server
Original KB number:   2607106

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