This is a double-hop scenario in which from the client machine end user requests MOSS Server to view Excel file and behind the scene Excel Services on MOSS Server requests SSAS Server to access cube on behalf of the end user. NTLM authentication cannot perform this, Kerberos can.
To get everything all right, I checked/ran following in that sequence. I figured that Excel Services will give “Data Refresh Error” if any step is missing or not performed properly. Depending on network and firewalls, I believe every implementation will have unique situations.
- Configure Active Directory for Kerberos. Aside from SPNs for servers and services, AD needs to be informed which user and service accounts are trustworthy (delegation tab for AD object)
o setspn -A HTTP/MOSSServer.domain.com DOMAIN\SPServiceAccount
o setspn -A http/MOSSSiteHost DOMAIN\SPServiceAccount
o setspn -A http/MOSSMySiteHost DOMAIN\SPServiceAccount
o setspn -A http/MOSSSSPSite DOMAIN\SPServiceAccount
o Setspn.exe -A MSOLAPSvc.3/OLAPServer.domain.com DOMAIN\OLAPServiceAccount
o Setspn.exe -A MSOLAPSvc.3/OLAPServer DOMAIN\OLAPServiceAccount
- Ensure that SharePoint web site is configured to use Kerberos authentication (default is NTLM)
- On MOSS Server, configure Excel Services for delegation (so that it can request cube data on behalf of end user, hence security is not compromised)
o stsadm.exe -o set-ecssecurity -ssp %SSPNAME% -accessmodel delegation
o stsadm.exe -o execadmsvcjobs
o iisreset