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.
We used SSS(secured stored service) to pass effectiveUsername to ssas but it doesnt apply. why?
Secure Store does an actual Windows logon based on credentials stored in its database, retrieving a real token. EffectiveUserName allows e.g. Excel Services to pass a username to SSAS.
Secure Store can be used to store a single account for all access - or for each user to enter their own credentials.
The correct approach is to disable Secure Store Service and either enable EffectiveUserName in Excel Services or implement Kerberos.