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

Anonymous,

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