Adventures with Power Platform: Fun with Connections and Connection References

Recently I deployed a solution that contained a Canvas App that grabbed data from a SQL Server using two methods:

• Canvas App queries the SQL Server using the SQL Server Connector.
• Canvas App calls a Power Automate Flow which queries the SQL Server using the SQL Connector.

Deployment seemed to go fine, but I noticed my flow was turned off.

I tried to turn it back on and received a nasty error message.

Just for kicks and grins I ran the Canvas App, wanted to know if this was just a flow issue or if my Canvas App was affected as well.

The gallery populated by the Canvas App querying SQL Server using the SQL Server Connector was empty and no visible error was displayed.

I took a look in the Developer tools and found an unexpected error Invalid object name dbo.Character, the query I was making was unable to find my table.

To be honest, I did not check the Developer tools the first time I encountered the error, instead I deleted the Solution from the target environment and redeployed it.

Same issue.

I dug a little deeper and that’s when I discovered the above error in the Developer tools.

I immediately navigated to the Default Solution to the underlying SQL Server Connection that the SQL Server Connection Reference used … only to discover … insert drumroll … the SQL Server Connection Reference was pointing to the wrong SQL Server Connection!

It was using the connection to my AdventureWorks database and not my Star Wars database.

In my haste of deploying, I had let the Power Platform pick what SQL Server Connection it would use, which, unfortunately, was not the right one!

The easiest way I got it working was to redeploy the application and update the Connection Reference to the correct Connection, I am using Power Platform Pipelines for deployment.

Once corrected, my Canvas App and Power Automate Flow worked as expected.

One other thing to note, if I paid attention to the App permissions dialog that displayed the first time I ran the Canvas App, I would have seen it was using the wrong database.

Anyway, glad to have figured this out.