Not setting SESSION_CONTEXT when connecting with Power BI Enterprise Gateway - NO RLS in SQL Server 2016 as a result

[dpetrancuri_LIP]

The Power BI Enterprise Gateway does receive encrypted individual credentials from the Power BI Service for the user logged into the service, but it does not set the SESSION_CONTEXT in SQL Server for the the user logged into the service. As a result, SQL Server Row-Level Security does not work as expected when connecting from the Power BI Enterprise Gateway because a user as defined in the administration of the gateway is being used for all users. Since the encrypted credentials of the user logged into the service are being passed to the gateway, which is stated in the documentation for such, it seems to me that it should be an easy change to the gateway to determine which database platform the connection has been established with, and after the connection is established, if it is to SQL Server 2016, SESSION_CONTEXT should be set. It is not reasonable to expect RLS to be defined in the Power BI Models when using DirectQuery to a SQL Server database. Centralized management and governance of application level data security is at the core of the rationalization of SQL Server RLS. A modern client application to SQL Server 2016, such as Power BI through the Enterprise Gateway *MUST* support SQL Server RLS. Perhaps the only setting regarding such from the perspective of the Power BI Enterprise Gateway should be whether or not to set SESSION_CONTEXT. Failure to address this critical deficiency in the very near term damages the viability of Power BI as an enterprise BI tool if it does not support a key feature of SQL Server 2016. Furthermore, failure to do so further underscores a lack of communication between product development teams across different components of the Microsoft BI stack, which SQL Server is no doubt a cornerstone of.

[dpetrancuri_LIP]

This is not a SQL Server problem. It is a problem with the Enterprise Gateway NOT setting SESSION_CONTEXT when connecting to a SQL Server 2016 database. The user name which is used to sign into the Power BI Service is passed as encrypted information from the service to the gateway. The implementation of the gateway is incomplete in this regard. Please do not assert this is a problem with SQL Server. It is not. SQL Server 2016 specifically was designed to accommodate scenarios such as those of the Enterprise Gateway were a single account is used to connect to SQL Server yet information can be provided via SESSION_CONTEXT to allow for information such as the application user (in this case the user signed into the Power BI Service) to be made known to the SQL Server connection through the use of SESSION_CONTEXT as described here: https://msdn.microsoft.com/en-us/library/mt590806.aspx?f=255&MSPPError=-2147217396 Failure to FIX this is extremely adverse to the enterprise use of Power BI for reasons stated in the original post.

[chass]

Hello Darryl, SQL Server has not implemented effective user name to work with Direct Query so it can use SQL RLS...yet. Did you want me to move this to the suggestions forums? Thanks Chuck

[messex1]

I'm in 100% agreement with Darryll on this. We are holding back our rollout of PowerBI to our user base because of this. Using Roles in PowerBI isn't viable for us, and the performance is greatly affected by this. We have all this work in SQL to manage RLS, and yet we can't use it with Power BI. I think this should be a top priority for Microsoft, and I would think that including SESSION_CONTEXT would be fairly 'easy' to implement.