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.
(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)
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.
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
SP_SERV delegates to
HTTP PORTAL <br> HTTP PORTAL.PILOTDC.COM
BI_SERV delegates to
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.
- C2WTS is running under the domain account (PILOTDC\SP_SERV)
- Pilotdc\SP_SERV is the local administrator of SPSERVER. C2WTS , WEB APP Pool , Farm Services are all running under Pilotdc\SP_SERV.
- SP_SERV delegates to HTTP PORTAL and MSOLAPSvc.3 BISERVER.
- 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.