Excel Services connection error using SharePoint 2010 to query Analysis Services (2008 RT)

Question

I am connecting to an Analysis Services cube from an Excel Services spreadsheet. SharePoint and SQL Server are configured on separate servers. Am using Excel 2010 / SharePoint 2010 / SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64).

Refreshing all connections (or clicking an item in the slicer) throws an error: "An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh: Adventure Works Cube1"

This from the SharePoint logs:

"Refresh failed for 'Adventure Works Cube1' in the workbook 'http://spsatl03t/team/Excel Documents/ExcelServices.xlsx'. [Session: 1.V21.8D/M51Qif9Y+JASEqZsk390.5.en-US5.en-US73.+0300#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.c306da43-6452-40db-9249-6d1e343c79511.N User: 0#.w|kc\svcdms]"

I have configured the below SPNs for my SharePoint and db servers. All SharePoint services run under a single acct (SVCDMS). The SQL Server/ SSAS services both run as SQLService.

setspn -S http/spsatl03t KC\SVCDMS
setspn -S http/spsatl03t.kilpatrickstockton.ks KC\SVCDMS
setspn -S MSOLAPSvc/ddevatl01 KC\SQLService
setspn -S MSOLAPSvc/ddevatl01.kilpatrickstockton.ks KC\SQLService
setspn -S MSOLAPSvc.3/ddevatl01 KC\SQLService
setspn -S MSOLAPSvc.3/ddevatl01.kilpatrickstockton.ks KC\SQLService
setspn -S MSSQLSVC/ddevatl01 KC\SQLService
setspn -S MSSQLSVC/ddevatl01.kilpatrickstockton.ks KC\SQLService

Also, I have NO issues connecting to the AdventureWorks OLTP database via Excel Services. It's only when connecting to an SSAS cube that I get the error. And Kerberos seems to be working just fine. Verifiable w/ this query:

Select
    s.session_id,
    s.login_name,
    s.host_name,
    c.auth_scheme
from
sys.dm_exec_connections c
inner join
sys.dm_exec_sessions s
on c.session_id = s.session_id
order by host_name, login_name, auth_scheme

Interestingly, I noticed here that this was an issue with the beta release, but I'm using RTM: http://social.msdn.microsoft.com/Forums/en-US/sharepoint2010general/thread/46921dd5-4bf8-4ac1-a6d3-13ac4be8cf25

Osama Osama · Accepted Answer · 2011-12-12T20:21:45

Configure constrained delegation

Open the Active Directory Object’s properties in Active Directory Users and Computers.
Navigate to the Delegation tab.
Select Trust this user for delegation to specified services only.
Select Use any authentication protocol. This enables protocol transition and is required for the service account to use the C2WTS.

Configure the required local server permissions that the C2WTS requires. You will need to configure these permissions on each server the C2WTS runs on.

Log onto the server and give the C2WTS the following permissions:

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:

i. Act as part of the operating system

ii. Impersonate a client after authentication

iii. Log on as a service

See this document for more details: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23176

Excel Services connection error using SharePoint 2010 to query Analysis Services (2008 RT)

2 Answers