Hi @joglidden2 ,
This is a great question as many app makers understand very little about how the platform works - which is fair, because with a 'no-code / low-code' byline we expect the majority of makers to be non-developers, oblivious to security or application architecture concerns. But it does help for those who manage these platforms to understand what can and should be done to foster and protect innovation at the no-code/low-code level, while also protecting against unintentional risks. Helping makers understand a little more doesn't hurt either - so apologies up front for using your post to dump a whole lot of information 🙂
Firstly, platform features and capabilities as well as connectors change often and are constantly updated, so this may be relevant for a specific connection type today, and become irrelevant tomorrow - so focus on the principles here, not the specifics.
For the SQL connector specifically there are some updates that address some of the issues I mention below - but I must stress that this may not apply in your instance and even if it could apply, you should seriously consider ALL aspects of security and governance when looking at these things.
Secondly, security and governance in the platform is a very deep and wide topic - so this post is LONG but doesn't touch even 1% of the consideration. You clearly have a good understanding of the impersonation risks, so this could have been a much shorter answer, but I wanted to highlight considerations many miss while still linking it to your question, so others can also read and hopefully understand this a little better. There are probably some shorter blog posts out there you can read, but hopefully this helps someone 😁
Lastly, different organisations have different approaches to innovation and governance - some industries are more regulated than others - but regardless of this, everyone should have a view of what their stance is. The Power Platform out the box supports open innovation - that means some default configurations that may not be desirable for many organisations.
The short version:
- Should you be worried? Yes. Risk is everyone's problem, everyone should know what is being done or can be done with their solutions and their data.
- How much should you be worried? Well that depends - on where you created your app, what the organisational governance framework and controls are and not only who you've shared it with, but how you've shared it with them.
- Should you use Excel instead of SQL? That's not exclusively a Power Apps question, that's primarily a data question. Use whatever suits your data and intended usage of it. To put it another way, based on your question/post, it might be better phrased as "should I use Azure AD SQL or standard SQL?"
- If you haven't explicitly shared a Canvas app yet, nobody can run it, let alone edit it. Just because you can see it (or others) doesn't mean it's shared. Model driven apps use a slightly different access model, but let's ignore those for now.
- If you do share it, you explicitly decide whether people can run it or edit it. Be aware, depending on the environment you're in, just sharing it with them might still open your connection up for abuse, whether you allow them to edit it or not.
So if you just created a Canvas app and published it - nobody else will see it. The apps you can see are either your own, Canvas apps that have been shared with everyone, Canvas apps that have been shared with you explicitly, or model-driven apps.
That said, there are a number of things to consider when it comes to security, governance and compliance on the power platform, and to make this useful for everyone, these require some background explanation - hence the long version;
Publishing vs Sharing:
Publishing your app is not sharing your app. It's just making the current "edit" version the "live" version which is the version you see when you "play" your app. If you've published your app but haven't shared your app with anyone, nobody will be able to access it or the connections you use in it.
Sharing your app is an explicit action - you need to decide who you're going to share it with, and what their role will be. They can be a normal user who can run your app - or you can designate them as an 'owner', in which case they can edit your app. This is however completely within your control.
They also need access to the data your app uses - Sharing your app does not automatically share the data your app connects to with people - but it's here you need to be cognisant of implicitly shared connections vs explicit connections (discussed below). Sharing data is not the same as sharing access.
But first, it helps to understand the environment context within which connections operate.
Environments:
When it comes to environments, what is not necessarily appreciated about them is how they relate to connectors, connections and how the Default environment in a tenant is created and configured up front.
An environment is an isolated 'box' that contains Apps, Flows and Connections (and possibly a Dataverse database).
Apps, Flows and connections are specific to the environment they are created in. You can't create an App in one environment that uses a Flow from another. You cannot use a connection in one environment that was created in another.
When a tenant is created, a Default environment is also created - the default configuration is quite open by design, as it's meant to be a sort of 'play pen' for the organisation to explore and play with apps and flows - however if you leave it as it comes out the box without introducing some governance, it can become difficult to manage.
The characteristics of the "Default" environment in a tenant are varied, but in this case these particular characteristic should be noted;
- Your admins cannot create or delete a Default environment - it is created by the tenant 'by default'
- All licensed organisation users are Makers (can create apps and flows) in the Default environment 'by default'
- Your admins can create DLP policies for this environment which control what connectors can be used in that environment
Organisations can (and should) create additional environments that give you more control over who can build apps in them and what connectors they can use in them - but your Default environment will always allow everyone with a license to build apps in it - so for control options in the "Default" environment, you can only manage what connectors are usable in this environment. You can also control who has a license at a global tenant level, but this should probably be a last resort as the license applies equally to users and makers - so if you remove someone's license they can't make or use an app in any environment.
ConnectORS vs ConnectIONS:
- A Connector is a pre-defined set of 'rules' that define how you can connect to a specific service and what you can do with it. Think of it as a template - for the more technically minded, an API definition wrapper of sorts.
- A Connection is a persistent instance of a Connector that you must create in order to connect to that service - and is generally configured to connect using specific credentials.
- Once you create a Connection, your apps and flows can reuse that Connection configuration many times to connect to the service (so you don't have to log in and authenticate every time you connect using a Connection).
- You can create multiple Connections to the same service in the same environment, using the same or different credentials.
- All Connections are environment specific - this means you can reuse a Connection only within a specific environment - you cannot reuse that connection in a different environment, you have to create a new Connection to use in that environment.
- Connections in an App that support AAD authentication are not shared with other users - those users need to create their own connection to that service to use the App.
- Connections that don't support AAD authentication, or allow implicit sharing, ARE shared with users. Users gain access to and can use the connection that comes with the app and don't need to create their own.
So, browsing your Connectors you will see hundreds of predefined connectors available - from O365 services, to Google services, Social platforms, Azure services and yes, SQL and others. These "templates" are added to constantly.
Browsing your Connections - you might see none - that is until you use a connector to create your own instance of a connection. You might have done this just by running someone else's Power Apps in which case you might have lots already.
Once you have a Connection to a service in an environment, you can use that specific Connection in your apps and flows in the same environment - or you can create more. Having multiple connections to the same source with the same credential is possible - it's just a little redundant in my opinion. You can have multiple connections to the same source with different credentials as well.
Implicitly Shared vs Explicit Privilege:
Explicit:
Most M365 and related power platform connectors support AAD authentication which is a user-context based authentication of a sort - this just means that the connection will always be made in the context of the user using it. These explicit role-based privileges and related authorisation tokens are granted based on the user creating the connection (such as security roles for Dataverse, OneDrive for Business, SharePoint Online and so forth).
Connection context you create with these connectors are not implicitly shared when you share your app - your users would have to create and authenticate their own connection to use it. This is why you can't just share an app that uses SharePoint Online lists as a data source with users and expect the app to work - you also have to make sure their user accounts have access to the SharePoint Online list the app uses, because they will connect to it in their own user context.
This is why you see a connection permissions request pop up the first time they run it - you'll notice they have to give approval for the app to use their connection context if they already have one for the services the app uses, or they have to create it and then provide approval for the app to use it.
Implicit:
Some connectors or data sources however might not support this and so instead allow you to 'bake' the connection credentials into the connection when you create it and share the connection itself.
As you're no doubt aware, this means that the connection will always use the credential you gave it when you create it - you've shared the connection and it's credential context itself. They won't see the password or anything, but they can still use it.
With an implicitly shared connection, when you share your app with another user they create a connection to the service using the same connection you created - not their own.
A user using your app with this connector will effectively be impersonating the credential configured in the connector.
The problem:
That might already sound like a problem, but actually, having users use a shared connection and impersonate the connection credential is an intentional outcome of implicitly shared connections - that is by design. So it's not necessarily a problem as long as they can't reuse that connector and use it in ways you didn't necessarily intend them to.
In the normal course of events, a normal user can only use your app the way you build it and can't manipulate what your app does with the connections it uses.
If you make them an 'Owner' of your app, then they can pretty much do what they want with it. That too, is not actually the problem, because it requires you to make them an owner, which is an intentional action.
HOWEVER - what is often not intended is this - if you share that app with a user (as a normal user, not an 'owner') and that user is also a Maker in that same environment, that user can go and create another app of their own and reuse your connection in their app (because you've implicitly shared the connection with them).
Because they're making their own app, they can now use whatever queries they want with that connection and credential, potentially accessing stuff you didn't really intend for them to access when you were building your original app.
Over-simplified example:
I create an app with a SQL connector and use admin-level credentials on the connector. My app only shows the user data from a specific table and doesn't allow them to write anything, only read info. They can't mess with the app code, so their perceived "privilege level" when using my app is 'read-only based on whatever the app displays to them' - even though they would effectively be impersonating my admin credential which has much higher privileges.
I share the app with "Bob" who also happens to be a Maker in the same environment. The SQL connection I created is also implicitly shared when I share the app. Bob runs my app and then decides to build his own app in the same environment.
When Bob adds a SQL connector to his app, he sees he already has my SQL connection which he can reuse.
Bob adds the connection to his app and can now run pretty much any query he wants on any table admin has access to, inside his app.
This is definitely not what we want! Bad Bob! Well, not really, Bob's just doing what I've allowed him to do.
Coming back to our Default environment - everyone is a Maker by default in the Default environment. Now you can see the problem. If my admin-SQL-connected app is shared with anyone in the Default environment, they can do what Bob just did.
The solution(s):
The way to deal with this is to consider the governance aspects across People, Process, Technology and Culture.
This involves having dedicated people who manage the platform, a framework within which they can operate and tools to support them, all of which support the innovation culture of the organisation. Nurture, not strangle. Guard, not block.
First and foremost - your organisation should have a governance and compliance framework to manage these things. Your admins can use the CoE Toolkit for guidance and tools, as well as the Platform administration configuration - but these are just guides and technology levers - your organisation should have a governance stance that dictates how these levers should be configured and managed to support a framework that allows for business innovation while at the same time protecting against unintended risks.
Now, I have no clue what level of maturity your organisation is at with regards to platform governance, so while we can assume 'low' for what follows, that doesn't mean it is or that they aren't or haven't already dealt with these issues...
Consider a number of controls;
- An environment strategy that reflects your organisational stance on innovation and governance
- A DLP policy that supports your environment strategy, and effectively determines what can and should be possible to connect to in each environment, including the Default environment.
For example, while you (and by "you", I mean "your admins") can't control who is a Maker in your Default environment, you can control what those Makers can do in that environment.
They can also decide who can create environments - By default, anyone can create an environment, but your admins may already have turned this ability off.
Your admins can (and should) implement an environment strategy that determines who can create environments, what environments can be created by whom, and who can create what inside those environments.
Example:
Your tenant "Default" environment should be considered as a playpen, or 'personal productivity' space for non-business critical or non-integrated productivity solutions.
You can for example stop users from creating apps and flows with implicitly shared connections in the Default environment, or any connectors that fall outside the context of 'personal productivity' - and only allow them in controlled environments.
Let's say we stop people from using SQL connectors in the Default environment, but create another environment where I'm a Maker but "Bob" isn't, and SQL connections are allowed. I can still share my App and its implicit SQL connection with Bob, but as it's an environment where he is not a Maker, he cannot reuse my connection in his own apps and flows because he can't create apps and flows in this environment. He also can't reuse the connection in any other environment where he might be a Maker because connections are environment specific.
- RBAC and correct privilege levels for source
- Use the right source
Use Role Based Access Control with the correct privilege level at source. My SQL connection credential should be privileged in SQL according to the function of my app - instead of using an admin (or even my own credential), I should rather create and assign a role with the minimum requisite privileges it needs only for my app to work - and nothing else.
In my opinion, impersonation is always going to be a security thorn, regardless of whether it's warranted or not - so if you can avoid it by using a source that supports AAD authentication, do that instead. Otherwise if you're stuck with it for whatever reason, then try to use a very narrow scope of privilege for your impersonation account.
Example:
In this instance, I can't use AAD SQL, so I should possibly create a role that only has read access to views or tables I specifically want to surface in my app. I could then create a 'myPowerAppUsers' account in SQL or on AD and assign it to the role. I should also possibly consider whether I can implement row or even field-level security for my data source and assign those privileges to the role as well.
Conclusion:
Like I said, LONG post, but hardly scratches the surface. Here's a list of takeaways that might help;
- Remember, we're talking about Implicitly Shared connections here - this may not apply if you're using explicitly privileged connections. In your case, the SQL connector you're using is implicitly shared.
- If you've built and published your app in the Default environment but you haven't shared it with anyone, then you're not necessarily at risk for now but I would strongly recommend moving it to be safe.
- If you have published and shared it in the Default environment, then be aware that anyone you've shared it with could potentially reuse your SQL connection in their own app - whether or not you made them an owner of your app.
- If this is not intended, then I suggest you stop sharing it immediately, delete the connections and move the solution to another environment where you can recreate them safely.
- If you can create your own environment, I suggest you read up on environments and create one as a starting point
- If you just want to play around with apps and flows yourself, create a developer environment for yourself. You will be the only Maker in this environment but you can't share any apps or flows from inside this environment - so it's pretty safe for you to play around in.
- If you want to share your app with someone else, make sure it's built in an environment where you have visibility or control over who can make apps in that environment (such as your own environment - just not a developer environment).
- Know and mitigate the implications of sharing it with someone if they are also a Maker in that environment.
- Lastly, check with your IT team. They might already be doing this stuff and can potentially provide you with some guidance relevant to your org.
Apologies for the long response, but I hope it helps to raise awareness of this to more people and kudos to you for asking a great question.
Kind regards,
RT
