How to setup Kerberos Constrained Delegation for SharePoint Excel Services and Analysis Services

Question

This question is in continuation to MSOLAPSvc.3 service missing from kerberos delegation

Target : To refresh a pivot table based out of a SSAS cube using Windows Authentication in the excel service settings. The excel file with embedded authentication settings will be uploaded on SharePoint and the logged in user can open the workbook in the excel viewer of SharePoint and refresh the cube. The windows login credentials of the logged in user will be sent to Analysis services for authentication through SharePoint.

The Setup:
(In braces are the hostnames)

Active Directory - (PILOTDC.COM) Windows Server 2008 R2
DNS A Record - portal (ip address of the SPSERVER)

SharePoint Farm (SPSERVER)
Single Server SharePoint 2010 farm. SharePoint services , Web Application pool and Service Application pools and SQL Server Service is running under a single service account (pilotdc\sp_svc)

Web Application: http://portal , Authentication Provider - Windows Negotiate (Kerberos)

AAM settings are updated and the sharepoint site opens under Kerberos authentication. This has been validated using fiddler to check response headers.

SQL Server Analysis Services 2008 R2 (BISERVER)
Services running on this server :-
SQL Server Database Engine and SQL Server Analysis Services. Both of them run under the same service account (pilotdc\bi_svc)

OLAP Cube is created from the database hosted in the BI SERVER.

Kerberos Settings:

SPN

SetSPN -S HTTP/Portal pilotdc\sp_serv
SetSPN -S HTTP/Portal.pilotdc.com pilotdc\sp_serv

SetSPN -S MSOLAPSvc.3/biserver pilotdc\bi_serv
SetSPN -S MSOLAPSvc.3/biserver.testdc.com pilotdc\bi_serv

Constrained Delegation

SP_SERV delegates to

HTTP PORTAL <br>
HTTP PORTAL.PILOTDC.COM

BI_SERV delegates to

MSOLAPSvc.3 BISERVER

Problem: My problem is when a user clicks on refresh all connections in the workbook in SharePoint the refresh fails for the pivot data. I have enabled SQL Profiler in the Analysis Services of the BISERVER and it shows NT Authority Anonymous login attempts whenever I refresh the worksheet from SharePoint. This concludes that SharePoint is able to communicate with the Analysis Services however does not pass the Windows Authentication correctly due to delegation problems.

Things I have verified :-
1. Claims to Windows Token Service is running
2. If I open the workbook in Excel Client from the SPSERVER , the kerberos authentication works and I am able refresh the data through the standalone excel application.

I'm sure I am missing something in the Kerberos SPN and Delegation front. I feel I'm almost there and only falling short of 1 or 2 configuration steps to make it work. Any help is appreciated over here.

Update (23/04/2013): I am adding additional configuration details as per the answers mentioned below.

1. C2WTS is running under the domain account (PILOTDC\SP_SERV)
2. Pilotdc\SP_SERV is the local administrator of SPSERVER. C2WTS , WEB APP Pool , Farm Services are all running under Pilotdc\SP_SERV.
3. SP_SERV delegates to HTTP PORTAL and MSOLAPSvc.3 BISERVER.
4. C2WTS depends on Cryptographic Services.

Answer Comments While, this is strange as both Wictor and Mike recommended a separate domain account for C2WTS service , in my case it worked just fine with a single service account. The missing step was the fact that Secpol.msc showed SP_SERV did not have the "Act as part of the operating system" rights.

Answer 1

Make sure you have the claims to windows token service on it's own service account:

a) Add the service account to the local Administrators Groups.

b) In local security policy (secpol.msc) under user rights assignment give the service account the following permissions:

1. Act as part of the operating system
2. Impersonate a client after authentication
3. Log on as a service

C) Open the command-prompt window.

Type: sc config c2wts depend=CryptSvc

Excerpt from: http://mikessharepoint.wordpress.com/2012/01/23/performancepoint-and-kerberos-woes/

Answer 2

You need to make sure that you have provisioned the Claims to Windows Token Service (C2WTS) using its own domain account. Then create a fake SPN for that account (this is only needed to get the Delegation tab to show up in the ADUC MMC):

setspn SP/C2WTS domain\user

Then configure constrained delegation for that service account to the SSAS service.

Answer 3

Did you add the file location of the Excel workbook to the trusted file locations from within CA>Service Applications>excel service application>Trusted File Locations?

Did you add the trusted data connections and the configure the SASS data model location?

Can you provide a breakdown of the Global Settings that you have configured from with the Excel Services SA?

Can you send the ULS log file entry for failed connection?

Answer 4

What about the SQL Server and SharePoint WFEs computer account settings / user account settings in Active Directory have they been setup to say which services they are able to use delegate user credentials for?

Have you created SPNs for the Claims To Windows Token Service?

Martin Hatch did an awesome presentation on configuring all of this a few years ago for London SharePoint User Group. The slides can be found here: http://www.martinhatch.com/2011/08/slides-from-suguk-london-configuring.html