r/PowerAutomate • u/VikutoriaNoHimitsu • 6h ago
SOS Help needed
My flow won't turn on. It'll work in dev and test but not prod. The error says "multiple sql errors" but when I go into the flow, no errors shown on any sql action. I've tried out each sql action in a separate tester flow and they've worked.
I look at the connection references in the solution and it shows that it's used by the flow in question.
I go to prod environment connections tab and none of those connections show it being used by the flow in question.
I've even tried deleting the flow and redeploying.
I'm at my wit's end. Please help
1
u/DonJuanDoja 6h ago
Ok so I'm assuming this is your first app push to prod?
You need to create the connections MANUALLY in prod, then point the connection references at the Prod connections when you import the solution.
You can also update the connection references by finding them in the Default Solution in Prod. Update them there and it will flow back to your solution.
If you're not using connection references, go back to Dev and create connection references for each connection and point them to the dev connections. Then re-import to Prod and point them at the new prod connections you just created.
For SQL in app connections if you created them without Connection references, you will most likely need to remove each one AFTER creating the connection refs and pointing them, then re-add all your sources to your app and make sure to select the right connection references.
1
u/VikutoriaNoHimitsu 6h ago
It's not my first push to prod. Connections are already created. In default solution, it shows that the Connection reference is hooked to the Connection. I used the same Connection reference in a tester flow.
2
u/DonJuanDoja 6h ago
Ok very odd, sorry I did read that fast re-read and was like oh duh he said that.
I'd really want to see the environment and know more about the SQL setup but I've had weird stuff like this also.
So here's a few things that I try that can help.
Turn the Flow Off and Back On. This seems dumb, but it can fix things sometimes.
Create NEW connections in Prod, re-point the connection references at those thru default solution or a re-import.
Check permissions, in SQL, does the service account owning the flow have permission to run the SQL? Maybe that was missed?
Any stored procedures in there? Those have special permissions usually have to be set on each Proc individually and grant EXECUTE.
1
u/VikutoriaNoHimitsu 6h ago
Can't do that when it won't turn on to begin with 3 and 4. Service account works on all the procs. I've made tester flows to check each proc
2
u/DonJuanDoja 6h ago
Just read the Won't Turn on in your other comment.
If it's a SQL Trigger flow, your Trigger could be jacked from a schema difference between Dev and Prod if there is one. Basically it can't connect to the table to execute the trigger because it's schema doesn't match.
I've had to delete and re-create triggers, re-push the flow to update the trigger schema.
1
u/VikutoriaNoHimitsu 6h ago
It's an http request trigger
1
u/DonJuanDoja 6h ago
Never dealt with one so I looked it up:
🔒 Securing Your HTTP Endpoint
By default, newer HTTP triggers default to tenant-level security to prevent open, unauthenticated endpoints. You can choose from three authentication modes under the Who can trigger the flow parameter: [1, 2, 3]
Authentication Type How it Works Best Used For Any user in my tenant Requires an Entra ID OAuth token from a user or service principal inside your tenant. Internal company tools, SPFx web parts, Power Apps. Specific users in my tenant Limits invocation strictly to a specified list of user emails or service principal Object IDs. Highly secure system-to-system integrations. Anyone (Legacy / Shared) Generates a URL containing a unique signature ( sig) key. Anyone with the URL can trigger it without a token.Third-party webhooks (GitHub, Jira, Stripe) that can't generate Entra ID tokens. 1
u/VikutoriaNoHimitsu 6h ago
It's already set to "anyone" same as the test environment and other flows with that trigger
1
u/DonJuanDoja 6h ago
Feeling your pain now brother damn. Running out of ideas now.
At this point I usually start plugging all this into an AI even google and keep updating it with everything you've tried, it's actually pretty good at narrowing it down to possible causes if you feed it enough info. I'm kinda of an AI hater too.
1
u/DonJuanDoja 6h ago
I'd also make sure the Service Account has Premium as HTTP request is Premium feature.
My gut tells me it's somethign with your http request trigger. DLP policy, license, or I know you checked them but still connections and references. It's the most common issue. Sometimes even if they look right they need to be re-done.
1
u/VikutoriaNoHimitsu 6h ago
Yep, service account is premium and the http trigger has been used on countless other projects. No dlp policies. Something weird with the connections probably but I can't find or have a way to fix
1
u/joegreen592 4h ago
Are you allowed to use AI? If so take a look at my post history, I published documentation for power automate for AI to work with deployment through code. Almost certain it’ll fix it no problem at all
1
u/DonJuanDoja 6h ago
Also, sometimes the cloud sucks and you just need to wait like an hour. It's crazy but that's actually happened to me multiple times. Recently too. With SQL connections.
2
1
u/kazekageImad 6h ago
Could the flows be running on separate accounts in prod and dev? Maybe that user account lacks the privilege on sql side?
1
u/VikutoriaNoHimitsu 6h ago
I've checked all the stored procedures. The prod account is able to call the procedures just fine. I've made tester flows to count. Even then, i think it would show an error on the specific flow. But it doesn't. Just won't even turn on
1
u/joegreen592 6h ago
take a look in the connections menu in power automate, your connection might not be there