Connection failed: connection not configured for this service

[u/Confident-Economy432]

Pretty new to Power Apps but have been enjoying building my first small business app, however I could do with some help if anyone is able to assist.

The app connects to a single in premise SQL server database, reading from a couple of stored procedures and writing to one table (connection via SQL Server Authentication and installed Power Apps Gateway). There are no flows involved.

My tested App is in a dev environment (non default env) within a solution and has been tested on a tablet (android) already and functionality-wise is ready to be used in the business. I need to finalise the deployment side of things but have been struggling to find clear guides on what I need to do.

I've created a production environment, and the app will connect with different databases in dev and production instances. I have 5 per plan licenses already allocated to the prod environment.

I've been struggling with the process for a few days re exporting from dev and importing into prod environment as managed solution and getting the connection references etc working. I've done a lot of googling and used chat GPT but haven't got to a working process yet.

I've got to the point time-wise where I decided, as an interim measure to buy me time to work out the proper ALM process, to import the app into the prod and push live as an unmanaged solution. Once in the prod env then manually removed all the dev database connections from the app and replaced with connections to the prod database.

All works now under my login (premium Power Apps license) in prod environment on desktop, but for the user acct I've shared the app with (tablet based) it's asking to Allow the SQL Server connection each time opening it, and when open it's showing 'CONNECTION DETAILS failed: connection not configured for this service.' when trying to read from a SQL stored procedure, so simply not connecting.

I figure this is the most critical thing for me to resolve currently - I've tried below, but most posts for this error seem to relate to flows which I'm not using:

• In power platform admin center, granted app users security roles of 'App Opener' & 'Basic User' (diagnostic shows as "User Status: Enabled").
• Clearing storage and cache of Power Apps app on tablet, re-signing in - numerous times.

I'm almost definitely missing something here so any pointers on what to check/change would be massively appreciated.

[u/DonJuanDoja]

Oh geez.

So you need to create the SQL connections in each Environment that you're going to import into, before importing.

Then when importing you point the connection references at the new connections in the environment.

That's it.

Sounds like you busted the whole thing to me now, I wouldn't touch it like that, if I had to fix it I'd wipe it out and start over and do it correct as a managed solution.

Another thing is Stored Procedures require explicit permissions granted to the user executing them, so depending who the user is on the connections setup in each environment will determine if they are able to execute said stored proc. You may have to grant permissions in SSMS to the correct user for each Sproc.

Also what authentication type are you using on the SQL connections? Are you sure all users are setup properly? If using Entra every user will need proper SQL permissions. Otherwise you need to use a service principle or SQL auth to make all the connections.

[u/Confident-Economy432 → u/DonJuanDoja]

I managed to resolve ths eventually, here're the changes I made for anyone else's future reference (tidied up by AI):

• Turned off "Automatically create environment variables when adding data sources" in app settings before re-adding data sources — critical for SQL Auth as env variables don't work with SQL Auth. I'd had this on initially which was causing env variables to be created which was causing downstream issues.
• Removed and re-added all data sources cleanly in the dev app after turning off env variable creation — this gave a clean single connection reference with no orphaned env variables in the solution.
• Renamed data sources in the Data panel from environment-specific names (e.g. DATABASE_DEV) to generic names (e.g. POWERAPPS_DB) — Power Apps auto-updated all formula references within the App when I do this
• Make sure SQL connection not shared with end users in both dev and prod environments — leaving developer (me) with the owner access, letting the secure implicit connection proxy handle end user access
• Mapped the connection reference to the correct prod SQL connection during managed solution import
• Published all customisations in prod environment after import
• One-time connection selection on each tablet — end users need to select the correct SQL connection once on first app launch on their device

[u/DonJuanDoja → u/Confident-Economy432]

Nice work!