Unanswered

Moving AAD SQL connections between environments for PowerApps

(2) Share

Report

Posted on by 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????

So 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 or are going to continue droning on about Model Driven/Dataverse nonsense and your mythical "low coders" (who always seem to turn to a grown-up to fix the mess once complexity/security/reality hits and their solution isn't viable anymore).

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

All responses (2)

Answers (0)

PaulD1 2,914 on at

Like (0)

Report

Re: Moving AAD SQL connections between environments for PowerApps

Sorry @MarrinerDev
I'm afraid that by the time the AAD SQL connector became available the 'SQL Connector Tax' of 2019 had been introduced after which we had no new requests for SQL based Canvas Apps due to the costs and hassles of licensing. As a result I haven't used the AAD SQL connector. I assume from your post it is not a case of exporting the App from one tenant, importing it to another and selecting the desired connector as part of the import process (as per the old SQL connector)?
I wouldn't hold my breath on any more SQL improvements coming through - all focus seems to be on Dataverse sadly. I don't think there have been any SQL improvements since support for connecting to Views (2018?) and support for AAD authentication (2020?). We never got fixes/enhancements for supporting defaults on mandatory columns, support for updatable views or the ability to call Stored Procedures without resorting to Power Automate.
As I have been so disheartened by the direction of canvas Power Apps I have largely moved out of that space bar some support for legacy apps that have not yet been moved onto more capable/robust platforms.
MarrinerDev on at

Like (0)

Report

Re: Moving AAD SQL connections between environments for PowerApps

Just seen some of your posts @PaulD1 and wondered if you might have any thoughts?