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

Question

Hi,

I am at a loss how to solve this.  I have tried regenerating the key.  I changed all of my sharepoint accounts, even the unattended credentials account, to the domain administrator.  This is a single server install (development environment).

I cannot refresh an excel sheet against an OLAP source or get the following: "An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh: PowerPivot Data"

The other threads about making sure the SP admin account is in the local admin group does not seem to apply since I am using the domain admin account for everything (did this to rule out that possible cause).  Further, in the logs I see nothing about a "port denial".  In fact I cannot locate ANY error for this in the log file.

Any ideas?

 

Answer 1

So I did find this in the log:

 

02/08/2011 03:10:17.25  w3wp.exe (0x1BCC)                        0x1B88 Excel Services Application     Excel Calculation Services     g197 Medium   ExternalSource.GetExternalKeyAndStateAndMarkUsed: Populating the session's credentials for external data. Index=5, Credentials=[18329135, CSDEV01\lcascio] 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.25  w3wp.exe (0x1BCC)                        0x1B88 Excel Services Application     Excel Calculation Services     eq3r Medium   ConnectionRequest.ConnectionRequest: New connection request. SessionId=1.V21.4nL2cqff44fmGxbSyV37Y90.5.en-US5.en-US73.+0000#0000-10-00-05T02:00:00:0000#+0000#0000-03-00-05T01:00:00:0000#-006036.77f7a857-69f4-4550-b9e2-488eb6542c251.N, WorkbookVersion=ConnectionInfo.WorkbookVersion: Uri=http://ip-0a6601c7/PowerPivot Gallery/Mobile Dashboard.xlsx, Version=Monday, 07 February 2011 14:11:20 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.25  w3wp.exe (0x1BCC)                        0x1B88 Excel Services Application     Excel Calculation Services     bco9 Medium   PF_CHECK_ERROR returned 'hresult error' 0x80070542 ; Stack Trace:NA 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.25  w3wp.exe (0x1BCC)                        0x1B88 Excel Services Application     Excel Calculation Services     d5b7 Medium   PF_CHECK_ERROR returned 'hresult error' 0x80070542 ; Stack Trace:NA 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.25  w3wp.exe (0x1BCC)                        0x1B88 Excel Services Application     Excel Calculation Services     6k4q Medium   ConnectionManager.GetConnection: Failed to create new connection, 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.Cre... 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.25* w3wp.exe (0x1BCC)                        0x1B88 Excel Services Application     Excel Calculation Services     6k4q Medium   ...dentialsDelegation.TryExecuteImpersonated(ExecuteImpersonatedMethod method)     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.CreateConnection(Credentials credentials, ConnectionInfo connectionInfo, Int32 keyLcid, Uri workbookUrl, Boolean auditConnection, SessionId sessionId), sessionId=1.V21.4nL2cqff44fmGxbSyV37Y90.5.en-US5.en-US73.+0000#0000-10-00-05T02:00:00:0000#+0000#0000-03-00-05T01:00:00:0000#-006036.77f7a857-69f4-4550-b9e2-488eb6542c251.N, connectionString=Provider=MSOLAP.4;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Optimize Response=3;Cell Error Mode=TextValue pool count=0 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.25  w3wp.exe (0x1BCC)                        0x1B88 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... 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.25* w3wp.exe (0x1BCC)                        0x1B88 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... 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.25* w3wp.exe (0x1BCC)                        0x1B88 Excel Services Application     Excel Calculation Services     eq3m High     ...tendedConnectionInfo extendedConnInfo, Credentials credentials, Int64 privateConnectionId, Boolean auditConnection) 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.25  w3wp.exe (0x1BCC)                        0x1B88 Excel Services Application     External Data                  2051 Information Refresh failed for 'PowerPivot Data' in the workbook 'http://ip-0a6601c7/PowerPivot Gallery/Mobile Dashboard.xlsx'. [Session: 1.V21.4nL2cqff44fmGxbSyV37Y90.5.en-US5.en-US73.+0000#0000-10-00-05T02:00:00:0000#+0000#0000-03-00-05T01:00:00:0000#-006036.77f7a857-69f4-4550-b9e2-488eb6542c251.N User: 0#.w|csdev01\lcascio] 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.25  w3wp.exe (0x1BCC)                        0x1B88 Excel Services Application     Excel Calculation Services     3sls Medium   ExternalSource.ValidateConnection: Unable to get a connection: 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.CredentialsDeleg... 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.25* w3wp.exe (0x1BCC)                        0x1B88 Excel Services Application     Excel Calculation Services     3sls Medium   ...ation.TryExecuteImpersonated(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 connectionRe... 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.25* w3wp.exe (0x1BCC)                        0x1B88 Excel Services Application     Excel Calculation Services     3sls Medium   ...quest, ExtendedConnectionInfo extendedConnInfo, Credentials credentials, Int64 privateConnectionId, Boolean auditConnection)     at Microsoft.Office.Excel.Server.CalculationServer.ExternalSource.TryGetValidatedConnection(Request request, Credentials credentials, ExtendedConnectionInfo extendedConnectionInfo, Boolean shouldReportFailure, Boolean auditConnection, Connection& connectionOut). sessionId=1.V21.4nL2cqff44fmGxbSyV37Y90.5.en-US5.en-US73.+0000#0000-10-00-05T02:00:00:0000#+0000#0000-03-00-05T01:00:00:0000#-006036.77f7a857-69f4-4550-b9e2-488eb6542c251.N, externalSource=PowerPivot Data 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.25  w3wp.exe (0x1BCC)                        0x1B88 Excel Services Application     Excel Calculation Services     482l Medium   ConnectionInfoManager.InitConnInfoFromOdcFile: ODC File Uri is not valid:  40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.25  w3wp.exe (0x1BCC)                        0x1B88 Excel Services Application     Excel Calculation Services     8xk9 Medium   ExternalSource.ExecuteOperation: We exhausted all available connection information. Exception: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException: Exception of type 'Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException' was thrown.     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionInfoManager.GetConnectionInfo(Request request, String externalSourceName, Int32 externalSourceIndex, Boolean& shouldReportFailure)     at Microsoft.Office.Excel.Server.CalculationServer.ExternalSource.ExecuteOperation(Request request, ExternalSourceStateInfo externalSourceStateInfo, ExternalSourceStateInfo prevExternalSourceStateInfo, Int32 index, ConnectionInfoManager connectionInfoManager, ExternalDataScenario scenario, DataOperation dataOpe... 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.25* w3wp.exe (0x1BCC)                        0x1B88 Excel Services Application     Excel Calculation Services     8xk9 Medium   ...ration, Boolean verifyPreOperationConnection), Data Connection Name: PowerPivot Data, SessionId: 1.V21.4nL2cqff44fmGxbSyV37Y90.5.en-US5.en-US73.+0000#0000-10-00-05T02:00:00:0000#+0000#0000-03-00-05T01:00:00:0000#-006036.77f7a857-69f4-4550-b9e2-488eb6542c251.N, UserId: 0#.w|csdev01\lcascio 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.26  w3wp.exe (0x1BCC)                        0x1B88 SharePoint Server              Logging Correlation Data       xmnv Medium   Result=Success 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.26  w3wp.exe (0x1BCC)                        0x1B88 Excel Services Application     Excel Calculation Services     eci4 Medium   ExcelService.PostProcessRequest: finished request of type ApplySlicerSelection 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.26  w3wp.exe (0x1BCC)                        0x1B88 SharePoint Foundation          Monitoring                     b4ly Medium   Leaving Monitored Scope (ExecuteWcfServerOperation). Execution Time=53.7784550823207 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.26  w3wp.exe (0x1BCC)                        0x1B88 SharePoint Foundation          Topology                       e5mb Medium   WcfReceiveRequest: LocalAddress: 'http://ip-0a6601c7.csdev01.mycardstar.com:32843/061a1546e5434668b536a3673c5d50f5/ExcelService.asmx' Channel: 'System.ServiceModel.Channels.ServiceChannel' Action: 'http://schemas.microsoft.com/office/Excel/Server/WebServices/ExcelServerInternalService/ExcelServiceSoap/ApplySlicerSelection' MessageId: 'urn:uuid:cf0dbd36-297f-4e9f-a3ec-70a9dc0801c3' 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.26  w3wp.exe (0x1BCC)                        0x1B88 SharePoint Foundation          Monitoring                     nasq Medium   Entering monitored scope (ExecuteWcfServerOperation) 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.26  w3wp.exe (0x1BCC)                        0x1B88 Excel Services Application     Excel Calculation Services     ecc3 Medium   ExcelServiceBase.EndProcessOperation: Called. UserOperation was finished synchronously: True 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.26  w3wp.exe (0x1BCC)                        0x1B88 Excel Services Application     Excel Calculation Services     d64g Medium   UserOperation.Dispose: Disposing Microsoft.Office.Excel.Server.CalculationServer.Operations.ApplySlicerSelectionOperation, WebMethod: ApplySlicerSelection. 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a
02/08/2011 03:10:17.26  w3wp.exe (0x1BCC)                        0x1B88 SharePoint Foundation          Monitoring                     b4ly Medium   Leaving Monitored Scope (ExecuteWcfServerOperation). Execution Time=0.207455914448248 40001a2d-d0b8-4a2a-84ec-257f2e4bf51a

Answer 2

One other tidbit, this is setup for SSAS anonymous access which I have conformed works.  So somehow Sharepoint managed to not allow access to something that is wide open.

Answer 3

Hi Lee,

 

Is it a PowerPivot workbook that uses a OLAP cube as a data source, or is it a regular Excel workbook connecting to a OLAP cube? If you download the workbook and open in a Excel client (desktop), can you refresh all connections? How did you setup the credentials in this workbook to access this data source?

 

Also, did you try to stop and start the Claims to Windows Token Service in the SharePoint's Central Administration app at Services on Server page?

Thanks,

Mariano

---

---

Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.

Answer 4

Hi Mariano.  They are powerpivot books.  I can refresh the connections if I manually open.  I've rebooted many times so I know the Claims svc will not help.  But within the site manual refresh and slicers do not work, which is normal for this type of problem as I understand now.  Thanks for your help!

Answer 5

Hi Lee,

I understand you've rebooted the server, but did you try to stop and start the Claims to Windows Token Service in the SharePoint's Central Administration app at Services on Server page? It has some issues and even the reboot may not have started it correctly, so you may want to stopt and start from Central Administration application in SharePoint.

For more information, you can check out this blog post:

  http://powerpivotgeek.com/2010/02/08/the-data-connection-uses-windows-authentication-and-user-credentials-could-not-be-delegated/

 and

http://powerpivotgeek.com/2009/12/11/excel-services-delegation/

Thanks,

Mariano

---

---

Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.

Answer 6

Hi Mariano,

 

The links you posted were very helpful.   Turns out the claims token service account had been changed.  Changing that back to a local system account got every thing working.  Thanks!

Answer 7

After spending HOURS trying to figure this out Lee's note above provided the key: on our system, when SharePoint starts the C2WTS it set a domain account as the user. In Services Manager I reset the user back to local system, restarted the service, tested, cheered, then cracked a well deserved beer.

Next step is to figure out how to get SharePoint to start the service with the local account.

 

Thanks Lee!

Answer 8

Hi,

The Claims to Windows Token Service (c2wts) really need to be started with Local System!

THANKS,

Tiago.

PS: It solved me problem, see Yet another question about - unable to refresh data for a data connection in the workbook.