AdomdConnectionException using PerformancePoint hitting Analysis Services
I was working with a customer who was encountering problems trying to use a PerformancePoint Dashboard against an Analysis Services Instance. The issue came down to the Claims to Windows Token Service (C2WTS) configuration. This is used to take the Claims context and convert it to a Windows Token for use to backend servers.
When trying to create a Data Source within PerformancePoint Dashboard Designer, using the Unattended Service Account, the test succeeds. If we switch that over to Per-user Identity, we see the following:
Within the Event Logs for the SharePoint App Server, we see the following from PerformancePoint:
Log Name: Application
Source: Microsoft-SharePoint Products-PerformancePoint Service
Date: 9/6/2012 11:59:57 AM
Event ID: 37
Task Category: PerformancePoint Services
Level: Error
Keywords:
User: BATTLESTAR\spservice
Computer: AdmAdama.battlestar.local
Description:
The following data source cannot be used because PerformancePoint Services is not configured correctly.Data source location: https://admadama:82/Data Connections for PerformancePoint/5_.000
Data source name: New Data Source 3Monitoring Service was unable to retrieve a Windows identity for "BATTLESTAR\asaxton". Verify that the web application authentication provider in SharePoint Central Administration is the default windows Negotiate or Kerberos provider. If the user does not have a valid active directory account the data source will need to be configured to use the unattended service account for the user to access this data.
Exception details:
System.InvalidOperationException: Could not retrieve a valid Windows identity. ---> System.ArgumentException: Token cannot be zero.
at System.Security.Principal.WindowsIdentity.CreateFromToken(IntPtr userToken)
at System.Security.Principal.WindowsIdentity..ctor(IntPtr userToken, String authType, Int32 isAuthenticated)
at System.Security.Principal.WindowsIdentity..ctor(IntPtr userToken)
at Microsoft.IdentityModel.WindowsTokenService.S4UClient.CallService(Func`2 contractOperation)
at Microsoft.SharePoint.SPSecurityContext.GetWindowsIdentity()
--- End of inner exception stack trace ---
at Microsoft.SharePoint.SPSecurityContext.GetWindowsIdentity()
at Microsoft.PerformancePoint.Scorecards.ServerCommon.ConnectionContextHelper.SetContext(ConnectionContext connectionContext, ICredentialProvider credentials)
This error is indicating that the C2WTS Service failed with getting the windows Credential. The S4UClient call is the key indicator. We reviewed the C2WTS settings, which aren’t many, and the one thing I remembered is that if you are using a Domain User account for the C2WTS Windows Service, you have to add it to the Local Adminstrators group on the box that is trying to invoke it. In our case, it is the server hosting the PerformancePoint Service App. You don’t have to do this step if you leave the C2WTS service as LocalSystem.
Once that is done, we need to recycle the C2WTS Windows Service and try it again. We were then presented with a different error:
Log Name: Application
Source: Microsoft-SharePoint Products-PerformancePoint Service
Date: 9/6/2012 12:09:42 PM
Event ID: 9
Task Category: PerformancePoint Services
Level: Warning
Keywords:
User: BATTLESTAR\spservice
Computer: AdmAdama.battlestar.local
Description:
The user "BATTLESTAR\asaxton" does not have access to the following data source server.Data source location: https://admadama:82/Data Connections for PerformancePoint/5_.000
Data source name: New Data Source 3
Server name: bspegasus\kjssasException details:
Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. ---> System.Net.Sockets.SocketException: The requested name is valid, but no data of the requested type was found
at System.Net.Sockets.TcpClient..ctor(String hostname, Int32 port)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)
--- End of inner exception stack trace ---
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenTcpConnection(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetInstancePort(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenTcpConnection(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
at Microsoft.PerformancePoint.Scorecards.DataSourceProviders.AdomdConnectionPool`1.GetConnection(String connectionString, ConnectionContext connectionCtx, String effectiveUserName, CultureInfo culture, NewConnectionHandler newConnectionHandler, TestConnectionHandler testConnectionHandler)
At first I thought that this may be because of SQL Browser, based on the error message. And, I know that for SQL Browser, when we have a Named Instance, you have to add the DISCO SPN’s per the following KB 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 Server
https://support.microsoft.com/kb/950599
My thought was that I had to add delegation rights for the Claims and PerformancePoint service over to the DISCO service. This actually turned out to not be needed at all based on my testing. I have this actually working with those SPN’s in place and without the Claims/PerformancePoint service accounts having Constrained Delegation rights to that.
After playing around with this a little more, I remembered that I had been told a while back that the Claims Service Account needs to have “Act as part of the operating system” right in order to work correctly. My mindset was that if the account was a local admin, this wouldn’t be needed. With that right missing, I was able to reproduce the 2nd error that the customer was hitting. This is actually listed on page 126 of the following whitepaper.
Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products
https://www.microsoft.com/en-us/download/details.aspx?id=23176
Of note, the “Impersonate a client after authentication” right that it lists, you get for free because the Claims Service account will be a member of WSS_WPG which is a member of the IIS_IUSRS group because of SharePoint.
The C2WTS Service Account will be automatically added to the “Log on as a service” right when you start the C2WTS Service from Central Admin in the “Manage services on server” area.
The lesson learned here is that the Claims to Windows Token Service Account needs to be in the Local Administrators group and has to have the “Act as part of the operating system” right that you can assign within Local policies.
Adam W. Saxton | Microsoft Escalation Services
https://twitter.com/awsaxton