Security and the Enterprise Gateway22 februari 2016 2016-02-22 13:24
Security and the Enterprise Gateway
While upgrading from Analysis Services Connector to the Power BI Enterprise Gateway,
you might notice a change in authorization structure. Here's a brief description of the differences you need to know when upgrading. I thought there was a change in authorization structure, but I had didn't fully understand the way Analysis Services Connector authorizes its users.
Power BI Enterprise Gateway authorization
With the Enterprise Gateway, authorization is first handled in Power BI: the administrator of the Enterprise Gateway in Power BI determines which data sources are included, and who gets access to these sources (1). Then, the Enterprise Gateway connects with Power BI using the stored credentials (2). Initially I saw this as a serious security limitation (because you don't know who's querying on the other side), but Bill Anton (@SQLbyoBI) pointed me at the fact that in the connection towards SSAS the "effectiveUserName" is included, which can be used for row-level security.
@kvstrien agreed - not ideal, but at least provides ability for dynamic row-level security via EffectiveUserName...
— Bill Anton (@SQLbyoBI) February 16, 2016
As you can see in the SQL Server Profiler log below, this is indeed the case: connection is done using the Service Account, while the querying user is passed on via EffectiveUsername:
Analysis Services Connector authorization
Initially, I thought the Analysis Services Connector acted only as a bridge, while all connections to SSAS were made using the querying Power BI user's account. As it turns out, I was wrong - evidence can be found in the docs over here:
- Analysis Services Connector: https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-analysis-services-connector-indepth/#making-the-connection
- Power BI Enterprise Gateway: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise-indepth/#making-the-connection
Thanks to @DimahZaid as well as @SQLbyoBI for pointing this out 🙂
— Dimah Zaidalkilani (ديمة) (@DimahZaid) February 23, 2016
Main differences between Analysis Services Connector and Enterprise Gateway are therefor:
Analysis Services Connector accesses one specific SSAS instance. Enterprise Gateway can host multiple connections to different data sources
- Enterprise Gateway stores credentials per source. A source can be SSAS, but also any other datasource. Connection details (including credentials) are configured in the Power BI web app
- Analysis Services Connector asks only once for the credentials, during the setup wizard. After that, they're invisibly stored, but used nonetheless for accessing the SSAS database.
After all, I think the way the Enterprise Gateway works provides way more insight in the security handling, which sources are in use, accounts are used etc.
UPDATE (Feb. 24th) - as John White points out (http://whitepages.unlimitedviz.com/2016/02/power-bi-enterprise-gateway-and-ssaswhat-account-should-you-use/), in order to use the EffectiveUserName, the proxy account (the stored credentials inside Enterprise Gateway) needs to have the 'Analysis Services - Administrator' role.
— John White (@diverdown1964) February 24, 2016
- When using the Enterprise Gateway, access control is granted per user (inside Power BI, the Gateway "administrator" / "owner" can grant PBI users access)
- Connections towards SSAS are made using the credentials stored inside the Enterprise Gateway[ref]Analysis Services Connector also used stored credentials, but after you entered them in the setup wizard, you could simply forget you once entered them[/ref]
- Username of the querying user is passed via 'EffectiveUserName'