A few weeks ago an important new feature for managing connections to data sources in the Power BI Service was released: Shareable Cloud Connections. You can read the blog post announcing them here. I won’t describe their functionality because the post already does that perfectly well; I want to focus on one thing in particular that is important for anyone using Power BI with Snowflake (and, I believe BigQuery and probably several other non-Microsoft sources): Shareable Cloud Connections allow you to have multiple connections to the same data source in the Power BI Service, each using different credentials.
Some of you are going to read that last sentence and get very excited. Many of you will probably be surprised that Power BI didn’t already support this. To understand what’s going on here you first have to understand what Power BI considers a “data source”. The answer can be found on this page of the Power Query SDK docs:
The M engine identifies a data source using a combination of its Kind and Path […]
The Kind value comes from the Data Source Kind definition.
The Path value is derived from the required parameters of your data source function. Optional parameters aren’t factored into the data source path identifier.
In the case of the Snowflake connector, the “Kind” of the connector is Snowflake and the “Path” is the determined by the two required parameters in the Snowflake connector, namely the Server and the Warehouse:
Before Shareable Cloud Connections, unless you used a gateway, you could only use one connection with one set of credentials for each data source used in the Power BI Service. This meant, for Snowflake, you could only use one set of credentials for all datasets that connected to the same Server and Warehouse, which led to a variety of problems like this one where different credentials were needed for different Snowflake databases or like this one where one user would publish a dataset and enter credentials that worked for them and then a second user would publish another dataset, enter different credentials for the same Server/Warehouse combination and break refresh for the first dataset. With most other popular connectors these issues were rarer because their Paths are more specific and aligned to how you’d want to use different credentials.
As I said, Shareable Clould Connections solve all this by allowing the creation of multiple named connections to the same source, each of which can use different credentials. As a result I strongly recommend everyone using Snowflake with Power BI to create new Shareable Clould Connections and use them in the Power BI Service.
Not only Snowflake, you could have the issue with Azure SQL DB as well when you connect with different SQL Server users. I used a “work around” where one connection had the database name in capitals and the other did not, as PQ is apparently case sensitive.
I think this feature would be great if it respected organizational policies and gave admins an option to determine who can create and share these connections. We’ve limited the ability for people to register gateways in our tenant to data stewards within our business lines for this exact reason – we don’t want anyone to be able to create a connection to a data source they have access to and share that with anyone they want even if they don’t have access to the same data source. It’s a backdoor around security implementation for all kinds of applications. Just because the people to whom the connection has been shared don’t possess the secret is irrelevant.
Delegating your personal OAuth credentials to other people is so obvious of a security hole I am shocked this feature just showed up one day for datasets and dataflows with no ability to turn it off or monitor who is creating them and who they are shared with. Why even bother with security in SharePoint or SQL Azure if one person with access can share to thousands?
Anyone can create them and share them with anyone. There are no (documented) APIs that allow us to retrieve a list of these connections and who they’ve been shared with like there are for gateway connections. Feature first, governance later when it comes to something so critical to information security like access to data won’t work for enterprise organizations.