Restrict/Prevent ad-hoc conections to SSAS Models

Question

We have several SSAS data models and folks connect to those typically via Power BI Report Server Reports or SSRS reports that we have developed. All users are expected to use the dashboards/reports only.

However, there are some users who are aware of the SSAS server name and connect/pull data from it via Excel, SSMS or other means. This has caused serious issues sometimes as those users end up pulling information incorrectly.

We want to restrict folks from being able to connect to the data models directly except of course via our reports/dashboards. We would still like to have the ability to connect to these ourselves i.e. our core team (This is ideal but can be lived without if required)

Is there a way we can achieve that?

Answer 1

There is no built-in mechanism in SSAS to restrict access based on the type of clients tool.

Off the top of my head I can think of a couple of potential solutions.

1. You could talk to your networking team and get them to restrict access to SSAS. This would probably also restrict access to your team since usually these sorts of rules are setup based on fixed IP addresses and subnets and those sorts of things are usually only assigned to servers. Although for this to work you would need to have another SSAS server, for a dev environment where your team can connect to create/edit reports.
2. If you are not using Row Level Security or Object Level Security in any of your models then you could possibly remove all end users (apart from your core team) from all security roles in your models. Then create one or more "service" accounts and put them in the roles and set the credentials for those in your Report Server data sources. This has the downside that anyone with access to the report can run it so you need to be more careful about the security in the Report Server layer. But this would block ad-hoc connections for most users while being able to maintain it for your core team.

Answer 2

Hi,

We could not control the client tool and forbidden certain softwares from connecting.

My ideal is to change the tabular server password, if the normal user only need to connect to data through SSRS/PBIRS, this is no need to share them the SSAS server permission and password.

They only need to have SSRS/PBIRS logon account and password, with which they could log on to SSRS PBIRS web portal and browse the report.

Regards,
Lukas

Answer 3

This method did seem to work but only for SSRS reports and Excel. PBIRS reports did not seem to pass the CustomValue to the model's row level security logic though.

Unfortunately you cannot add any additional parameters to the connection string for PBIX based reports. The SSAS connector only accepts the server name and database name, any other options are ignored. So sneaking something into the connection string is not an option for those.

Answer 4

I actually ended up trying it again and it worked like a charm with PBIRS reports too.

Basically 2 steps:-

1. Add && CUSTOMDATA="SomeCodeWord" to any existing row-level Security logic you may already have in the model
2. Add CUSTOMDATA=SomeCodeWord to the connection string. For PBIRS and SSRS you will basically need to update the connection after publishing/uploading the report in PBI Report Server.

The customdata value can be added in Excel, DAX Studio, PBIRS, SSRS reports etc as needed. If it's not passed then user does not see any data. If it's passed, user sees the data he/she has access to as per the RLS security.

Essentially, unless someone knows the "CodeWord" they can't access the data from any other application.