Power App button calling a flow that executes a SQL Server stored procedure: user permission considerations?

[leemager]

I'm trying to think of a simple way for designated business users to get access to their data (stored on SQL Server, which the users don't have direct access to) via a canvas app. The idea is to create a self service interface for users to add their report filters and their data is then displayed to them within the canvas app. From there they can do all the usual good stuff in a canvas app plus export to CSV (I realise model-driven might be more suitable for such a data-centric application use case, but I want to see if this is doable in canvas apps before considering virtual tables in Dataverse). All users of this app will have premium licenses, just not direct access to the database.

My initial thought is to have a button in the canvas app that calls a flow which executes a stored procedure. I can do this fine for myself, because I own the flow and have the direct connection to the SQL Server database. But other users don't have that direct database access and obviously I don't want them to be able to edit the flow. But I do want them to see the data they're allowed to see.

So what I'm wondering is whether there are any obstacles in terms of the Power Automate flow. Can a canvas app business user call my flow which executes my stored procedure? If the flow is part of the managed solution would that take care of my concerns or is there still some permission gap because the canvas app users themselves can't own the flow / don't have direct database access?

[takolota]

@leemager 

If you only need to create, read, update, or delete things in SQL from the Power App, then this Power Apps flow template may help

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/SQL-Batch-Create-Read-Update-and-Delete/...

Fair warning though, the REGEX piece can be more difficult to set up to read JSON responses back to the Power App. And I haven’t updated the template to use the new JSON parsing method because it is still an experimental feature.

[leemager]

Hey @takolota and thanks so much for this, that's an awesome solution! My use case is much simpler as well as I'm concerned only with retrieval.

The part I'm more worried about is the canvas app user and their relationship to the flow that executes the SQL statements. They don't, and shouldn't, have direct access to the database, I just want to make the data relevant to them from the database available to them in a convenient canvas app interface. If they press the button that calls my flow, does it matter that they don't have access? So the flow that their button calls uses my admin credentials to retrieve the data, but I want to send that data back to them in the canvas app. I obviously don't want them to be able to edit or even see the details of that flow, so I'm not sure how I can return their data to the canvas app in a secure way without letting them see the flow. 

Edit: found something on the MS site which may help regarding implicit connections:

• An implicitly shared connection means that the user implicitly uses the credentials of the account that the app maker used to connect and authenticate to the data source during while creating the app. The end user’s credentials are not used to authenticate. Each time the end user runs the app, they're using the credentials the author created the app with.

This is a risk because theoretically the end user could use that connection to make an app of their own using those admin credentials. But I've found another fantastic post by @RusselThomas saying that a solution to this is only to make that connection available in an environment where Mischevious Bob doesn't have a Maker role.

" 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." 

So the question I have now, to make sure before I make a start on this idea, whether someone with only user access in a particular environment can definitely run a flow that connects to SQL Server using my admin credentials and sends their data back to the canvas app.

It may well be that using virtual tables with Dataverse is the best solution though as I believe that only ever enables read-only data in the first place, and setting up refined permissions is fairly straightforward in Dataverse. But some of the vids I've seen of virtual tables look like way more hassle than how Microsoft is selling them...

[takolota]

@leemager 

Can you use the Run-Only user settings: https://regarding365.com/how-to-add-new-run-only-users-to-a-flow-bb0e2f6817c2

And an Azure Active Directory user group like All Users or all internal users, so anyone in the organization using the flow will run it as a Run Only user without flow editing permissions?

[leemager]

Based on past posts it seems the 'run only user' option isn't available if the trigger is a flow called by a Power App unfortunately, it's very limited in terms of available triggers.

I think Dataverse virtual tables connecting to SQL Server is going to be the least painful option overall. It's almost certainly the safest / most securable approach and probably more performant than calling a flow too, I just expect it'll be a long and hair-tearing experience setting it up first time which I'm not looking forward to, hopefully I'm proven wrong on that though! I'll try it out and will post back in this thread with results in case others have a similar use case.

[takolota]

@leemager 

Sounds like you know what may be best for you & your organization.

But if you reach an impassable point with that route, there may also be an alternative way to create something like a Run Only flow for all users using an HTTP or Child Flow action instead to house all the actions you do not want the users to edit.

 https://piyushksingh.com/2021/05/25/update-the-child-flow-for-action-run_a_child_flow-to-not-use-run...

But a warning here for you & anyone else, the HTTP premium action can often make things technically possible to do, that are still against Microsoft policies, like multiplexing policies. So if all the users are not premium licensed, then you could be hit with an unexpected bill for every user of that method later.

[leemager]

Just to note that there were no issues using the approach recommended by Russel Thomas, namely: as long as the app is an environment where the users only have the 'basic user' (not a maker) security role, the flow returns the right data to them in the canvas app just fine and they can't access the flow or connection itself. Might still give the virtual table method a go as it may be more performant, but for now I'm relieved that the route one Canvas-Flow-Canvas approach works without risking any credential access!