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
Anonymous
Not applicable

This restricted access is a must for real business usage

Level: Powered On

I totally agree. This is a huge security flaw in powerapps usage. An immediate concern!

Super User

Hi there - it seems to me that the platform is going in the direction of the common data service for Apps (essentially a database inside dynamics).  One of the reasons we didn't go down the SQL route was that flow does not work very well with local databased as it cannot detect changes.

It's worth a look. 

Level: Powered On

MartynasJurkus, I think this is a really good observation, and it would make a lot of sense to further develop the permission levels as you suggest.

 

However, I'm curious as to why you feel this problem is not resolved (at least "mostly") by using environments? Creating a new environment would force the administrator to use "Plan 2", so that is an immediate downside, but beyond that, using a non-default environment removes the "maker" permission level from other users (say, in an Office 365 tenant). And since connectors (AFAIK) do not carry over across environments, users of apps from other environments do not have the ability to create new Apps, and hence cannot make use of the shared connection.

 

Again, appreciate your argument, just curious about why that solution wouldn't be a workaround?

Anonymous
Not applicable

Why should someone have to upgrade to Plan 2 to ask for more isolated "security", and really that only stops them from creating new apps?

 

This should have the ability for the app owner to create the connections and then the user is able to interact with the app without having to have direct backend access. Even then with "environments" you would still have users in a single environment with back-end access. 

Super User

Hi,

 

In answer to your question on why upgrade to Plan 2

 

Bear in mind that plan 2 is only for administrators - business users wouldn't need this.  OK here are the reasons why you need Plan 2:-

  1. You can restrict the connections that people can make when building apps - keeping this open offers far too many options.
  2. It provides a corporate 'home' for your apps.  The default environment is effectively the wild west of your business apps
  3. You can assign different administrators to edit the app (admittedly they would also need Plan 2)
  4. You can import/export apps with Plan 2
  5. Any flows you create are held in the environment you create
  6. You can provision a Common Data Service Database

Moving onto the Common Data Service for Apps (and I admit to only getting into this recently):-

  1. "Connections" appear more straightforward - i.e. no requirement for connectors that have be maintained
  2. You have the ability to create both Canvas apps (WYSIWIG) and Model driven apps
  3. You are far closer to a Dynamics integration should you choose
  4. Entities/Tables are easy to create
  5. Field types reflect more closely the required fields for apps
  6. You can set rules for calculated fields in a straightforward manner
  7. Your app building and entity building experience are all in the same place

I'd suuggest that any business that is serious about PowerApps has to get a Plan 2 licence anyway, so you may as well benefit from all that comes with it.

 

 

 

This is quite a good video on Model Driven Apps in the CDS-Apps

https://youtu.be/02NWfHRYkeo

 

 

Hope this helps and I hope I'm actually answering your point :),

 

Rory

DataSpinners

 

Level: Powered On

I'm not trying to be complacent about the lack of features in this case - I think they should implement more granular permissions asap, but I was also thinking about possible, albeit (im)perfect workarounds to OP:s Azure SQL connector scenario. While other enviornments might work with the Azure SQL connector, it will not solve this problem regarding the on-premise Gateway (which is only available in default environments). That's certainly another aspect to fix soon.

Level: Powered On

The best solution would be to hide the connector from the user completely, not even prompting them for permission at all.

Level 10

I think a difference shold be made between on-premise SQL and Azure SQL Database, Flow works perfectly well with Azure.

There should be a connector for Azure SQL Database where it connects with the user's identity (Azure AD?), so that we can use the RLS (row level security) features of Azure SQL Databse as well.

This is a somewhat different approach but should resolve the problem and allow SQL admins to administer rights the way they are used to.

 

PS Not using the default environment for anything sensitive seems to resolve the issue of the SQL connector being available to all, seems like a good approach

Level: Powered On

Yes, this is a serious problem for my company's use of PowerApps. In regards to @blacklodger 's idea... I wonder if there is any way to confirm that:

  1. A connection to an Azure SQL database can be created in environment A
  2. An app can be created in environment A that leverages that connection
  3. The app can be shared with a user in environment B
  4. The user in environment B can use the app, but cannot create a new app in environment B that uses the Azure SQL database connection from environment 

Does anyone know how to confirm this?