Unauthorized when using SharePoint PowerPivot document from Report Builder or Excel?

Here is the scenario: You’ve got WFE Server which is also an app server with PowerPivot set up, we’ll call it SPWFE. You also have the database server which is a SharePoint database, let’s call it SPDB. That same server hosts reporting services. Lastly you’ve got a 3rd database server hosting some data, let’s call it the LOBDataDB. So you have a PowerPivot workbook sitting in your SPWFE PowerPivot library, the data retrieved being retrieved from LOBDataDB. When you execute the workbook right from the PowerPivot library – the workbook renders no problem. When you use this workbook as a datasource to build the report in the ReportBuilder you get

The remote server returned an error: (401) Unauthorized

Here are the ground rules … you have to configure Kerberos authentication on:
-SPWFE, since it will be accessing data and passing credentials to other machines
-SPDB, well if SPWFE uses Kerberos, the DB server has to use it too
-LOBDataDB, needs to have Kerberos configured because SPWFE will be accessing information from it

For detailed instructions on how to configure Kerberos in each of the scenarios, search for this document which explains everything in details: Configure Kerberos Authentication for SharePoint 2010 Products

The solution:

So considering Kerberos is configured, you still need to ensure that PowerPivot uses Kerberos for it’s configurations.

On the SPWFE machine, access the following default folder for default PowerPivot installation:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\PowerPivot

Find the web.config, and locate the four nodes:

<transport clientCredentialType="Ntlm"/>

and replace the clientCredentialType attribute value to:

<transport clientCredentialType="Ntlm"/>

Now, under the customBinding node, locate the

<httpTransport manualAddressing="true"
authenticationScheme="Negotiate" transferMode="Streamed"
maxReceivedMessageSize="9223372036854775807"/>

and ensure the authenticationScheme is set to Negotiate as shown above.
Make this change for both custom bindings.

Now when you access the report server query builder and pass the PowerPivot datasheet as a data source, you should have no problem adding an entities.

Enjoy!