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

I believe this issue applies to any type of connection that is shared automatically (e.g. Twitter or Twilio), not just the SQL Server connection.

 

It makes total sense to create Flows to proactively report on this, however depending on your Flow license, you could quickly reach your max number of runs / month (assuming you run them more frequent than once or twice a day).

Level: Powered On
It seems, but I could be wrong, that some of the other Connectors you might want to use alongside your SQL Connector do not "travel" so well when the app is used by a user outside of the environment i.e. by sending the app link or the non-app environment user launching the app off the Dynamics menu or PowerApp Mobile menu. The Office365Users connector, for example. The non-app environment use is getting no data back from their profile when the app's control is configured to get it, yet users who are in the app environment do get the profile data back from the Office365Users connector in the control. I'll double check the set-up I've got, but otherwise that seems to be the case.
Level: Power Up

 Dear Microsoft,

 

May I know when the target date is to roll out the mentioned additional authentication model for SQL? As it is very critical for us to evaluate whether PowerApps is really the business solutions we can rely on.

 

Thank you.

Level: Power Up

Please make it secure!

 

Just pushing it to a non-default environment is ONLY a short-time solution!

Level: Powered On

In addition to what everyone else has said about the "Environment" solution, each individual P2 license only allows for two licenses.  So essentially the solution is that if you want to use SQL Server, one of the most common data storage solutions in the business world, each developer using PowerApps has to have a P2 license, and create their own separate environment.  It's a really rough work-around, and really undercuts the value of PowerApps for real business environments.

Level: Powered On

This is a significant issue for apps developed for an enterprise. I'm hoping Microsoft has a solution soon that allows app developers with a P2 license to adjust security settings, not just super admins (who are often extremly disconnected from development).

Level: Power Up

Even further, the level of security that is needed should be implemented at the core not just with P2 licenses.

Level: Powered On

This is basically the same issue as https://powerusers.microsoft.com/t5/PowerApps-Ideas/Removing-user-ability-to-access-data-source-with..., strongly recommend everyone go vote for that as well to get traction.

Nyk
Level: Powered On

^^

Level: Powered On

Seriously concerned with the thoughts of Microsoft Flow/PowerApps/Azure product team on this. It is the simplest use case where an admin user/developer should be able to cretae Flows/PowerApps and configure SQL Azure connectors which we're doing in our case. BUT, an elevated privillaged SQL data connector within a Flow / PowerApp shouldn't extend the previllages to anybody who's granted access to the Flow/PowerApp.

The only reason we are implementing a PowerApps UI to surface SQL Azure data is to create the security layer in between where users do not directly access/manipulate SQL data (Isn't the most obvious case for all organizations?) and if Microsoft's saying users get access to the connector if they get access to the app, that's a basic design flow in the products.

Please let me know if this concern is heard and if there's a roadmap to correct this.


@Anonymous wrote:

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.