Adventures with Power Apps: Canvas Apps and Connection References

Let me set the stage.

I have two Connections in my environment that point to the same database server and database, the first one named SQL Server 1 (XXX) and the second one named SQL Server 2 (XXX), where XXX is the name of the database.

Both use an implicit connection using SQL Authentication, see Use Microsoft SQL Server securely with Power Apps – Power Apps | Microsoft Learn for more information on the differences between implicit and explicit connections.

I then created two solutions, SQL Server Solution 1 and SQL Server Solution 2.

The solution, SQL Server Solution 1 contained the following:

• SQL Server Connection Reference 1, pointed to the SQL Server 1 connection.
• SQL Server Flow 1, which returns a list of customer records and uses SQL Server Connection Reference 1.
• SQL Server Canvas App 1, which displays a list of customer records using the SQL Server connector.

The solution, SQL Server Solution 2, contained everything SQL Server Solution 1 had, save I replaced the 1 with a 2.

I made sure that my flows used the correct connection reference from the solution.

On to the Canvas App, this is where it starts to get interesting.

According to the article at Use a connection reference in a solution – Power Apps | Microsoft Learn:

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.

I added a SQL Connection to the Canvas App that pointed to the appropriate connection, for example, SQL Server Canvas App 2 is where I added the connection for SQL Server 2 (XXX).

I clicked Save and Publish on both Canvas Apps, and then clicked Publish all customizations for the solution.

Just for kicks, I opened up SQL Server Solution 2, click the three dots next to SQL Server Canvas App 2 and then click Advanced and Add required objects.

For some reason, it adds the connection reference SQL Server Connection Reference 1, instead of just using SQL Server Connection Reference 2 for the Canvas App?!

I did not expect that behavior.

Playing around with a bit more, it seems like the Canvas App, when using a connector that requires a connection reference, will automatically use the oldest Connection Reference that uses that same connection, and by same, seems like in SQL it is unique based on Authentication, Database Server and Database Name.

This is kind of annoying, again, I would expect it to use the Connection Reference already in the solution.

Note: Sometimes to really get rid of a referenced Connection Reference by a Canvas App, you will need to remove the Connection and/or the Flows from the Canvas App and/or remove the Canvas App from the solution, and then add the Canvas App back to the solution, followed by the Connections and Flows.

So, what does this tell me?

It tells me that in my environment, if I am using Connection References in Canvas Apps, I should have a one-to-one relationship between Connection Reference and Connection, just to be safe and avoid dependency and deployment challenges.

So created a single Connection called SQL Server (NAME_OF_MY_DATABASE), and a single Connection Reference called SQL Server Connection Reference (NAME_OF_MY_DATABASE).

This seemed to do the trick!

As always, if there is a better way, please share!