reporting services - Allowing users from a non trusted domain to Dimension data -

domain = ssrs users domain b = ssrs , ssas instances

there around 5 separate roles segregate allowed member set in dimension data. domain user(s) cannot added ssas cube role(s) in domain b since domain not trusted domain b. best way enable ssas dimensional data level security user(s) domain a?

tia!

the straightforward solution move ssas server domain a. that's users are. plus, if it's in domain a, domain b users can connect since domain trusts domain b 1 way. highly recommend this.

if that's not option, might try this. in ssrs, should able set credentials on ssas data source use domainb\serviceaccount. should able pull ssas data ssrs.

to bake role based security ssas, see 2 options. first, might create 5 reports , use ssrs security control can see report. behind scenes, each of 5 identical reports use different service account on ssrs data source connect ssas. each use granted access 1 of 5 reports , report connect using 1 of 5 service accounts ssas , role based security in ssas show appropriate data 1 service account.

if that's not acceptable might consider using dynamic security using customdata since can't have domain users connecting ssas on domain b.

typical dynamic security uses 1 role mdx expression , username() mdx function lookup current user can access: http://hccmsbi.blogspot.com/2007/08/implementing-user-specific-security-in.html

your situation since can't connect ssas on domain b domain users, use customdata() mdx function instead of username() mdx function. may have use expression based connection string in ssrs. hardcoded connection string like: data source=ssasserver.domainb.local;initial catalog=yourdb;customdata=joeblow

the 1 role have have domainb\serviceaccount member. internally use customdata connection string property figure out "who are" , dimension members can see.

as far excel, i'm thinking setup 5 msmdpump virtual directories , in iis grant each domain user access one. however, i'm not clear on how work because don't know how setup iis impersonate domain b service account when connects ssas. had thought through this?

let me know if doesn't make sense. again, recommend moving ssas domain a.