We have an OLAP project which uses Sharepoint 2013 Excel Services to show excel files that contains external data from SQL Server Analysis Services (SSAS) cube. Our Authentication method to login users is FBA (Forms Based Authentication)
Our Goal is to show only subset of data from SSAS cube for each user based on their role which has already been defined in SSAS side.
FBA authenticates users against AD and all authorized user are already member of respective role in SSAS based on their clearance level. We need Excel Services passes EffectiveUserName to SSAS and for that we enabled the EffectiveUserName in global settings of Excel Services on SharePoint central admin.
Than in Excel, we have enabled the Windows Authentication in the Excel Service Authentication Settings of Connection Properties as we read on some blog posts. But after accessing it via the SharePoint, the following error occurred.
“The data connection uses Windows Authentication and user credentials could not be delegated.” As I already stated, I don't want to delegate user credentials to SSAS. It's already been configure for Excel Services to use single account to connect to Analysis Services. I only want to pass EffectiveUserName.
Using windows authentication is not an option because users access the site from extranet and internet.
Finally we change our login method. we use a customized login page that use windows claim authentication to login to Sharepoint site. For more information see this link : Customize windows authentication login