ECS: Data Refresh Failed

  • Article

We have setup a MOSS Farm with WFE, ECS and SSAS separated by physical boundaries:

 

 

Server

Notes

WFE: MOSS Web Front End

App pool running under Account A1

ECS: Excel Calculation Services

App pool running under Account A1

SSAS: SQL Server Analysis Services

SSAS Service running under Account A1

 

In the above configuration constrained delegation is enabled in the farm which allows each farm entity to authenticate and authorize user requests. For example

  1. user Redmond\Joe requests an excel file to be rendered in HTML from the WFE (Step: S1)
  2. User request is forwarded to ECS (Step S2). In this example, the excel file the user has requested has content as well as embedded data connection pointing to a cube. When this request arrives at ECS, ECS in turn tries to refresh data from the cube (this is a setting in ECS which has been turned ON in our farm)
  3. ECS forms the appropriate MDX query and sends the request to SSAS (Step S3). At this point SSAS needs to verify if user redmond\Joe is authorized to view the content. As we have enabled constrained delegation, Account A1 which is running the App Pool for ECS, is allowed to impersonate user redmond\Joe (loosely speaking) and hence SSAS sees the request coming from redmond\Joe.
  4. SSAS gets this request from redmond\Joe and either returns the result of the MDX query or an appropriate message in case user redmond\Joe is not authorized (Step S4)
  5. ECS converts the result set (or the error message) into HTML and sends the response back to WFE (Step S5)
  6. The user sees the appropriate content in his browser in Step S6

Ok, now since we have got the background out of the way, let me quickly get to the point of this blog post. Today, account A1's password was updated, and hence I updated the app pool passwords on WFE and ECS. Once I did that, on request of an excel report, I would see the following error in my browser:

Data Refresh Failed

The data sources may be unreachable, may not be responding, or may have denied you access.

Verify that data refresh is enabled for the trusted file location and that the workbook data authentication is correctly set.

pursuant to which I started looking up into the logs to figure out what happened. The error in the logs would read:

Cannot connect to the server provided in the connection string: XXXXXXX. Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: Authentication failed. ---> System.ComponentModel.Win32Exception: The target principal name is incorrect at Microsoft.AnalysisServices.AdomdClient.NTAuthentication.GetOutgoingBlob(Byte[] incomingBlob, Boolean& handshakeComplete) at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Authenticate(ConnectionInfo connectionInfo, DateTime startTime) --- End of inner exception stack trace --- at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Authenticate(ConnectionInfo connectionInfo, DateTime startTime) at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenTcpConnection(ConnectionInfo connectionInfo) at Microsoft.AnalysisServices.AdomdClient.XmlaC

Failed on constrained delegation. Exception: System.ArgumentException: Authentication failed. ---> Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: Authentication failed. ---> System.ComponentModel.Win32Exception: The target principal name is incorrect at Microsoft.AnalysisServices.AdomdClient.NTAuthentication.GetOutgoingBlob(Byte[] incomingBlob, Boolean& handshakeComplete) at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Authenticate(ConnectionInfo connectionInfo, DateTime startTime) --- End of inner exception stack trace --- at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Authenticate(ConnectionInfo connectionInfo, DateTime startTime) at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenTcpConnection(ConnectionInfo connectionInfo) at

Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla() at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open() at Microsoft.SharePoint.Portal.WebControls.KPIAuthWork.Execute(Object[] args) --- End of inner exception stack trace --- at Microsoft.SharePoint.Portal.WebControls.KPIAuthWork.Execute(Object[] args) at Microsoft.Office.Server.ApplicationRegistry.Infrastructure.WindowsAuthenticator.ExecuteAfterConstrainedDelegation(Object[] args) at Microsoft.SharePoint.Portal.WebControls.KPIHelper.OpenConnectionAndCube(ODCData connectionInfo, AdomdConnection& asConnection, CubeDef& cube, Boolean remapSsoCredsExceptions)

System.ArgumentException: Authentication failed. at Microsoft.SharePoint.Portal.WebControls.KPIHelper.OpenConnectionAndCube(ODCData connectionInfo, AdomdConnection& asConnection, CubeDef& cube, Boolean remapSsoCredsExceptions) at Microsoft.SharePoint.Portal.WebControls.ScorecardFilterWebPart.ResolveExternalValues(String[] externalValues, FilterValuePair[]& resolvedPairs)

The solution to this problem: Update the password for Analysis Services and restart the SSAS ServiceJ.

See you all in 2008.

MOSS_Farm.jpg