October 23, 2017 at 1:15 pm
I need to build / implement a tabular model in SSAS where users can execute / browse reports in Power BI and SSRS, with row level security. I've got that part under control. But SSAS still lets me down in a very important way.
In my organization, there are some very sneaky users. If they found out the name of my SSAS server, they would go around me and start using my model to create data dumps and do whatever they want, with no governance or validation procedures. My bosses see that as a risk.
So I need to be able to publish reports to all users in my company using roles to govern row level security, but if those same users figure out my SSAS server name, they SHOULD NOT BE ABLE TO BROWSE MY TABULAR MODEL TABLES IN ANY OTHER TOOL SUCH AS EXCEL OR POWER BI DESKTOP. It seems like this is not possible, even with the new "metadataPermission": "none" property in the latest version of SSAS.
The property name "metadataPermission" is misleading. It should be called "dataPermission" instead. I assumed, incorrectly, that setting "metadataPermission = None" on a table would block sneaky users from seeing it in Excel, PBI Desktop, etc. while STILL BEING ABLE TO CONSUME REPORTS THAT DEVELOPERS WROTE USING THE SAME TABLES. Instead, they cannot see the tables or the data when I turn this on, so it's useless to me.
I am still unable to use SSAS because of this shortcoming. Instead, I have to use Power BI native models, because with those I can use workspace permissions and apps to publish only what the end user sees, and the end user has no way to steal the source data. I wish I could use SSAS instead.
Is there something I'm missing, other than maybe switching to a job where corporate IT doesn't have such a contentious relationship with divisional power users?
October 23, 2017 at 2:05 pm
Pretty sad situation if you go to all the trouble of building SSAS cubes, but you're not able to provide direct access to your end users.
If you use native Power models, end users will still have access to your data model through the "Analyze in Excel" feature...so you're not "winning" by moving to native Power BI models (which is really SSAS tabular behind the scenes anyways). If you really need to restrict direct access to the cubes, I think the only thing that would give you the ability to do that would be firewall rules, denying any connections made by end user machines.
October 23, 2017 at 2:43 pm
Martin Schoombee - Monday, October 23, 2017 2:05 PMPretty sad situation if you go to all the trouble of building SSAS cubes, but you're not able to provide direct access to your end users.If you use native Power models, end users will still have access to your data model through the "Analyze in Excel" feature...so you're not "winning" by moving to native Power BI models (which is really SSAS tabular behind the scenes anyways). If you really need to restrict direct access to the cubes, I think the only thing that would give you the ability to do that would be firewall rules, denying any connections made by end user machines.
Thanks for the input. Actually, Power BI models can have "Analyze in Excel" turned off globally in the admin portal. So that's not an issue. I like the idea about the firewall rules. I'll definitely check that out! Thanks!
October 23, 2017 at 2:58 pm
You may be able to get around it on SSRS reports (and eventually powerbi but not sure as Tabular requires Windows Authentication)
1 - Do not grant users access to Tabular
2 - Setup a generic user on SSRS with access to the tabular instance and setup your reports to use this user instead of the user running the report
3 - For each report Grant permissions to the users that need them - you may need to grant at a user level or to setup groups per block of reports - but that is standard SSRS
Long shot and not necessarily the best/unique solution
February 9, 2021 at 12:20 am
Did you happen to arrive at any solution for your problem?
We arrived at a solution (almost) using customdata attribute of the connection string. We would basically pass a "secret code" as part of the connection string using the customdata property and in SSAS leverage that code to enable/disable access along with the row level security logic.
So in all the reports we would pass the magic code via the connection string so the user would see the data they should see. However, if the same user were to access the model via Excel or any other tool, he/she would see nothing because the "secret code" was not present or did not match.
However, the reason we use this method was because SSRS reports were correctly passing the customdata value to SSAS but Power BI reports were not :(.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply