Row Level security

[DanielFountain]

Hey all,

I am getting a little confused with row level security.

So i understand you can only implement this if you use SSAS is that correct?

I dont understand how this works - so could someone shed some light.  If i connect to SQL and make a dashboard and publish it - the data held within will be all the data that i had published as a dash.  If i share that to someone they will see all the data.

Now lets say hypothetically - i make a tablular cube.  I have full access to the data.  Other people have restricted access.  So i publish said dashboard - now surely when i do that it will publish with all the data?  Then if i share it will not everyone still see the data?

OR does it not store the data in the cloud (like if you connect to SQL) and forces a refresh for each person that views the data?

I hope this makes sense,

Dan

[Ray]

I have simiar confusion, my dataset tables came from a web call. Now how can I implement a security restrictions per each employee of my organisation? So each user has a certain level of viewing the dashboard!

I am not using SSAS or SQL server as an external data.

Can someone help me with that?

Thanks,

[greggyb]

Currently, the only way to implement role-based security is with SSAS in a live connect mode. This is the only data source connection in the Power BI Service that passes EffectiveUserName to the source to be evaluated for role-based access.

Anything else will use a stored credential, so the most granular security enforceable is binary: whole dataset access (based on security of stored credential) or no dataset access.

[greggyb]

---

@pmorris

@greggyb wrote:

Currently, the only way to implement role-based security is with SSAS in a live connect mode. This is the only data source connection in the Power BI Service that passes EffectiveUserName to the source to be evaluated for role-based access.

Anything else will use a stored credential, so the most granular security enforceable is binary: whole dataset access (based on security of stored credential) or no dataset access.

---

This may change in the future, but it's as accurate today as it was 2 days ago.

[dpetrancuri_LIP]

There is an unacceptable and glaring defect in the implementation of the Enterprise Gateway to SQL Server 2016 / Azure SQL Database.

Row level security in both environments can rely on the actual user connection to the database or to information provided via SESSION_CONTEXT.

What needs to happen is that the enterprise gateway must determine the database platform is has connected to (after establishing the connection), and if it determines the platform is SQL Server 2016 or Azure SQL Database it needs to set the SESSION_CONTEXT, providing the value of the key 'UserID' from the encrypted credentials that were passed from Power BI (desktop or service) to the connection. 

This means that the enterprise gateway must establish a separate connection (which hopefully it already does) for each 'end user' signed into Power BI. 

To me, this is a glaring case of the Power BI team not understanding the capabilities of SQL Server 2016 and the teams not speaking to each other.

The fact this is properly implemented for SQL Server Analysis Services and not for SQL Server 2016 or Azure SQL Database is absurd and patently unacceptable. How can MS be touting Power BI as an enterprise BI delivery platform but not support this feature is beyond me.

And, to take it a step further, I frankly don't understand how Azure SQL Data Warehouse does not yet support row level security when you consider that it is designed to be an MPP relational data warehouse that should eliminated if not dramatically reduce the need for Analysis Services. Query performance provided by the MPP architecture and the use of Clustered ColumnStored Index as the default storage model for tables in Azure SQL Data Warehouse allows for queries dependent on millions of rows potentially needing to be aggregated in real-time to be processed efficiently with generally acceptable end-user response times using this ROLAP model. Given such, how can Azure SQL Data Warehouse not have support for row level security at this time? Microsoft can't truly intend organizations to HAVE to use Analysis Services simply to provide row level security at the data level when there is no additional financial benefit to Microsoft to do so.

[adcolani]

@dpetrancuri_LIP were you able to get around this and use RLS in SQL Azure from Power BI with Direct Query?

Or this is still not supported and we have to either implement RLS in Power BI or in Azure AS?

[sekic]

I can't implement RLS in service (only desktop)....

I can't call parmetrized table aka table function which get any (beside constant) from Power BI...

I can't get user ID or any similar user info,  into M language in advanded query editor...

All tables/views in SQL have some form of User ID identification (email, domain name or similar) but it is unusable in Power BI to "send" to filter.

What can I do? After three weeks I'm on way to give up from Power BI mostly because of this particular limit.

[MateuszBI]

What is workaround to give users access to their data only? 

[greggyb]

@MateuszBI, use the Enterprise Gateway to connect to SSAS, either Multidimensional or Tabular. This is the only way to enforce RLS currently.

If you want to, if you have N users, you could create N datasets that are each filtered to only a specific user's data, and then each user only has access to "their" dataset. This would be awful for N>3 if you ask me.

[gastonguy]

As SQL Server 2016 and Azure SQL DB both support now Row-Level Security, it can work the same way as with AS, using DirectQuery instead?

[pmorris]

This Microsoft Power BI Security document states that if the underlying data source supports RLS, the data will be refreshed using the current user's credentials. Go to page 15 where it says this:

If an underlying data source is capable of Role Level Security (RLS), the Power BI service will apply that role level security, and users who do not have sufficient credentials to access the underlying data (which could be a query used in a dashboard, report, or other data artifact) will not see data for which the user does not have sufficient credentials. If a user’s access to the underlying data is different from the user who created the dashboard or report, the visualizations and other artifacts will only show data based on the level of access that user has to the data.

 Has anyone tested this and confirmed?

[Seth_C_Bauer]

@DanielFountain for an example of what you need to do in order to filter what the end users see you can reference this documentation on how to apply this. Essentially you need to create dimensions that will filter the what the end users see.

https://msdn.microsoft.com/en-us/library/hh479759.aspx

[Bjoern]

Access rights are a really interesting and complex things. Especially for Microsoft BI tools it's like one of the most complicated things in the world. 🙂

(I will set the scene with some general info).

In general you can restrict user's access via 2 ways:

1. Functional restrictions: Which user is allowed to see which reports/dashboards and also, how are they allowed to work with the reports.

2. Data access restrictions: Basically, certain rows in your data are only accessible for specific users. The data access concept depends on the database/source you want to use. For SSAS MD it works pretty good. For SSAS AS Tabular it is a more complicated. It even gets more complicated if you want to directly connect to SQL on-prem (row-level security will be enabled in SQL2016, AFAIK). For MS Azure SQL DB (PaaS) it already works now.

In either of these concepts, you have to define which user is allowed to see which masterdata (e.g. sales regions, departments, products ...). The database then automatically calculcates the respective measures (e.g. sum()), as only the "permissioned elements" are visible for the tool. These access lookup tables have to be defined before publishing the source.

Please be also aware, that only a "functional restriction" will not be sufficient to restrict the access, as the users would still be able to get the data, if they know name/address of the server. Thus I definitely recommend using a combination of 1. and 2.. 

If you connect to the sources you also always have to use the direct connect, as otherwise the data is just pulled into the pbix file. A pbix-file does not have sophisticated data restrictions. Thus, you must use the live connect via Windows Logon. The user connects to the data and is authenticated at the database (either AS Tabular, AS MD, Azure SQL DB, SQL DB on-prem, ...). The query then shows the respective users the data they are allowed to see and not more than that.

Please be also aware that especially if you are working with hierarchies it can get pretty complex pretty fast.

// Please be also aware that PowerBI caches data in the cloud if you use the web-service.

[fredrikg]

@Bjoern: Just trying to understand what is supported and what is not.

You write:

"The user connects to the data and is authenticated at the database (either AS Tabular, AS MD, Azure SQL DB, SQL DB on-prem, ...). The query then shows the respective users the data they are allowed to see and not more than that."

Is that really supported against anything else than SSAS Tabular or MD? Specifically, I have been searching for a way to get row-lowel security against a SQL Server database (without SSAS involved).

The closes I have come is using DirectQuery + Enterprise Gateway, but I can't see a way to filter data depending on the current user - simply because the current user is not represented in the call to SQL Server. The Enterprise Gateway uses the same user (specificed in Power BI Service->Manage Gateways->...) for all calls, independing on the current user.

Am I missing something here?

/Fredrik

[LenWright]

I made a SQL 2012 connection to my data for a direct query, and defined the SQL credentials as Windows Authentication, not SQL authentication. Then when I went into my SQL data source, a View, and included the SQL USER_NAME() function in my returned results, and i was able to see my windows authentication in the Dashboard dataset using power BI Desktop.

But I did this with the free version (since I'm still evaluating), and when I published it to the service, it failed of course, since direct queries are not allowed in the free version.

Otherwise, with the PRO version, this will allow you to join your results by user name and effectively create row-level security.

[Bjoern]

@fredrikg

To be honest, I do not know for the enterprise gateway for sure so far. For the AS Connector it does work (it uses the logged on username). So I would have supposed that it will also work for the "Enterprise Gateway of SQL". 

Have you checked the calls via the SQL profiler or is it just your guess based on the SQL Enterprise gateway settings?

// And, as far as I know RLS only works for SQL2016! 

[fredrikg]

Yes, I checked the SQL:BatchCompleted calls. The "LoginName" and "NTUserName" both show the same user - this is the user I have specified for authentication in Enterprise Gateway's datasource connection settings.

No sign of the actual end-user identity.

[Bjoern]

double post, see below

[Sacha · Accepted solution]

Hi Dan,

You need to utilise the Analysis Services Live Connector for this scenario.  You can find more info here:

http://blogs.msdn.com/b/powerbi/archive/2015/03/11/power-bi-analysis-services-connector-deep-dive.as...

In this arrangement, no data is stored in the cloud.  Power BI converts your request to a DAX query, and sends that query (along with your AD info) to the Tabular model who duely executes the query applying the appropriate Row Level Security.

Hope this helps

Sacha