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 Sourcehttp://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:
Possible scenarios that might cause this process to fail:
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.
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):
6366.DHCheck.zip
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=comAccount type....................: ComputerUser Account control............: 16781344(DEC) 1001020(HEX)Account Trusted for delegation..: FalseAccount sensitive for delegation: FalseConstrained delegation is enabled for:MSOLAPSvc.3/Server01:InsanceMSOLAPSvc.3/Server01.FQDN:InsanceRegistered Service Principal Names:HTTP/Server01HTTP/Server01.FQDNDuplicate SPN found: HTTP/Server01Account01,CN=Account01,OU=Users01,OU=IT,OU=ABC,OU=AdminUnits,DC=,DC=Microsoft,DC=comAccount02,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).
I have worked with many customers who are seeing refresh failures in the Excel Web Access Web Parts in their TFS Dashboard.
In most cases, this is caused by not having a Secure Store Service ID set up for Team Foundation Server and that SSS ID has not been added to the Enterprise Application Definition. Follow the steps below to configure your environment.
Step One:
Set up a Secure Store Services ID. Here are the steps to create a Secure Store Services ID:
Central Administration > Manage Service Applications > Secure Store Service > New
Target Application Settings:Application ID: tfsDisplay Name: tfsContact E-mail: AdministratorTarget Application Type: GroupTarget Application Page URL: NoneNext
Field Name: Windows User NameField Type: Windows User NameMasked: No
Field Name: Windows PasswordField Type: Windows PasswordMasked: YesNext
Target Application Administrators: AdministratorMembers: example: “SecureStoreUsers” (This will be an AD group that you create. Many companies will create certain groups for certain resources. Example: One company may create an Application ID called "SSSAccounting" and have an AD group called "Accounting" that they make a member.)Next
Select the SSS ID "tfs" > Set Credentials:Windows Name: (this account needs to have access to the backend data).
Step Two: Add the Enterprise Application Definition:
Note: For all the workbooks to be updated, you need to wait about 30 minutes. I am not sure what Timer Job runs to update there workbooks. Here is the best documentation I could find.
Team Foundation Server Timer Job for SharePoint Products
Project portals may contain Excel workbooks that have a connection to the Team Foundation Server data stores. The administrator of a Team Foundation Server instance can change the reporting configuration for the instance. For example they could enable/disable reporting, change the location of the SQL Server Analysis Services cube or change the Enterprise Application Definition for sites within a given SharePoint Web application.
To keep these connections pointing to the correct location or to keep the Excel Services Authentication Settings matching the Enterprise Application Definition, a timer job for SharePoint Products runs on each SharePoint Web application that contains project portals. The timer job periodically scans the portal sites in the Web application and updates the workbook details if it finds that the connection or authentication details have changed.
If you are adding additional workbooks to a project portal and want their connections to be updated by the timer job then you should place them in the same document library as the Excel workbooks for Team Foundation Server. Note that the name of the connection in the workbook must be "TfsOlapReport" if it is to be updated by the timer job.
Customizing Team Foundation Server Project Portalshttp://msdn.microsoft.com/en-us/library/ff678492.aspx
I have had several cases where a customer will have Kerberos properly configured but refresh in the browser fails.
In the UI, the error will appear.
"Unable to get the required information about this cube. The cube might have been reorganized or changed on the server. The PivotTable was not refreshed."
IMPORTANT: I have also seen the standard “Data Refresh Failed” error:
There are a variety of places you can look to see if this is indeed an error with the Kerberos.dll:
1. UDL (Universal Data Link)2. ULS Logs3. Event Viewer4. Network Trace
There is one common theme you will notice across all these results, NOT SUPPORTED.
1. Test a UDL to the datasource from the server running Excel Services.
*Since the OS is 64 bit, you will need to call it a certain way (see below):
On the server you are running Excel Calculation Services on please perform the following:1. Right click on your desktop (or wherever you are having the problem) and create a new text doc.2. Rename it to .udl (make sure you do NOT have on "hide known extensions") Save > Close > Place this on the Root of C:. Execute the command below from a command line or Start/Run: C:\Windows\syswow64\rundll32.exe "C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll",OpenDSLFile C:\test.udl3. Double click, choose Microsoft OLE DB Provider for Analysis Services 10.0 on the first tab (Provider).4. On the 2nd tab (Connection) enter the AS Server name (or AS servername\instance for a named instance) in the Data Source box. Leave Location empty. For the server name enter the shortname/netbios name.5. On the 2nd tab (Connection) choose "Use Windows NT Integrated security".6. Now go to the 4th tab (All) and scroll down to highlight SSPI. Click on "Edit Value" and enter Kerberos as the Property Value.7. Go back to the 2nd tab (Connection) and choose "Test Connection".
If you see the error, "Test connection failed because of an error in initializing provider. The following system error occurred: The function requested is not supported.", you need to upgrade the kerberos.dll (see KB 969083).
2. ULS logs.
If you see the below error in the ULS Logs, you need to upgrade the kerberos.dll (see KB 969083).
"OLEDBConnection::InitConnection: The following system error occurred: The function requested is not supported."
3. Event Viewer (turn on Kerberos Event Logging here, reproduce the error in the browser, then look in Event Viewer > Windows Logs > System):
If you see the error "KDC_ERR_ETYPE_NOTSUPP" in the Event Viewer, you need to upgrade the kerberos.dll (see KB 969083).
4. Network Trace.
If you see the error "NT Status: STATUS_NOT_SUPPORTED" in a Network Trace, you need to upgrade the kerberos.dll (see KB 969083).
To resolve all the above errors, apply the Hotfix in the below KB to the machines receiving Kerberos traffic (including the database; SQL & SSAS).
A Kerberos authentication fails together with theerror code 0X80090302 or 0x8009030f on a computer that is running Windows Server 2008 or Windows Vista when the AES algorithm is used
http://support.microsoft.com/kb/969083
A real easy thing to do is look at the Kerberos.dll in C:\Windows\System32\kerberos.dll > right click > Properties > Details
It needs to be at least 6.0.6002.22138. If it is not, apply the above Hotfix.
When refreshing in the browser you may come across this error:
"External Data Refresh FailedAn error occurred while working on the Data Model in the workbook. Please try again.We were unable to refresh one or more data connections in this workbook. The following connections failed to refresh:<Connection>"
This happens when you choose Data > "Refresh Selected Connection" or "Refresh All Connections"
If the SharePoint user has permissions to the PowerPivot Data Source AND that data source accepts Windows Credentials (SQL 2012/SSAS 2012) you can pull real-time data! This is achieved via the "Use the authenticated user's account" found in the Excel (PowerPivot) workbook > Data > Connections > Properties.
In "What is PowerPivot for SharePoint? Part 2". I discuss the 3 types if Refresh in PowerPivot 2013. With the "Interactive Data Refresh" you are passing the User's Windows Credentials to the backend data source (so make sure they have permissions to the data source.
Configuration Steps:
(*Disclaimer: This is meant to be a template to set up delegation and may not explicitly apply to your situation. Modifications may be necessary.)
To get this to work, you need to first make sure you are not opening the workbook in the "Microsoft Excel Web App" (see our previous blog).
To refresh external data in PowerPivot 2013, you need to follow several steps to configure your environment & set up Delegation.
1. Verify that the PowerPivot instance is at least 11.0.3000 (SQL 2012 SP1) and that it is running in SharePoint mode (SQL Management Studio > Right Click the PowerPivot Instance > Properties):
2. Collect the account running "SQL Server Analysis Services (POWERPIVOT)":
To do this, log on to the server running PowerPivot > Start > Run > Services.msc and locate "SQL Server Analysis Services (POWERPIVOT)", you will see the account in the "Log On As" column (write that account down, you will need it shortly).
3. On the Analysis Services Server (PowerPivot Server) running in SharePoint mode, Add the Analysis Services service account (account running "SQL Server Analysis Services (POWERPIVOT)") to the "Act as part of the operating system" privilege:
a. Start > Run “secpol.msc”b. Click Local Security Policy, then click Local policies, and then click User rights assignment.c. Add the service account.
4. Restart Excel Services and Reboot the Analysis Services server (the Server running PowerPivot).
If the backend data source is on the same server as the Analysis Services PowerPivot instance, delegation is not required and you are done at this point!
5. Collect the account running "SQL Server (MSSQLSERVER)" or "SQL Server Analysis Services (<Instance>)":
"SQL Server (MSSQLSERVER)":
Log on to the server running SQL > Start > Run > Services.msc and locate "SQL Server (MSSQLSERVER)", you will see the account in the "Log On As" column (write that account down, you will need it shortly).
"SQL Server Analysis Services (<Instance>)":
Log on to the server running SQL > Start > Run > Services.msc and locate "SQL Server Analysis Services (<Instance>)", you will see the account in the "Log On As" column (write that account down, you will need it shortly).
6. Add these SPNs to the account running the data source your workbook is connecting to. Either "SQL Server (MSSQLSERVER)" or "SQL Server Analysis Services (<Instance>)":
Account Running SQL Server:
MSSQLSVC/<SQLServer_Name>MSSQLSVC/<SQLServer_Name.FQDN>
Account Running SQL Server Analysis Services:
MSOLAPSvc.3/<SQL_Server_Analysis_Services_Server_Name:Instance>MSOLAPSvc.3/<SQL_Server_Analysis_Services_Server_Name.FQDN:Instance>
******Important Note: If Analysis Services has a named instance other than ("MSSQLSERVER)" (this is the default Instance) you will need include that in the Service Principal Name. In the below example, I will use the instance name "TABULAR".
SQL Server:
MSSQLSvc/<SQL_Server_Server_Name:Instance>MSSQLSvc/<SQL_Server_Server_Name.FQDN:Instance>
Example:
MSSQLSvc/SQLSvr:TABULARMSSQLSvc/SQLSvr.contoso.com:TABULAR
SQL Server Analysis Server:
MSOLAPSvc.3/SSASSvr:TABULARMSOLAPSvc.3/SSASSvr.contoso.com:TABULAR
******Important Note 2: If the Analysis Services Server has a Named Instance other than (MSSQLSERVER) (which is Default), like "TABULAR". You will need to add MSOLAPDisco.3 SPNs to the account running the SQL Browser service (Example: Contoso\BrowserSvc). “Disco” is short for “Discovery”. This needs to be set so the Browser Service can discover SSAS.
MSOLAPDisco.3/<SQL_Server_Analysis_Services_Server_Name>MSOLAPDisco.3/<SQL_Server_Analysis_Services_Server_Name.FQDN>
MSOLAPDisco.3/SSASSvrMSOLAPDisco.3/SSASSvr.contoso.com
Article: More Information regarding the MSOLAPDisco.3 SPNs can be found in the below article:
An SPN for the SQL Server Browser service is required when you establish a connection to a named instance of SQL Server Analysis Services or of SQL Serverhttp://support.microsoft.com/kb/950599
7. Constrain Delegation between the account running "SQL Server Analysis Services (POWERPIVOT)" and "SQL Server (MSSQLSERVER)" or "SQL Server Analysis Services (<Instance>)":
In Active Directory Users and Computers > Account 1> Properties > Delegation Tab > Trust this user for delegation to specified services only > Use any authentication protocol > Add… > Users or Computers… > Account 2 > Check Names > OK > “Select All” Available Services > OK > OK
Additional Notes:
Excel Services sends the Analysis Services server a process command that instructs the server to impersonate a user account. To obtain system rights sufficient to perform the user impersonation-delegation process, the Analysis Services service account, requires Act as part of the operating system privilege on the local server. The Analysis Services server also needs to be able to delegate the user's credentials to data sources. The query result is sent to Excel Services.
Delegation from the Excel Services service account or from Claims to Windows Token Service (C2WTS) to the Analysis services instance is not required. Therefore no configuration for KCD from Excel Service or C2WTS to PowerPivot Analysis Services service is necessary.
If the backend data source is on the same server as the Analysis Services PowerPivot instance, delegation is not required.
If you do not have a SPN set for the account running the "SQL Server Analysis Services (POWERPIVOT)", the Delegation tab will not be present for that Account in Active Directory. I propose adding the dummy SPN, Http/dummy.
Delegation from the Excel Services service account or from Claims to Windows token service (C2WTS) to the Analysis services instance is not required. Therefore no configuration for KCD from Excel Services or C2WTS to PowerPivot AS service is necessary.