Power BI Enterprise Gateway fails to set SESSION_CONTEXT when connecting to a SQL Server 2016 database

[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. 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 where 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

[dpetrancuri_LIP]

It is up to the SQL Server application architects / developers of the database used as the data source for a DirectQuery to implement the Security Policy and Security Function that will cause proper predicate and block filtering to be enforced by the SQL Server Query Engine. It is the responsibility of the Enterprise Gateway to set the SESSION_CONTEXT after connecting to the SQL Server database (and determining the database server version since SESSION_CONTEXT only applies to SQL Server 2016). I understand this means that each user of the Power BI Service who uses a Power BI Model that is dependent on DirectQuery would need their a separate connection to SQL Server 2016 established through the Enterprise Gateway. The connection would still be made using the user established by the Enterprise Gateway administrator but SESSION_CONTEXT would be different for each user signed into the Power BI Service in this case. This fact should not serve as a blocker to fixing this critical defect in the implementation of the gateway

[tim65]

Excellent Idea. We are trying out PowerBI embedded to an Azure database that has RLS implemented via Security Policies and SESSION_CONTEXT. I don't think you would need to modify the connection string for User Id. The User Id value could be passed in the Access Token passed into the report execution.