Unanswered

Enabling 'SQL Server Stored Procedures' from the upcoming features works in my standalone apps, but not Model Driven Pages outside of Design Mode

(0) Share

Report

Posted on by Mach_GO_GO_GO

Having a really finicky issue that I'm hoping someone can help me with:
I've enabled 'SQL Server Stored Procedures' from the upcoming features in the Settings, and while the process of adding SQL Server stored procedures to my app and running them in Design Mode (including using them to populate variables/collections ) has worked flawlessly, running them off of the Custom Page of the Model Driven App gives a notification prompt with the error below.

databasename.dbostoredporcedurename failed: { "error": { "code": 403, "message": "Procedure dbo.dbostoredporcedurename is not allowed", "source": "msmanaged-na.azure-apim.net", "path": "choose[3]\\when[1]\\choose\\when[2]", "policyId": "", "clientRequestId": "SomeGUIDhere" } }

I've tested by removing and re-adding the procedure, removing and re-adding the procedure again but with using a different connection, tested by creating a separate stored procedure with no table connection and just a simple select statement as output, etc. I ran the Model-Driven App in a monitor session and copied my results below: it's a 403 error but I have no idea as to why.

I've ran the same procedure using the same connection from a standalone test app (not Model-Driven App custom Page), and it performed as expected. It's also worth noting that this SQL data source and connection are being used across multiple apps without issue until now.

Any help with this issue would be appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

KiwiMark 23 on at

Like (0)

Report

I have the same error. I have 2 SPs: one is from a schema called "pwa" and one from a schema "ent". Both work when run in the PowerApps designer. Neither returns records. When trying to run the app from my mobile or from the PowerApps desktop the "ent" one fails - it has optional parameters that are not used. Both schema's have the same permissions. For all tests I'm using the same account.

Was this reply helpful? Yes No
KiwiMark 23 on at

Like (0)

Report

Been trying for hours now. It seems that I can only get 1 SP working at a time. Even when they are identical in parameters and content. This is a Preview feature and I think there is a bug!

Was this reply helpful? Yes No
KiwiMark 23 on at

Like (0)

Report

Also curious that you got this working in a Model Driven App - I thought it was only available for Canvas Apps and that Model Apps were limited to Dataverse tables??

Was this reply helpful? Yes No
Mach_GO_GO_GO 27 on at

Like (1)

Report

Try this (big thanks to Thamarai @ Microsoft):
In a standalone canvas app, open Settings, click Upcoming Features, select Retired, then scroll down to the bottom and turn on 'Disable secure implicit connections'. My Canvas App is part of a solution but I still found that this works to get rid of the error message on it. This fix isn't working for Custom Pages, however.

(Canvas App - Settings - Upcoming Features - Retired - 'Disable secure implicit connections')

Regarding Model Driven Apps - note that you have the option of creating Custom Pages in the solution (they're essentially single-page standalone canvas apps, with the option to enable multiple pages but turned off by default), and those pages can be added to a Model Driven App. The navigation and passing of parameters between separate Custom Pages is a little bit different - only one record ID can be passed at a time, and there's aren't global variables between them (Environmental Variables have to be retrieved via Power Automate, and people have used PA to create workarounds to the single-record limitation).

(from the Solution control page)

(from the edit-screen for the model driven app, after clicking 'New Page')

Was this reply helpful? Yes No
KiwiMark 23 on at

Like (1)

Report

@Mach_GO_GO_GO "Disable secure implicit connections" works!!!! That's great. My only worry is using a "retired" option seems a bit fragile - how long with that option remain active. Maybe something else will replace it eventually.

Was this reply helpful? Yes No
KiwiMark 23 on at

Like (0)

Report

Another episode in the saga ... after losing some stored procedure (procs) connections to error 403 and trying various ways to get it going again I have found the following. A connection can be working one day and next morning is still working but after making a few changes to just the GUI of the app the connections fail with error 403. So to fix this I take the following steps:
1. delete the old procs eg dbo.myprocA and dbo.myprocB
2. remove the data sources for those procs from the app
3. re-create the procs but this time with the suffix "1" in the name of the proc: eg dbo.myprocA1 and dbo.myprocB1
4. restart the gateway - if you miss this step you will see error 400 for a few hours till the gateway catches up the cloud fully.
5. reconnect the data sources to the new procs. NB: connecting more than 1 proc at a time will give you 1 data source with both procs under it. Rename the datasource to say "sqldb" and refer to the procs as sqldb.myprocA1 and sqldb.myprocB1
6. save and publish the app
7. NB: when saving/publishing the app I increment a version number on the home screen so I can see it has ACTUALLY updated when I go to test it. Sometime even waiting a few minutes and restarting the app (and loading the "new" version) actually just runs the old version. Much confusion if trying to test something and you're still running the old version!!

Was this reply helpful? Yes No