cancel
Showing results for 
Search instead for 
Did you mean: 

Making SQL Connector Secure

Problem

The biggest problem with developing PowerApps with Azure SQL Database is that we have to share the SQL Connector with each user of the app.

What that means is that each employee can bypass the app by creating their own app and adding this connector to their app (since it is shared). They get the ability to see all the tables and views in the database. Basically, everything there is in the SQL database, on top of that they also get the ability to edit the information in any way they please.

This is not an issue for non-confidential information and simple apps. However, we have plans to develop more complex apps with data that should not be seen by everyone who will be using the app. PowerApps is great as we can build custom logic on who sees what. However, since each employee can create a fake app and throw in the SQL connector that was shared with them, this means that all the security and complex data validation built in the app becomes useless.

 

Idea

I think the simplest solution would be to make the SQL Connector when sharing it, the user gets “Can use” permission, it would be great if we could give an even lower permission level like “Can use only in this App”. This would make it impossible for them to create fake apps and throw in this SQL Connector to see data they are not supposed to see.

OR

Another option would be that when user has “Can use” permissions on SQL Connector they would only be allowed to use it where the owner put that SQL Connection, making it impossible for them to drop this connection in their Apps or Flows.

 

 

Either one of those solutions would make PowerApps a lot more useful for a large number of corporations. This would definitely push PowerApps adoption for more complex systems and bring it above other similar platforms out there.

Status: Under Review

Catching up to this discussion and updating the status. We are looking at adding additional auth models for SQL. In the meantime, as many posters have pointed out here, the solution is to create your app in an environment other than the default environment, where you can control who can build apps and thus reuse the connection. Separating apps by environments is a best practice regardless.

 

Regarding discussion here on using Gateways in the non-default environment, per comments here this is possible today by filiing a support ticket and giving us some context to evaluate the request.

Comments
Level: Powered On

Since I initially recognized this issue, it seems to have gotten WORSE actually.  I don't know if this is standard for all organizations, but for my org, model-driven apps are auto-shared to the entire organization.  At least with canvas apps, it's limited to your shared selection of people.  As such, god help you if you create a model-driven app with a SQL connection.  Now your entire organization has access to your data source.

Level: Powered On

How will this "environment trick / workaroud" work with the new PowerApps per App licensing?

In this article ...

https://docs.microsoft.com/en-us/power-platform/admin/powerapps-flow-licensing-faq 

... it says ...

Each “per app” license provides an individual user with rights to two apps (canvas and/or model-driven) as well as one PowerApps Portal, all within a single environment.  

... and ...

Effective October 1, 2019, the SQL, Azure, and Dynamics 365 connectors listed below will be reclassified from Standard to Premium. Non-Microsoft connectors that had previously been classified as standard connectors will still be available to Office 365 users. A standalone PowerApps or Microsoft Flow plan license is required to access all Premium, on-premises and custom connectors ... SQL Server ... Azure SQL ... many others

So will Office 365 users with PowerApps but no specific PowerApps plan not be able to use the "environment trick / workaround" after the cessation of P1 and P2 type licensing (end of subscription, 1st of some month or other, or whenever it is) unless they have a PowerApps per App Plan or a PowerApps per User Plan for the environment in which the app is resident - in which case they could copy the app in the environment (because they have a Plan for that environment) with the connector, and thus gain access to data they shouldn't have access to?

Level: Powered On

Hey all, good news, it seems they've come up with a solution, which is to force everyone to stop using SQL Server connector entirely by making it Premium, so that if your app has any significant user base you're suddenly on the hook for tens-hundreds of thousands of dollars worth of extra licensing every year.  Now there won't be a massive security hole potential with this connector, because nobody will be using it anymore.  Problem solved!

Level: Powered On

Microsoft has announced that Azure AD-based authentication is coming to PowerApps and Flow for SQL Server. Hopefully this includes Azure SQL DB.

 

https://docs.microsoft.com/en-us/power-platform-release-plan/2019wave2/cdm-data-integration/better-e...