Strategy for the Data Gateway Connection to our Data Warehouse

[hs28]

Our current connectivity to the on-premises Data Warehouse from Power BI online service introduces a data security risk by using a connection with wide range of privileges on the data. This connection is shared with a lot of users that have different level of access. While this approach has worked in the past, with the introduction of service like Data Flow, Data Mart and Copy Data in Fabric, the shared connection introduces a risk of exposing all the data to users authorized to access only a subset of it on the DWH.

What is the recommended approach for managing connections from Power BI online service to data warehouse with self-service analytics approach? 

Please note: This is a concern only for the Data Gateway Connection to our Data Warehouse. The rest of the data sources have data gateway connections that are shared only with a limited number of people that have the same access rights.

[v-yohua-msft · Accepted solution]

Hi, @hs28 

Implement RBAC to ensure that users only have access to the data they are authorized to view. You can create different roles in your data warehouse and assign them to users based on their access needs. Instead of using a single powerful service user, create multiple connections with different access levels. This allows users to connect to the data warehouse with credentials that provide only the required access to the data.
You can also use an on-premises data gateway in Enterprise mode, which supports live connections and DirectQuery operations. This allows the gateway to be centrally managed and monitored, ensuring better security and control. You can check the following link:

Power BI usage scenarios: Managed self-service BI - Power BI | Microsoft Learn

Or take advantage of Power BI dataflows and data marts to preprocess and store data to share necessary data in a more controlled way to limit data exposure. You can check this link:

Connect to cloud data sources in the Power BI service - Power BI | Microsoft Learn

Use parameterized queries to limit the returned data based on user input, ensuring that users can only retrieve the data they are authorized to view. Implement audits and monitoring to track who is accessing what data, helping you quickly identify and resolve any unauthorized access.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[hs28]

@lbendlin Thanks for getting back. The issue is that the service user that we use behind this connection is very powerful (has access on all data). 

[lbendlin]

yes, oversharing is a constant risk for gateway connections. The only remedy is credentials pass through, and only for Direct Query connections.  In import mode you have no choice.  In that case data protection/scoping has to be done in the downstream system.

[hs28]

@lbendlin Yes, since Single sing-on is not possible for import queries it makes our case very challenging. Thanks for your input.

[lbendlin]

Does your on-prem data warehouse connector support credentials pass through?