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
ToninoBruno
Regular Visitor

@Django  problem is resolved! It seems we did re-create the canvas app in a dedicated environment but we did not actually re-create the connector there. I re-created the connector today in the dedicated environment and now effectively nobody can use the connector as they do not have any create privileges in this dedicated environment. Thanks again for your feedback and pushing me to double check 🙂

Rutu1
New Member

Creating a new environment has been suggested as a solution however while creating a new environment

i get the following error

This environment can't be created because your org (tenant) needs at least 1 GB of database capacity. 

 

Also the Default environment shows Used Capacity 1024 MB out of available capacity of 0 MB

 

I have a power apps per user plan and a Microsoft 365 Business Premium Plan

 

How do i create a new environment?

MauriceVold
Advocate III

Good day.  You need to buy space.  As a GA account in Office 365 you can find it under Billing and Purchase Services.  Might want to talk to your Microsoft rep as well - you might be able to get some sort of deal.