Dataflows & Gateway Permissions

[theBIbutler]

Hi,

We have a SQL Data Warehouse...users have access to it which restricts them to be able to see certain data.  They can build reports and publish them to the service.

We have a Gateway set up and have created a datasource pointing to the Data Warehouse using credentials that can see everything.

We give relevant users access to the gateway datasource so that they can schedule refreshes on their datasets.

Problem I'm seeing is if they were to create a dataflow connecting to that server & database they can then see everything in the datawarehouse, it isn't restricted as they can use the gateway datasource to connect.

Is my understanding correct?  How could this be avoided other than by trying to implement SSO or is that the only way?

Thanks

[dhruvinushah]

Hi @theBIbutler ,

From what you have shared - I believe the users are able to see everything because the credentials used for the gateway are allowing all data to pass into the power bi service. So if the end user were to use that Gateway to create a Dataflow that connects back to your SQL Server & Db, it will bring back everything. 
Best practice here would be to build a dataset with RLS and share that with end users. Let them build reports off the dataset instead of them creating a dataflow to get access to the data. 
RLS will ensure that the data gets filtered and users can only see the data that they have access to. 
How to implement row level security (RLS) in Power BI [With Examples] - SPGuides

Hope this helps, tag me (@) if you need more help on it. Feel free to mark the answer as a solution if it helped you.
Thanks!

[theBIbutler]

Thanks, but if only it were that simple!

This is a 'legacy' warehouse that has hundreds of tables spanning lots of different business areas, not feasible to combine it all in one nice dataset.  The analysts using it are used to connecting directly to it and then adding data from other sources to build their datasets and reports. 

I want them to be able to create dataflows but ideally not from this data source.  I wish there was a way of specifying in the Gateway Data Source User permissions whether to allow use for Dataset Refresh and/or Dataflow use. 

I think there is an issue with the current documentation; it only mentions dataset refresh when creating users, there is no mention that it will also allow dataflow use too.

[dhruvinushah]

Hi @theBIbutler ! 

Your analysts should build their reports and datasets in Desktop by collating the data sources as they normally do. When you publish to the service, the Gateway's job is to connect the existing queries to it's source using the stored credentials in the pbix file. The credentials used will be the same as the one the team of analysts currently use to build the report in Dekstop. 

As of this date, Dataflows are currently not able to do what you would like. You can try to use the method I suggested and wait for future product releases and watch the idea blog to see if it gathers any traction.

Hope this helps, tag me (@) if you need more help on it. Feel free to mark the answer as a solution if it helped you.
Thanks!

[theBIbutler]

Thanks, that's what the users are currently doing, the problem is that by having user access to the gateway data source they are also able to create dataflows that use the gateway data source to gain access to the whole warehouse.

Your line:

"The credentials used will be the same as the one the team of analysts currently use to build the report in Dekstop."

The analysts each have their own credentials...so are you saying we should create gateway data sources for each person?