On-Premises Data Gateway - Options for Authenticating To Analysis Services Via Service Account

[weightj]

My issue is related very close to this post:

https://community.powerbi.com/t5/Integrations-with-Files-and/On-Premises-Data-Gateway-Options-for-Au...

Here is the contents of that post:

"Is it possible for a user to view a Power BI Report that is connected to a Analysis Services Datasource via the On Premisise Data Gateway, but NOT have access to the datasource itself?

I would like to explain our situation / issue to see if I am understanding this correctly and if anyone has any suggestions.  We have a Data Cube on Anlysis Services that has complex and sensitive data in it.  We have several "Power Users" in our company who understand the data well, and want to build reports / dashboards off of it, that they can share with their departments ("End Users").  The Power Users are the only ones who we want to give access to the Data Cube, because we don't want an end user to be emailed a spreadsheet and change a report, as they could create an incorrect report, by removing an important filter for example, and make decisons based on incorrect data.

We were hoping this is where Power BI could help us.  We have built some reports in Power BI.   The Power BI Reports are connected to the Analysis Services Datasource via the On Premisise Data Gateway.  We then shared the report with the end users via an App.  The end user has a liscence to Power BI Pro, access to the App but not the data source.  The Gateway Data Source is using an Admin Account for authentication.  

When the end users opens the Report from the App, none of the visuals display for them, I beleive because they do not have access the datasource.  

Is there anyway, that we can have Power BI authenticate to the data source with differerent credentials than the user has logged in to Power BI with?   We want to share the data with users on our terms, we don't want the users to be able to get access to the data and build their own reports off of it.  If we give access to the Data Cube for Power BI reasons, they could potentially connect to the same data source via Excel, which we don't want."

Here is the additional piece I am trying to figure out:

What about in the case of doing row level security? For instance if I change the mapping of the user running the report to a domain service account (same idea as presented in the referenced posted ealier but not referencing a power user name) that has access to the cube in a certain role that has Dynamic RLS logic. The system will not recognize the user running the report, because the service account will be mapped as the EffectiveUserName. I had the thought well if I can map a service account then I could pass in the username of the person running the report via the CustomData field and configure the Dynamic RLS logic to reference the CustomData field since it would contain the username running the report. I am currently able to do this in SSRS 2016 in a shared data connection. I cannot tell the gateway to send the customdata field the connection string, since I put a mapping rule on the Effective user name to map the user to the domain service account. Thoughts? How can I accomplish this issue?

Environment:

SSAS 2017

On Premise Data Gateway (December 2017 Release)

Have a local domain

[v-yuezhe-msft]

@weightj,

Do you connect to the SSAS data source using the domain service account under gateway? If so, does the account have Server Administrator permissions for the SSAS instance you are connecting to? And how about you share dashboard to end users after using map user name feature?

Regards,
Lydia

[weightj]

Following up to see if you have any ideas. Currently I am going to try this option: https://www.youtube.com/watch?v=NG05PG9aiRw

[weightj]

@v-yuezhe-msft,

There are two different service accounts in use in this scearnio. The first one resides on the connection side of things on the Gateway settings (ie. Type of connection, server name, DB name, credentials).  This account has SA to the SSAS server instance which is required to even connect using the gateway. The second service account is one that is assign within a role on a Tabular cube. From there is where the permissions, RLS, and object level rules apply. This is part where I am inquiring to see if it is possible to map any user to this service account on a specfic gateway connection (which you can), but allow the ability to pass in the users whether it be UPN or domain name into the connection string and the build the RLS rules to reference that piece of the connection string to then invoke dynamic RLS rules. I have been able to do this on the SSRS side of things. I use a shared data connection by using the service account and passwords as the login credientials, but then I am able to pass the username of the report via CustomData field which is allowed in SSAS 2016 and after. Here is an example of the connection string on SSRS shared connection (Data Source=servername;Initial Catalog=DB Name;CUSTOMDATA = {{ USERID }}) and then have below in the credentails the domain\service account as the login credentials.  (Below is an example of a shared data connection in SSRS 2016)

Again to stress why we would want to do this. First, we do not want users to have direct access to the cube, because it would give them the ability say through excel or Power BI desktop to connect to the cube and be able to write reports. We only want to have ability to have the users view the data via a report that is shared to them and data that is only applicable to them (hence the dynamic RLS ability). This is a concern because this data can be very sensitive. Second, is just the ability to avoid large maintenance needs. The need to create and apply new roles and assign users all the time, is a business we do not want to get into, since many reports have a wide breathe of users.

Thanks,

Josh