shutterstock_184473665.jpg

Summit 7 Team Blogs

Resolving PowerPivot Kerberos / Permission Error

Error: Unable to connect to data source. Reason: Access denied. You either made a mistake typing in your User ID and/or Password, or you do not have permission to access the database server

In Tableu (or Excel), when you try and open an XLSX as a datasource using the whole URL, you get a permissions error. It ends up it’s simply that the web.config for the PowerPivot ISAPI is set to use NTLM and not Kerberos…we tried and tried to find the root of the problem seen below:

Enlarge

 

We had Kerberos installed and verified, used Fiddler to validate Kerberos on the client in conjunction with kerbtray.exe, verified and re-verified permission on libraries and the service application - all to no avail. Microsoft and Tableu told us it will work with workbook URL - I just felt like beating my head on the desk. Arghhh! But, buried down in the ISAPI web.config for PowerPivot, there are 6 places you have to set AuthN to Kerberos…no KB article or the like – so hopefully you stumble on this and save your forehead.

MSDN Blog (Thanks John!!)

http://blogs.msdn.com/b/johndesch/archive/2012/04/23/using-powerpivot-workbooks-from-a-mid-tier-server-configured-for-kerberos-authentication.aspx

From Social:

http://social.msdn.microsoft.com/Forums/en-AU/sqlkjpowerpointforsharepoint/thread/4bd65a54-85d9-400f-a576-6eb3a7e016f3

From John’s Blog, (go there for the full text):

 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

<binding name="RedirectorBinding"> tag and the <binding name="RedirectorSecurBinding"> tags and change the authenticationScheme value from "Ntlm" to "Negotiate", 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>

Easy fix once you know the solution :-)

Ben

Special thanks to Brian Laws at CCBCC for helping find this problem!

 

Disclaimer
The sample scripts are not supported under any Summit 7 Systems standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Summit 7 Systems further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Summit 7 Systems, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Summit 7 Systems has been advised of the possibility of such damages.

SHARE THIS STORY | |
About Ben Curry