Help - moving AAD SQL connections between environments for PowerApps

[MarrinerDev]

This question has been asked several times in the past with no satisfactory answer from Microsoft, so let's try again and see if they surprise us.

We have a Canvas PowerApp which uses an Azure SQL connection with AD Security - as per our own and Microsoft's best practice advice (i.e we're NOT using implicitly shared SQL-auth connections). We have the usual 3 environments, Dev, Test, Prod and we need to move the App between those environments. Very simple setup, as old as computing itself - one server per environment.

Note: --> The Power Automate Flows move fine because they use Connections References and Environment variables to point to the different server/database names.

However, the canvas app does NOT. As per this documentation link from MS, there is a troubling bullet point:

https://docs.microsoft.com/en-us/powerapps/maker/data-platform/create-connection-reference

" * Canvas apps and flows handle connections differently. Flows use connection references for all connectors, whereas canvas apps only use them for implicitly shared (non-OAuth) connections, such as SQL Server Authentication."

A support ticket to MS and subsequent investigation by their engineer suggested there is no solution to this. We have over 35 connections in the app (all to views and tables in the same database). Are you seriously telling me that we have to delete/re-add each one every time we publish an update from Dev to Test etc????

My question for the forum members is - have you discovered any way around this?

And my question for Microsoft is:  --  at your upcoming "Microsoft Business Applications Launch Event" on April 6, are you going to provide any joy for professionals using Canvas Apps/SQL server?

So, Charles Lamanna, Ryan Cunningham, Greg Lindhorst etc - any thoughts? @GregLi

Thankyou...

[WannaBePro]

Hi @MarrinerDev ,

Currently I believe the "DevOps" context to PowerApps is mostly limited to Solution based deployments that don't necessarily use data sources outside of DataVerse.

I'd be curious to see if they can actually address this without rebuilding how PowerApps connections and connectors work.  As you probably know already, in PowerApps the connector is 'baked' into the app at save, allowing users who run the app to create a new connection or reuse their existing connections of that connector when they first run it.

Connectors and user Connections are also scoped to each environment, so if you're physically moving your app from one environment to another, connections will be unique and different across environments.  There's no way to replace the connector source baking on environment migration.

So I doubt we'll see anything there any time soon, although I'd be happy to be proven wrong here.  That said, while it's not possible to change the connector source configuration on migration, it is possible to keep all the connectors and just do the one-time remapping of user context for each.  By this I mean, you will always have to realign connections from one environment to connections in a new environment by selecting existing or creating new in the target environment.

It's pretty manky, and not ideal, but I would guess theoretically possible to bake all 'external' connections into your app and then choose which one to use based on the app development context?

So for eg: If we think about SQL, let's say you have three SQL servers, dev, test, prod.  They are all independent of your environments, so your app can see all of them regardless of which environment it's running in.

• In your PowerApp, you have three connections configured - dev, test and prod.
• Using dataverse environment variables or PowerAutomate on launch, you should be able to figure out which environment an app is running in.  You can then have logic in your app that uses the relevant dev/test/prod connections based on what environment the app is running in.
• Migrating an app from one environment to another will still always trigger a connection realignment for the target environment for each of those connectors, but the code should stay the same.

As I said, not ideal at all, pretty manky and three times the logic for direct data calls, but in theory....possible?

WBP

[MarrinerDev]

Thanks for your reply @WannaBePro.

Yes, we found out the hard way that Microsoft even embeds secondary connection info inside the app, via the digging around we did to solve our previous question:

Solution management is broken for SQL. - Power Platform Community (microsoft.com)

Trawling through the JSON definition files for the PowerApp found references to the old SQL-authenticated connection. The reason why it was still there as we hadn't yet updated a couple of Flows which the app called, and these flows used the old SQL-authenticated connection rather than the newer AAD version. Sigh.

Given that the process of connector indirection works for Flows (using AAD SQL) and works for PowerApps using SQL-Auth, there really isn't any reason why they can't let it also work for PowerApps using AAD SQL.

i.e. if I move the app from Dev to Test and then manually delete/re-add all the SQL connections, I'm left with a set of JSON files which have been transformed to use the new connections. I could have done this myself manually using some scripts - but again, it's (1) messy and (2) undocumented.

The fundamental problem is that MS are obsessed with devoting resources towards Dataverse/Model-driven nonsense and haven't finished "baking" the offerings for grown-ups using canvas/SQL.

Just pinging @WarrenBelz, @RandyHayes in case they haven't seen this and may have some additional thoughts...

Many thanks...

[FredericForest]

Would love an update on this. With the announcement of stored procs in Canvas, I'm considering switching to Premium licences, Managed environnement, deployement pipelines and all that jazz. But if something as basic as point to a different dbb per environement is not going to work, it may be a show stopper.

[MarrinerDev]

No update yet. I missed that Stored Proc announcement. Do you have a link?