Error message when you use a PowerPivot for Excel workbook as a data source in SQL Server Analysis Services
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:
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
.Open the Web.config file for the Redirector service in Notepad.
Locate the
<binding name="RedirectorBinding">
tag, and then change theauthenticationScheme
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>
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>
On the File menu, click Save.
Exit Notepad.
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for