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

@Barrett I did confirm (at least with my quick test) the following: An Azure SQL-connecting app is created in a non-default environment. The App is then shared with a regular user (not administrator for the environment, and not able to create Apps for that environment). Since the user is not able to create Apps for that environment, the user cannot "re-use" the connection from that particular App for Flow or PowerApps. If this had been done in the "default" environment the user would have been able to "re-use" the connection for their own Apps.

In other words, it's not users but connectors that are bound to different environments.

 

Level: Powered On

@blacklodger Ok, awesome. So you can SHARE an app outside an environment?

 

That's good to know. But that user cannot re-use the Azure SQL Server connection since they are not an admin/maker in the environment where it was created. 

Level: Powered On

@Barrett, correct, "running" an App doesn't seem bound by the environment at all. One downside might be that the user has to go to home.dynamics.com to find the App in question, if it was created outside of the default environment (it also shows up in the PowerApps mobile App), but the direct web link works as well, for opening it.

 

However (again, in my test), when having a regular "test user" trying to create an App in the default environment, the connection from the "other" environment was not available. So the connections do not seem to "travel" across environments.

Level: Powered On

@blacklodger Wonderful, thank you for the quick response! 

Level: Powered On

@Barrett, you bet 👍

Level: Powered On
This is a security hole so big you could drive a truck through it. Seriously, how can the SQL gateway be used with any confidence at all when rolling out an enterprise solution? Until the gateway can be used in a non-default environment then it is pretty much useless for anything serious. And in terms of using the CDS as a solution? I cant see this happening when every other database is SQL Server on prem within an organisation.
Level: Powered On

Hi guys, 

 

We find that this is a serious security issue here too. To resolve this issue, we decided to modify all our PowerApps Apps that were using a SQL connector to custom connectors built using a custom API. It's the best way to remain with SQL and use other environments.

Level: Power Up

Everyone please keep in mind that Enterprise Office 365 accounts need solutions as well... just saying.

I'd be really cautious about how this gets resolved. Some organizations have not yet defined their "one version of the truth" for data and I'd hate to see inaccurate truths become the one version via some creator having exclusive ownership of the data. It's really important for organizations to congregate to determine what their data strategies look like, and the controls for this should follow suit.

PowerApps Staff rc
PowerApps Staff
Status changed to: 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.

I've built apps with the SQL connector, I would 100% agree that you create all but the most generic, non GDPR/Persioinal data apps in a spesific environment, making an environment the best way of manaing any level of company data above "didn't we have a lovely time at the comapany picnic".

 

You can be quite specific over who does what with the newly intergrated D365 secuitry back end however, I see everyones point that you want visivility of what is going on in you business.

 

I would throw this out as a solution, use the new Admin connectors for PowerApps and Flow.

 

You can easily create a flow or app to report on what connectors are in use and who implimented them. Use the power of the platform to manage the platform. So if SQL security is your bag, then create a flow that runs every 10 minutes to report if any new apps or flows in your tennant or environment are useing any sort of SQL connector, that way you're covered.