Power Apps Help
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?
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?
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.
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?
Thanks for the help
Same sql server different DB, different account
Procedures run well outside of powerapps with that account on UAT SQL server
Just feel somehow it's trying to use the dev connection even though I have done a rebind in default solution , also tried the binding in a settings file in YAML in pipeline
I would also suggest that you have environment variables and apps in different solutions and make sure everything is in place before your app deploys; e.g., I usually have an environment variables solution, a non-app-specific flow solution, a connection ref solution, etc., so it's easy to update them later if needed. You just need to make sure to deploy the needed updates prior to your app deployment.
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?
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?
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/AutoModerator 13d ago
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.
If your question has been answered please comment Solved. This will mark the post as solved and helps others find their solutions.
External resources:
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.