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:

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 filescommon filesweb service extensions14ISAPIpowerpivot. Open the web.config file with a text editor and locate the <binding name=”RedirectorBinding”> tag and the <binding name=”RedirectorSecurBinding”> tags and change the authenticationScheme value from “Ntlm” to “Negotiage”, 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 Lawes at CCBCC for helping find this problem!