Stored Procedure is not allowed (bug?)

[FredericForest]

So I'm having a weird behaviour with the new feature for stored procedure calls directly in PowerApps canvas... This might be a bug since the feature is still in preview, or maybe I'm doing something wrong.

I have this setup : 

- A SQL Login type connexion to a SQL Database in the environnement

- A connexion reference in my solution, pointing to the connexion

- An app in my solution, pointing to the connexion reference

In the app, I create a datasource of type Stored Procedure against the connexion reference. It works well in the authoring client (yay!) and works well in the published app. So far so good.

But then, I change the connexion object to point to a different database... It still works fine in the authoring client (proving the new login works), but when I publish the app I get a 403 "Procedure XXX is not allowed".

I can see with the monitor that the request's server and database have been properly updated. The request from the web authoring client seems a bit different than from the published app, but the published app includes a "x-ms-shared-connection-id" property that points to the right connexion.

Any idea what might be going on?

[kennethplatt]

Nothing much yet. I was able to demo the issue to our MSFT contact and she's taking it back to the team. I'll share this thread with her too so she's aware it's not just a problem with our org.

[FredericForest]

Eagerly waiting for news too. I just had to hold off a dev with this feature yesterday because the ALM would have prevented going into production... 😕

[Baudelaire]

This morning, out of the blue, my SQL connected app experienced this same issue.  Has anyone found a real solution?  I have an app that is scheduled to go into production tomorrow and now THIS happens??

Enabling that retired feature does make it work, but as stated by others, this is NOT the solution.  This platform can be SO frustrating.  What works one day does not work the next, then it works again, then it doesn't.  Hard to depend on it as an enterprise-grade mission critical platform when changes just drop from the sky and cripple productivity.

[kennethplatt]

Yeah. It's not consistent, but in my experience, as soon as you try to publish the app it fails without this retired feature turned on. 

Here is an update on my end. About a week ago, I met with our rep from MSFT. We recorded a session where I demo'd the issue and even built a new simple app that demonstrates the issue as well. We were able to show it failing, but the new app seemed to have a different error message. Both apps worked ok with the retired feature enabled. Yesterday, after hearing nothing, I sent a follow up email to see what the status is. I expect I'll probably get a response today, but who knows what's happening on the MSFT side.

I'll keep poking her for an update. I've also shared this thread with her so she knows it's not just me who is experiencing this and frustrated by it.

[WCarterMVUSD]

Any updates from your ticket? I've encountered this same issue and have just opened a ticket myself. I do note that stored procedures running on our on-prem sql, over a gateway connection, do not exhibit the error (though they do have other issues...). I don't know what aspect of our setup in particular is the cause of that, but it is interesting.

[kennethplatt]

No update yet. I know they just had the Build conference in Las Vegas and the whole team was super busy, but that's done now. I know there are changes happening daily so I attempted to reset the Secure Connections setting again yesterday to see if it "magically" was fixed, but nope.

I pinged my contact yesterday and I have an office hours call on Thursday next week I plan to attend. If I don't get an answer soon, I'm going to escalate with an official support ticket. That usually gets some attention.

Keep pinging this thread. I'll update as I learn more. This is painfully slow.

[FredericForest]

Thanks for keeping us posted! We're so close to a game-changing feature! 😅

[kennethplatt]

I got a response this morning, but I'm not sure now much it helps. I pushed back a little bit as I feel like the explicit secure connection feature has been around for a while and it's movement out of preview is not really a reason for this to be failing. I'd be interested in anyone else interpretation here.

See below:

Hey Ken!

Sorry I missed updating you last week. After I got back from Europe, I had to fly to Charlotte last minute for a customer onsite. Thankfully, I’m home for a while now.

Good news! I finally had a breakthrough on your SQL issue. It turns out there were changes to the way the SQL connections work in Power Apps for enhanced security on implicit connections that the product owner, Lance Delano announced GA in February. All new apps use this feature, and older apps must be converted which is why you are seeing the retired “Disable secure implicit connections” setting. I’m waiting to hear back from Lance today to get clarification on the continued support for explicit SQL connections. His post has a link to the documentation on configuring for implicit connections:

https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/connections-list#shared-connections--...

I’m so sorry this has taken way too long to get sorted out for you! I’ll let you know as soon as I have more clarification from Lance.

[WCarterMVUSD]

Thank you for sharing this update. Interesting, though I'm not entirely sure it's helpful to the situation. We know the setting exists and have some idea of what it's doing, but this message doesn't really address the interactions between secure implicit connections and stored procedure failures. The most interesting line is probably "The connection proxy limits the actions that are sent to the connections to the ones in the Power App {Get, Put/Patch, Delete} ", but the proc is invoked via POST, ""x-ms-request-method": "POST"", so I don't know why it would be an issue. Maybe whomever created the method to run stored procedures did so in a way that is outside of / uncounted for by the backend service that handles the secure implicit connections and that method of connecting is only used when playing the app? Not enough data on our ends to know what's going on.

Also, I've got a tech wanting to schedule a teams meeting with me to go over my issue, if you don't mind shooting me the name of your contact in a DM I might mention it to the person helping me and they can consolidate their efforts, the person who initiated contact with me apparently had no idea that the stored procedure feature even existed.

[kennethplatt]

Will do. I'll message you shortly. FYI I got on the phone with my contact this afternoon and played a little more. I was able to get a sample app to work (strangely enough, it didn't work last time we tried so I'm not convinced), but my main application still fails. I sent a dump of the error and the entire application to them to review. Hoping for some momentum from that.