none
Analysis Services Refresh Error

    Frage

  • I get the following error when trying to expand a Pivot Table saved inside an excel document (in a PowerPivot gallery):


    "An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh: XXX"


    This workbook connects to a remote Analysis Services instance - NOT the one installed by PowerPivot for SharePoint. Is this supported? 


    I have configured the unattended Excel Services account and the unattended PowerPivot account... how can I fix this error?


    Are there any logs I can view to troubleshoot the error? The standard SharePoint Logs even with Excel Services event log source configured don't seem to report anything.



    Donnerstag, 7. Juli 2011 04:48

Alle Antworten

  • I updated the Excel Workbook's Connection Authentication Settings to "None" and now I see this error in the Logs:

     

    07/07/2011 14:58:47.90 w3wp.exe (0x04AC)                       0x142C Secure Store Service           Secure Store                   7493 Critical The Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was 'Access is denied.'. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK). 324ae782-73f7-497f-8ed4-b227437ac098

    07/07/2011 14:58:47.90 w3wp.exe (0x04AC)                       0x142C Secure Store Service           Secure Store                   efp4 Monitorable GetCredentials failed with the following exception: System.ServiceModel.FaultException`1[Microsoft.Office.SecureStoreService.Server.SecureStoreServiceFault]: Access is denied. (Fault Detail is equal to Microsoft.Office.SecureStoreService.Server.SecureStoreServiceFault). 324ae782-73f7-497f-8ed4-b227437ac098

    07/07/2011 14:58:47.92 w3wp.exe (0x04AC)                       0x1E00 SharePoint Foundation         Monitoring                     b4ly High     Leaving Monitored Scope (ExecuteOnChannel: GetCredentials). Execution Time=2252.50807015975 ff354052-5e6e-40e0-8c6f-73db60859144

    07/07/2011 14:58:47.92 w3wp.exe (0x04AC)                       0x1E00 Excel Services Application     Web Front End                 4969 Warning SSS has failed with the following exception: Access is denied. [Session: 1.V21.9bWQ8vVK2cfT7CyIq+Vc890.5.en-US5.en-AU73.-0600#0000-00-00-00T00:00:00:0000#+0000#0000-00-00-00T00:00:00:0000#-006036.81005747-e353-41d8-b113-db7f45c723451.N User: 0#.w|DOMAIN\user] ff354052-5e6e-40e0-8c6f-73db60859144

    07/07/2011 14:58:47.92 w3wp.exe (0x04AC)                       0x1E00 Excel Services Application     External Data                 3237 Information Unable to establish connection using only the connection string. If a username and password are saved in the connection string, they may not be correct, or the Unattended Service Account may not be configured. [Session: 1.V21.9bWQ8vVK2cfT7CyIq+Vc890.5.en-US5.en-AU73.-0600#0000-00-00-00T00:00:00:0000#+0000#0000-00-00-00T00:00:00:0000#-006036.81005747-e353-41d8-b113-db7f45c723451.N User: 0#.w|DOMAIN\user] ff354052-5e6e-40e0-8c6f-73db60859144

    07/07/2011 14:58:47.93 w3wp.exe (0x13E0)                       0x1ECC SharePoint Foundation         Monitoring                     b4ly High     Leaving Monitored Scope (ExecuteWcfOperation:http://schemas.microsoft.com/office/Excel/Server/WebServices/ExcelServerInternalService/ExcelServiceSoap/TogglePivotDrill). Execution Time=2372.85939972818 ff354052-5e6e-40e0-8c6f-73db60859144

    07/07/2011 14:58:51.37 w3wp.exe (0x13E0)                       0x1894 SharePoint Foundation         Monitoring                     b4ly High     Leaving Monitored Scope (ExecuteWcfOperation:http://schemas.microsoft.com/office/Excel/Server/WebServices/ExcelServerInternalService/ExcelServiceSoap/GetRange). Execution Time=110.16707430693 a67b9a24-291b-44ed-9342-21919f41483e

    07/07/2011 14:58:55.03 w3wp.exe (0x04AC)                       0x16F4 Secure Store Service           Secure Store                   efr5 High     ValidateCredentialClaims - Access Denied: Claims stored in the credentials did not match with the group claim for a group app. 815be79d-1645-4924-b73e-d2dc49240ac2

    07/07/2011 14:58:55.03 w3wp.exe (0x04AC)                       0x16F4 Secure Store Service           Secure Store                   7493 Critical The Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was 'Access is denied.'. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK). 815be79d-1645-4924-b73e-d2dc49240ac2

    Donnerstag, 7. Juli 2011 05:01
  • I also notice this error in the logs - this one is when I try expanding a Pivot Table node when the excel document is hosted outside of a Power Pivot gallery:

     

    07/07/2011 16:26:29.07 w3wp.exe (0x17B8)                       0x0A2C Excel Services Application     Excel Calculation Services     eq3m High     ConnectionManager.GetConnection: Caught an exception: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException: Exception of type 'Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException' was thrown.     at Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInterop.InitConnection()     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.<>c__DisplayClass1.<CreateConnection>b__0()     at Microsoft.Office.Excel.Server.Credentials.TryExecuteImpersonated(WindowsIdentity wi, ExecuteImpersonatedMethod method, Boolean dispose)     at Microsoft.Office.Excel.Server.Credentials.TryExecuteImpersonated(ExecuteImpersonatedMethod method, Boolean dispose)     at Microsoft.Office.Excel.Server.CalculationServer.CredentialsDelegation.Try... a2db11de-93c0-4aed-8d7d-7ee9a0fba31e

    07/07/2011 16:26:29.07* w3wp.exe (0x17B8)                       0x0A2C Excel Services Application     Excel Calculation Services     eq3m High     ...ExecuteImpersonated(ExecuteImpersonatedMethod method)     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.CreateConnection(Credentials credentials, ConnectionInfo connectionInfo, Int32 keyLcid, Uri workbookUrl, Boolean auditConnection, SessionId sessionId)     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.CreateConnectionAndAddToList(ConnectionRequest connectionRequest, ExtendedConnectionInfo extendedConnInfo, Credentials credentials, Boolean auditConnection, Int32 keyLcid, ConnectionInfo connectionInfo, ConnectionKey connectionKey, AutoReaderWriterLock autoPoolLock, Connection& connection, ConnectionList& connectionList)     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.GetConnection(ConnectionRequest connectionRequest, Ex... a2db11de-93c0-4aed-8d7d-7ee9a0fba31e

    07/07/2011 16:26:29.07* w3wp.exe (0x17B8)                       0x0A2C Excel Services Application     Excel Calculation Services     eq3m High     ...tendedConnectionInfo extendedConnInfo, Credentials credentials, Int64 privateConnectionId, Boolean auditConnection) a2db11de-93c0-4aed-8d7d-7ee9a0fba31e

    07/07/2011 16:26:29.07 w3wp.exe (0x17B8)                       0x0A2C Excel Services Application     External Data                 2051 Information Refresh failed for '{CUBE NAME}' in the workbook 'http://SERVER/PowerPivot/Shared Documents/CubeTest.xlsx'. [Session: 1.V21.2tSxTweNbtleS5oaURHWM90.5.en-US5.en-AU73.-0600#0000-00-00-00T00:00:00:0000#+0000#0000-00-00-00T00:00:00:0000#-006036.81005747-e353-41d8-b113-db7f45c723451.N User: 0#.w|DOMAIN\USER] a2db11de-93c0-4aed-8d7d-7ee9a0fba31e

    Donnerstag, 7. Juli 2011 06:31
  • Hi Peter, I recently got the same Access is Denied error in SSS.  The problem for me is that the account used to run data refresh did not have Read permissions on the extenral.  Whatever credentials you used to setup the unattended account must have read permissions on the external SSAS database. 
    Heidi Steen (MSFT)
    Donnerstag, 28. Juli 2011 19:48
  •  

     

    Hi Peter,

     

    Did Heidi's suggestion work? Are you still having problems?

     

    Best,

    Mariano


    Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.
    Dienstag, 16. August 2011 04:08
  • No I"m afraid not Mariano - I checked the permissions on the remote SSAS database/cube and they seemed OK. Perhaps there's other things I need to check?

    Mittwoch, 17. August 2011 23:09
  • Hi there,

    Slightly related to this, I also find myself in the same position: I have an instance of SSAS which is where I have the Contoso/AdventureWorks cubes and an instance of SSAS that was created when setting up PowerPivot for SharePoint.

    Now here's where I look to Microsoft for confirmation... It would seem that creating an Excel workbook using PowerPivot for Excel, when hosted in a PowerPivot gallery, does not support querying of data from an instance of SSAS that is not configured in SharePoint integrated mode.

    I think the above statement is true... It woul dbe great if somebody was able to confirm.

    Also, if the above IS true, then consider an organisation that already has several OLAP Cubes on an SSAS instance prior to setting up PowerPivot for SharePoint. How should the organisation go about querying this data for publishing in SharePoint? Sure, the excel services rendering will work just fine, but when the data processing part is passed onto PowerPivot for SharePoint - and ultimately SSAS - it does not have the data; It's in the other instance.


    Regards, Jason Dunbar

    Mittwoch, 22. Februar 2012 10:35
  • Hi,
    From the below line in yoiur error message, I think that your credentials are not getting delegated properly. See if claims based windows token service is running in services. Also check if all the services and app pools are working properly.

    07/07/2011 14:58:55.03 w3wp.exe (0x04AC)                       0x16F4 Secure Store Service          Secure Store                  7493 CriticalThe Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was 'Access is denied.'. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK).815be79d-1645-4924-b73e-d2dc49240ac2

    ==============================

    Please mark as answer if it is useful

    Mittwoch, 14. März 2012 11:15
  • Hi,

    I have the same problem, but i don't find the solution. Any other idea ?

    Best regards

    Anouar


    sharepoint 2010


    • Bearbeitet boussarsar Montag, 25. Juni 2012 14:52
    Montag, 25. Juni 2012 14:52
  • Have you checked if the account running the PowerPivot System service has credential rights to retrieve the stored creds in the Secure Store target app? You cannot actually see if does or not, but you can set the credential again for this secure store target application. It looks like this is missing.

    Best,

    Mariano


    Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.

    Dienstag, 26. Juni 2012 18:20