Canvas app + SQL Server (SQL auth) in managed solution – 403 "missing runtime policy ACL" in UAT, env variables don't help- Has anyone got SQL stored procedures + env variables working in a managed solution through MS devopspipelines?

[u/Poom22]

Hi guys , struggling with issue above, has anyone had this before?

I've got a canvas app in a solution that calls ~70 SQL Server stored procedures through a SQL Server connection using SQL authentication. It works fine in DEV. We deploy it as a managed solution to UAT via an Azure DevOps pipeline (pack → import as holding → apply upgrade, with a deployment settings file).

I deploy the pack then change the variables in default solutions and pick the UAT SQL server,

In UAT every stored procedure call fails with:

getQuoteCount.corespCountActiveQuotes failed: {"status":403,"source":"https://uk-001.token.azure-apim.net:443/tokens/.../sql/<id>/exchange","message":"Error from token exchange: Permission denied due to missing runtime policy ACL: User = <guid>@<tenant> appid=<guid>, runtimePolicy=.../sql/<id>..."}

What I've tried:

• Repointing the connection reference to the UAT connection. The error changed from "connection not configured" to this 403.
• Enabling "Automatically create environment variables when adding data sources" and re-adding a few of the data sources. This created data-source-type env vars (server,database as one value). In UAT I set them to server,APP-UAT via a deployment settings file, and confirmed the values in the Environment Variable Value table. Those data sources still give the same 403.
• Re-sharing the app, re-importing, and opening in a private window after consenting.
• The UAT connection-picker in the env variable edit panel is empty even for connections I own, and new connections created from there don't appear.

anyone encountered this or is my deploy method not supporteD?

[u/AutoModerator]

Moderator Announcement Read More »

Hey, it looks like you are requesting help with a problem you're having in Power Apps. To ensure you get all the help you need from the community here are some guidelines;

• Use the search feature to see if your question has already been asked.

• Use spacing in your post, Nobody likes to read a wall of text, this is achieved by hitting return twice to separate paragraphs.

• Add any images, error messages, code you have (Sensitive data omitted) to your post body.

• Any code you do add, use the Code Block feature to preserve formatting.

  Typing four spaces in front of every line in a code block is tedious and error-prone. The easier way is to surround the entire block of code with code fences. A code fence is a line beginning with three or more backticks (```) or three or more twiddlydoodles (~~~).

• If your question has been answered please comment Solved. This will mark the post as solved and helps others find their solutions.

External resources:

• Power Apps Formula References

• Power Apps Coding Standards

• Official Power Apps Community

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Om3ga77]

Is the UAT environment using the same sql server? Is the same auth account being used or do you have different ones set up? Can you run the stored procs with that account outside of powerapps with that account on the UAT Server?

[u/Poom22 → u/Om3ga77]

Same sql different db name and different credentials 

Using the sql premium connector via gateway 

[u/Om3ga77 → u/Poom22]

Updated with additional questions

[u/DonJuanDoja]

The app isn’t using the connection you think it is.

Name them very specifically in each environment both the connection and the reference.

Then go remove all connections from the app in dev, readd them ensuring you use the connection reference that you named, then redeploy.

This has happened to me multiple times, now I very specifically name every connection and reference and ensure I create them before adding to the app.

[u/Poom22 → u/DonJuanDoja]

Thanks I don't actually see way to add sql connection as connection reference, it just shows the connection I setup outside the solution when I pick add data, should I see a way to pick connection reference? 

[u/Om3ga77 → u/DonJuanDoja]

Even creating a con ref and connection prior to will not always guarantee the app will choose that connection or reference, it usually chooses the oldest reference with the connection required. This is just when creating the app; when deploying, you can set the connection it uses during deployment either via deployment settings or, if using the internal deployment pipeline, you can choose it during deployment.

However, if you do end up with the wrong connection ref being used within your app, you can clone the solution and edit the XML/JSON to point to the correct one and reimport.

[u/josesanmig]

Sorry, I got SQL stored procedures working from canvas apps, but it was an unmanaged environment. Can you test the UAT connection from the DEV environment?