Excel Services & PowerPivot for SharePoint

PowerView - An error has occurred while loading the model for the item or data source.

2013-05-24T19:56:00Z

Even though this is the PowerPivot and Excel Services blog, I felt that adding a blog on this common PowerView error was worthy of a post.

Many times I have seen customers with the error:

"Power View

An error occurred while loading the model for the item or data source '<filename.extension'>. Verify that the connection information is correct and that you have permissions to access the data source."

If you expand the "Show details", you will see Claims errors, but what does that mean?

<detail><ErrorCode xmlns="http://www.microsoft.com/sql/reportingservices">rsCannotRetrieveModel</ErrorCode><HttpStatus xmlns="http://www.microsoft.com/sql/reportingservices">400</HttpStatus><Message xmlns="http://www.microsoft.com/sql/reportingservices">An error occurred while loading the model for the item or data source 'http://microsoft/sites/bidemo/PowerPivot/Tabular.bism'. Verify that the connection information is correct and that you have permissions to access the data source.</Message><HelpLink xmlns="http://www.microsoft.com/sql/reportingservices">http://go.microsoft.com/fwlink/? inkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsCannotRetrieveModel&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=11.0.3349.0</HelpLink><ProductName xmlns="http://www.microsoft.com/sql/reportingservices">Microsoft
SQL Server Reporting Services</ProductName><ProductVersion xmlns="http://www.microsoft.com/sql/reportingservices">11.0.3349.0</ProductVersion><ProductLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">127</ProductLocaleId><OperatingSystem xmlns="http://www.microsoft.com/sql/reportingservices">OsIndependent</OperatingSystem><CountryLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">1033</CountryLocaleId><MoreInformation xmlns="http://www.microsoft.com/sql/reportingservices"><Source>ReportingServicesLibrary</Source><Message
msrs:ErrorCode="rsCannotRetrieveModel" msrs:HelpLink="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsCannotRetrieveModel&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=11.0.3349.0"
xmlns:msrs="http://www.microsoft.com/sql/reportingservices">An error occurred while loading the model for the item or data source 'http://microsoft/sites/bidemo/PowerPivot/Tabular.bism'. Verify that the connection information is correct and that you have permissions to access the data source.</Message><MoreInformation><Source>Microsoft.ReportingServices.ProcessingCore</Source><Message msrs:ErrorCode="rsErrorImpersonatingUser" msrs:HelpLink=http://go.microsoft.com/fwlink/? inkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsErrorImpersonatingUser&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=11.0.3349.0 xmlns:msrs="http://www.microsoft.com/sql/reportingservices">Cannot
impersonate user for data source 'TemporaryDataSource'.</Message><MoreInformation><Source>Microsoft.ReportingServices.ServiceRuntime</Source><Message msrs:ErrorCode= "rsClaimsToWindowsTokenError" msrs:HelpLink=http://go.microsoft.com/fwlink/? inkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsClaimsToWindowsTokenError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=11.0.3349.0 xmlns:msrs= "http://www.microsoft.com/sql/reportingservices">Cannot convert claims identity to windows token. </Message><MoreInformation> <Source>Microsoft.SharePoint</Source><Message>Could not retrieve a valid Windows identity.</Message><MoreInformation><Source>mscorlib</Source><Message>Access is enied.</Message></MoreInformation> </MoreInformation></MoreInformation></MoreInformation></MoreInformation><Warnings xmlns=http://www.microsoft.com/sql/reportingservices /></detail>

If you want to analyze this more you can use Rodney Viana's tool to analyze the C2WTS:

Troubleshooting Claims to Windows NT Token Service (c2WTS) in SharePoint 2010 may be difficult if you don’t know where to start

http://blogs.msdn.com/b/rodneyviana/archive/2011/07/19/troubleshooting-claims-to-windows-nt-token-service-c2wts-in-sharepoint-2010-may-be-difficult-if-you-don-t-know-where-to-start.aspx

I ran Rodney's tool and saw:

Testing Service c2WTS
+- Service c2WTS found
+- Service c2WTS is running
+- Path of service: C:\Program Files\Windows Identity Foundation\v3.5\c2wtshost.exe
+- Config File: C:\Program Files\Windows Identity Foundation\v3.5\c2wtshost.exe.config
+- Service Logon: SYSTEM\NT AUTHORITY
----- start of config file ----
<?xml version="1.0"?>
<configuration>
<configSections>
    <section name="windowsTokenService" type="Microsoft.IdentityModel.WindowsTokenService.Configuration.WindowsTokenServiceSection, Microsoft.IdentityModel.WindowsTokenService, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
</configSections>
<startup>
    <supportedRuntime version="v4.0" />
    <supportedRuntime version="v2.0.50727" />
</startup>
<windowsTokenService>
    
    <allowedCallers>
      <clear />
      <add value="WSS_WPG" />
    </allowedCallers>
</windowsTokenService>
</configuration>
----- end of config file ----
Retrieving security groups/users allowed to use the service from config file
+- WSS_WPG
Trying to login .........
Using current Windows Credentials
***** c2WTS could not provide a valid Windows Token. Reason: WTS0003: The caller is not authorized to access the service.

Server stack trace:
   at System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood(Message reply, MessageFault fault, String action, MessageVersion version, FaultConverter faultConverter)
   at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)
   at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
   at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at Microsoft.IdentityModel.WindowsTokenService.S4UClient.IS4UService_dup.UpnLogon(String upn, Int32 pid)
   at Microsoft.IdentityModel.WindowsTokenService.S4UClient.<>c__DisplayClass1.<UpnLogon>b__0(IS4UService_dup channel)
   at Microsoft.IdentityModel.WindowsTokenService.S4UClient.CallService(Func`2 contractOperation)
   at c2WTSTest.Form1.button2_Click(Object sender, EventArgs e)

Now Verifying if user msft\test has rights on c2WTS
+- User msft\test has no access to the service
*** Analysis Complete ***

The first highlighted item tells me the account that was running the C2WTS: SYSTEM\NT AUTHORITY

The second highlighted item gives me the error: c2WTS could not provide a valid Windows Token. Reason: WTS0003: The caller is not authorized to access the service.

The error tells me that the account running the Claims to Windows Token Service lacks permissions/security.

Steps to resolve the issue.

I changed the Claims to Windows Token Service to a managed account and then applied the below changes (Note: you can attempt to apply the below changes to the existing account running the C2WTS):

a. Add the service account to the local Administrators Groups.

b. In local security policy (secpol.msc) under user rights assignment give the service account the following permissions:

i. Act as part of the operating system

ii. Impersonate a client after authentication

iii. Log on as a service

Restart IIS

After making this change the error went away.

PowerPivot for SharePoint 2010 & SQL 2012 - Refresh in browser fails due to multiple "Process" Data Models in Analysis Services.

2013-05-23T16:03:00Z

When opening a workbook after a Schedule Data Refresh, the workbook does not open in Excel Services:

This is because after a Scheduled Data Refresh, the “Refresh data when opening the file” is checked. This is by design, because after a Scheduled Data Refresh, the workbook’s Data Model is updated with new information and the workbook is re-published to the SharePoint Library. To ensure that you are getting fresh data in the browser, the “Refresh data when opening the file” is checked. (I will get to the reason why it is spinning later under the Cause section).

If we uncheck “Refresh data when opening the file” and open the workbook in the browser, the workbook opens, but if we click on a slicer (a workbook refresh is triggered), the error “Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The
following connections failed to refresh: PowerPivot Data” is thrown:

This is caused by a Bug. When the Scheduled Data Refresh runs, a Process cube is created under the SharePoint Server running PowerPivot > Analysis Services > PowerPivot Instance > Databases. The Process cube should be deleted, but it is not. Therefore many (not sure I can call them “duplicate” since they have a unique GUID so I will use the term “multiple”) “multiple” Data Models are created and not deleted. When the workbook is refreshed, the workbook looks for the Data Model associated with that workbook under the SharePoint Server running PowerPivot > Analysis Services > PowerPivot Instance > Databases. As you can see there are many “multiple” Data Models and the workbook refresh either hangs (spinning when using “Refresh data when opening the file”) or fails when clicking on a slicer (error “Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh: PowerPivot Data).

To resolve this, you will need to upgrade all servers running PowerPivot to SQL SP1 (11.0.3000.0) you most likely are on an earlier version (RTM - 11.0.2100.60) and apply the Cumulative Update 4.

Microsoft® SQL Server® 2012 Service Pack 1 (SP1)

http://www.microsoft.com/en-us/download/details.aspx?id=35575

Cumulative update package 4 for SQL Server 2012 SP1
http://support.microsoft.com/kb/2833645/en-us

Upsizing PowerPivot 2013 Workbooks to SSAS for Knowledge Workers

2013-05-23T15:42:00Z

Much of the existing content for converting a PowerPivot workbook to a SQL Server Analysis Services (SSAS) tabular instance assumes that the reader is a developer. This post details the process of upsizing a PowerPivot workbook to SSAS from the point of view of a knowledge worker and or a SharePoint administrator.

This post assumes you are using PowerPivot as a data source, so no UI is present in the workbook hosting the PowerPivot model. The display of the data would happen from another workbook that connects to the PowerPivot workbook as a data source or from some other reporting tool like PowerView or Excel Services.

What is upsizing PowerPivot (aka convert PowerPivot to tabular model, aka convert PowerPivot to SSAS):

Upsizing PowerPivot is simply moving the solution from a SharePoint centric storage mode to a SSAS centric storage mode. The end result is that the PowerPivot model and data will not be stored in SharePoint.
PowerPivot stores models in Excel Workbooks when using a SharePoint centric mode and stores the models in native SSAS files when using SSAS storage mode.

The key takeaway is that the same query engine is used regardless of where the model is stored, Excel client, SharePoint or SSAS.

Why upsize a PowerPivot workbook:

You may want to consider upsizing your PowerPivot workbook after it exceeds 100MB in size for the following reasons.

Stability

SharePoint was not optimized to host very large files and it has a hard limit of 2GB for a single file.
You will find that SharePoint features may fail or behave oddly when you are using very large files, this is also a burden on the SharePoint system that could adversely affect other users.

Performance

When a PowerPivot workbook is stored in SharePoint the PowerPivot model and all of the data must be streamed from the SharePoint database to a special instance of SSAS to build the database.
If your users are experiencing intermittent poor performance this could be because the cache time has expired and the backend SSAS database needs to be rebuilt.
In SharePoint 2013 the default behavior of Excel Services data \ “Refresh all connections” is to have the PowerPivot data completely reload itself from source data. For large workbooks this could cause poor user experience.

If you want to have a PowerPivot workbook larger than 2GB your only options are to upsize or only use it in the Excel client (64bit), SharePoint only support files 2GB or smaller.

Issues to consider before upsizing:

Your administrators will need to install and configure a tabular instance of SSAS in your network.
You will need to install “SQL Server Data Tools” on your system.
SSAS does not use the SharePoint security system, if you want to restrict who can access the workbook data you will need to configure it on the SSAS database.

This consists of assigning Windows users and or group’s access to the database.
Can be done with “SQL Server Data Tools”

If you were not using the workbook as a data source you will need to create a separate workbook to view the data. This would be in cases where both the data and the pivot tables that view the data are stored in the same workbook.

Steps to upsize a PowerPivot workbook:

Have your administrator setup an tabular instance of SSAS in your network

Use the SQL 2012 media, pick Analysis Service and Tabular mode
http://msdn.microsoft.com/en-us/library/hh231722.aspx
Make sure to have the SSAS service run under a domain account, it will need to access the file share made in step 2
You must install SQL 2012 SP1 if you are using Excel 2013 workbook as the source.
i. http://www.microsoft.com/en-us/download/details.aspx?id=35575
The “SQL Server Browser” service must be enabled and running

Create a file share somewhere where both the service account used in step 1.c and the users building the model have full control.
On the client PC that will author the model install the SQL Server Data Tools (formerly called BIDS) from the SQL Server 2012 media.
- Run setup, pick “Installation” then “New SQL Server stand-alone installation or add features to an existing installation”
- On the “Setup Role” screen pick “SQL Server Feature Installation”
- On the “Feature Selection” screen pick “SQL Server Data Tools” (see image below)
Finish the installation wizard.
Install SQL Server 2012 SP1 on the client computer, this allows for support of Office 2013 workbooks.

http://www.microsoft.com/en-us/download/details.aspx?id=35575

Copy the workbook you want to upsize to the fileshare created in step 2
Start the “SQL Server Data Tools” program
Pick “Business Intelligence Settings” in the “Choose Default Environment Settings” popup (see below)
Create a new Project. Menu: File \ New \ Project
Within the “Business Intelligence \ Analysis Service” template pick “Import from PowerPivot” project type.
Enter a good project name, this project will hold the model and build the database on the SSAS server.
Enter the tabular mode SSAS server\instance name in the next popup window (see below)
Test the connection to ensure the SSAS Server was entered correctly and is functioning properly.
Click “Yes” to the warning below, it is simply asking that you trust where the PowerPivot workbook is getting data from. And informing you that the data will not be imported, this is OK because the server will fetch data from the data sources defined in the PowerPivot model. If you have data in the model that is from a linked sheet that will need to be copied by hand into this solution via the “Past Append” function.
Select the workbook you want to upsize in the file open dialog box that pops up next. This file should reside on the file share created in step 2 and referenced in step 6.
If all goes well, several progress bars should pass by in the lower right corner of the screen and you should end up with an open Model.bim file like screen shot below:
Click the Existing Connections ( ) toolbar button and review any existing connection(s) included in the model (see image below).

The domain account used in step 1.c should have read access to all of the data sources used.

Deploy the project to the server via the menu item: Build \ Deploy

Should get Success message as shown below

At this point you have a working data source that you can access from new workbooks and other data source consumers. In Excel you just treat it like any other Analysis Services data source.

http://office.microsoft.com/en-us/excel-help/connect-to-or-import-data-from-sql-server-analysis-services-HP010342296.aspx

All is good, except that the new SSAS database is not getting refreshed with the latest data, it is a static snapshot of the data at the time you deployed it to the server. You can setup an automated processing schedule by: (the following steps can be accomplished using the “SQL Server Management Studio” tool):

Ensure that the “SQL Server Agent” service is running on the SSAS server
Ensure that the account running the “SQL Server Agent” service has permissions to process the new SSAS tabular database. You can create a new role for the database that has “Process” permissions and assign the agent account to that.
Create a new SQL Server Agent job, with one step.
The step should be setup with Type = SQL Server Analysis Services Command, Run as = SQL Server Agent Service Account, Server = Name of you SSAS Tabular server (see image below)
Command should be:
<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Type>ProcessDefault</Type> <Object> <DatabaseID>TabularProject1</DatabaseID> </Object></Process>

Where DatabaseID should equal the name of the SSAS database you just created.
Schedule the job to run daily or however often you want changes made in the source data to reflect in the SSAS tabular database.
FYI, New data is fetched from the source data sources when the SSAS database is processed.

Congratulations you have now upsized your PowerPivot model to a powerful full featured SSAS database.

Excel Services - The workbook cannot be opened.

2013-05-21T17:35:00Z

When opening an Excel workbook (.xlsx) in the browser the error "The workbook cannot be opened" is thrown:

This is caused by the account running Excel Services not having proper permissions to the Content Database. When you upload an Excel workbook to a SharePoint library, the workbook is stored as blobs in the Content Database. When you choose to open this in Excel Services, the account running Excel Services needs to retrieve and reassemble the workbook. If the account running Excel Services lacks proper permissions, "The workbook cannot be opened".

We have a KB on how to resolve this issue:

The Excel Services Application for SharePoint 2010 does not load or display workbooks

http://support.microsoft.com/kb/981293

In this blog, I will step you through this KB; where to collect data and explain what the two below SharePoint Management Shell Commands do:

$w = Get-SPWebApplication –Identity <URL of the Web application>
$w.GrantAccessToProcessIdentity("<insert service account>")

You need to find the <URL of the Web application>. To do this:

1. Browse to the report library where the failing workbook is located and make note of the URL.

2. Central Administration > Application Management > Manage web applications compare the URL to the list of Web Apps. Find the correct Web App URL and add it to “URL of the web application”.

You now have the first command:

$w = Get-SPWebApplication –Identity http://tschauer

3. Central Administration > Security > Configure Service Accounts > from the first dropdown, select the application pool running "Excel Services Application" and add that account where is says “<insert service account>”.

You now have the second command:

$w.GrantAccessToProcessIdentity("Devtest\Excel_Services_Account")

So now you can run these commands by following the below steps:

1. Click Start, click All Programs.
2. Click Microsoft SharePoint 2010 Products.
3. Click SharePoint 2010 Management Shell.
4. At the Windows PowerShell command prompt (PS C:\>), type the following command, and then press ENTER:

$w = Get-SPWebApplication –Identity http://tschauer
$w.GrantAccessToProcessIdentity("Devtest\Excel_Services_Account")

When you run these commands you are:

1. Adding the account running Excel Services as a User to the Content Database (Security > Users).

2. Giving the account running Excel Services the SPDataAccess Membership (Account > Membership)

3. Giving the account running Excel Services Full Control to the Web Application (Via "Policy for Web Application")

PowerPivot Scheduled Data Refresh - Call to Excel Services Returned and error.

2013-05-08T19:12:00Z

I have encountered the error "Call to Excel Services returned an error" when performing a Scheduled Data Refresh.

In several cases, this has been because the PowerPivot connection is using the "SQL Server Native Client" Provider. We recommend that you use our enterprise Provider "Microsoft OLE DB Provider for SQL Server". I assume the reason why many workbooks are using this is because these workbooks are created on a user's desktop machine. I assume the reason why this fails is because the server does not have this provider installed.

To check and see what provider you are using Open Excel > PowerPivot tab > Manage

Design > Existing Connections

Select the appropriate PowerPivot connection > Edit

Change the Provider from "SQL Server Native Client" to...

"Microsoft OLE DB Provider for SQL Server"

Important Note: Click "Test Connection" to ensure that the connection in the workbook is able to properly retrieve data. If is cannot retrieve data, then you will need to analyze the error it returns.

Error attempting to create new PowerPivot Gallery Document from PowerPivot gallery

2013-04-17T16:03:00Z

With SharePoint 2010, you might run into an issue when attempting to create a new PowerPivot Gallery Document from PowerPivot gallery, like this:

If you encounter this issue, you may then see the following to errors:

The reason for the error is that SharePoint is looking for the template .xlsx file in the wrong location.

To resolve the issue:

Navigate in SharePoint to your PowerPivot gallery
Click on the Library menu at the top (like below)
Click on the Open with Explorer icon (like below)
Move the Forms folder that is located there into the ReportGallery folder, or if the Forms folder does not exist, create a Forms folder in the ReportGallery folder (like below)

Now the path to the template.xlsx folder will be correct, and you shouldn’t get any errors when creating new PowerPivot Gallery Documents from PowerPivot gallery.

PowerPivot for SharePoint Manual Data Refresh Failing

2013-04-02T21:56:00Z

You might see a scenario where data refresh with PivotTables or slicers or Data > Refresh All Connections are not working, and you are seeing an error in the browser like this:

You may also find a corresponding error in the SharePoint logs like this one:

“MossHost.TryGetWindowsIdentity: Current identity is ClaimsIdentity. We need to get a WindowsIdentity”

With PowerPivot workbooks viewed in the browser via Excel Services, in order for PivotTables or slicers or Data > Refresh All Connections to work, the C2WTS is utilized. If the C2WTS is being run by a domain account (Microsoft recommended best practice), then that domain account needs a few certain permissions on each of the SharePoint servers where it is running:

The C2WTS domain account must be in the local Administrators group on the SharePoint servers where C2WTS is running
For the SharePoint servers where C2WTS is running, in the server’s local security policy, the C2WTS domain account needs these permissions:

Act as part of the operating system
Impersonate a client after authentication
Log on as a service

To access a server’s Local Security Policy, go to Start > Administrative Tools > Local Security Policy > Local Policies > User Rights Assignment

If you do add any of the permissions above for the account that is running the C2WTS, then you will need to restart the C2WTS on each of the SharePoint servers where it is running.

If the account that is running the C2WTS is LOCAL SYSTEM (not recommended), then no further permissions are required.

3 Things you may/may not know about PowerPivot-AS for SharePoint 2013

2013-04-02T20:24:00Z

The following applies to SharePoint 2013:

1. Restarting ECS will force a wipe and reload of PowerPivot databases.

2. Any service account needing to interact with the PowerPivot data model needs to be an Administrator on the PowerPivot AS instance. This is less obvious when you are in a client application but consider these scenarios:

a. PowerView client (in Excel) > SQL Reporting Services service application needs to be an admin

b. Performance Point > Performance Point Service application needs to an admin

3. This table shows backward compatibility for workbooks in SharePoint 2013 and what service application performs a scheduled data refresh. Interactivity refers to the user-level interactivity allowed with the PowerPivot data model. Only Excel 2013 workbooks allow for full permissions, which equates to full back-end connection-based data refresh in browser (which means writing/updating the data model in PowerPivot-AS):

* Note: Data refresh schedules for PowerPivot 2012-Excel 2010 workbooks, the PowerPivot System Service account requires modify rights to the workbook in SharePoint as well as admin rights on the PowerPivot-AS server.

PowerPivot Workbooks as a Data Source

2013-02-15T21:53:00Z

We have been seeing some issues with customers using PowerPivot workbooks as a source file in new Excel PowerPivot files. There are a variety of errors that can arise from this process, and this blog is intended to provide you with some troubleshooting steps to resolve the issue.

You may or may not be aware, but it is possible to use a PowerPivot workbook as a data source for other data applications. For instance, one can use Excel and connect to a PowerPivot file on a SharePoint site as a PowerPivot data source. There may be a file on SharePoint site named BIFinance.xlsx. When creating a new PowerPivot workbook, we could specify our data source as the following:

http://sharepointsite/PowerPivot Gallery/BIFinance.xlsx

Fig 1: Using PowerPivot workbook as a data source

In a nutshell, when the data source is the PowerPivot Mid-Tier hosted PowerPivot workbook, the connection goes through a Redirector service on the SharePoint server and is ultimately routed to the SQL Server Analysis Services PowerPivot instance.

For more information on how to use PowerPivot workbooks as a Data Source, please see the following video:

Using PowerPivot Workbooks as a Data Source
http://technet.microsoft.com/en-us/sqlserver/dn151361.aspx

A few sample errors that may arise when attempting to connect to the PowerPivot workbook as a data source:

The following system error occurred:
The following system error occurred: Invalid class string
Failed to connect to the server. Reason: No error message available, result code: DB_SEC_E_AUTH_FAILED(0x80040E4D)
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.

Possible scenarios that might cause this process to fail:

The SharePoint Web application is setup with Kerberos. If you are seeing the DB_SEC_E_AUTH_FAILED(0x80040E4D) error, then the web application is more than likely set up with Kerberos.

Please follow the steps in the following article to resolve the issue. You will need to modify the web.config file on the web front end servers.
http://blogs.msdn.com/b/johndesch/archive/2012/04/23/using-powerpivot-workbooks-from-a-mid-tier-server-configured-for-kerberos-authentication.aspx

The client machine making the call does not have updated Microsoft SQL Server Analysis Server OleDB drivers. Please ensure that the Analysis services (SQL_AS_OLEDB) drivers are up to date on the client machine: You may need to check with your SharePoint administrator to determine what version of PowerPivot is installed in your SharePoint environment.

If using SQL Server 2008 R2 PowerPivot:
http://www.microsoft.com/en-us/download/details.aspx?id=30440
If using SQL Server 2012 PowerPivot:
http://www.microsoft.com/en-us/download/details.aspx?id=35580

Make sure the SharePoint Web application where the workbook is stored does not have multiple bindings in IIS.

Open IIS on the SharePoint Web Front End servers and select the SharePoint web application and click on Bindings.
If there is more than one this will fail.
If your web application needs more than one binding, you may extend the web application in SharePoint. Please see the following article on how to extend the web application in SharePoint.

Extend a Web application (SharePoint Server 2010) - http://technet.microsoft.com/en-us/library/cc261698(v=office.14).aspx

The user that is making the connection needs to have limited read access the Root Web application.
If you see this error: "XML parsing failed at line 1, column 1: Incorrect document syntax." Please review the following article:

http://powerpivotgeek.com/2012/06/06/xml-parsing-failed-at-line-1-column-1-incorrect-document-syntax/

The steps above should resolve most of the errors that are seen when attempting to use a PowerPivot workbook as a data source within Excel PowerPivot.

Tools and Techniques: Troubleshooting Kerberos in Excel Services and PowerPivot for SharePoint

2013-02-08T22:32:00Z

I have troubleshot many Kerberos cases over the years and here are the best techniques and tools that I have used over the years.

1. Kerberos Event Logging (KB here):

Add the following registry value to each machine in the farm that receives Kerberos Traffic:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters

Registry Value: LogLevel
Value Type: REG_DWORD
Value Data: 1

To see the results, open Event Viewer > Windows Logs > System

You will now see Kerberos Errors in Event Viewer:

You will see a variety of errors such as:

KDC_ERR_S_PRINCIPAL_UNKNOWN: This means one of two things:

1. You are missing the listed SPN and need to add it to the correct account.

2. There is a Duplicate SPN effecting Kerberos Delegation, to analyze the Delegation use the tool listed below, DHCheck).

KDC_ERR_ETYPR_NOT_SUPP: This too means one of two things:

1. Delegation in SharePoint 2010 only supports Constrained Delegation, I have seen where one account is Constrained and another is not, this causes Delegation to fail and this error is thrown.

2. This is a bug with the Kerberos.dll (see my other blog).

2. DHCheck (DoubleHopCheck):

(Please visit the site to view this file)

This tool is great, it will inform you if the account is Trusted for Delegation, the SPNs Registered to the Account, and the SPNs the Account is Constrained too. Oh, and most importantly Duplicate SPNs.

A file named "results.txt" will be placed in the C:\temp\ folder, when you open it, it will look like the below:

Distinguished name..............: CN=Account01,OU=SharePoint_Servers,OU=SharePoint_Enterprise,OU=Domain Servers,DC=AD,DC=Microsoft,DC=com
Account type....................: Computer
User Account control............: 16781344(DEC) 1001020(HEX)
Account Trusted for delegation..: False
Account sensitive for delegation: False
Constrained delegation is enabled for:
MSOLAPSvc.3/Server01:Insance
MSOLAPSvc.3/Server01.FQDN:Insance
Registered Service Principal Names:
HTTP/Server01
HTTP/Server01.FQDN
Duplicate SPN found: HTTP/Server01
Account01,CN=Account01,OU=Users01,OU=IT,OU=ABC,OU=AdminUnits,DC=,DC=Microsoft,DC=com
Account02,CN=Account02,OU=Users02,OU=IT,OU=123,OU=AdminUnits,DC=MSFT,DC=Microsoft,DC=com

To use this tool:

1. Rename the attachment from dhcheck.txt to dhcheck.vbs and save it on the Application Server (root of C:).

2. Open a command line window and browse to the same directory as dhcheck.vbs (root of C:) enter the below information into the command line and press enter.

cscript dhcheck.vbs Account1 Account2 Account 3 > c:\temp\results.txt

*The accounts in Red will be the account running Excel Services, Claims to Windows Token Services, SSAS and/or SQL (any accounts you want to collect Delegation information on).