I have an app which is up and running and writes back to Excel Online hosted on OneDrive. We do not find this a secure way as we have to give users of the Power App edit access to the Excel Online as well to successfully make the comment/write back.
We want to replace this with a On-Prem SQL Server Table. I am unsure of what rights are needed for the SQL Table besides reader and writer and how to go about replacing this Excel Online connection and how to go about making this happen.
Any documentation/advice would be really helpful.
Solved! Go to Solution.
+1 on John's suggestion to use Stored Procedures.
You can limit the SQL account to just executing the necessary procedures, no need to grant Insert/Update on the underlying tables.
The SP enforces data types and can have additional logic to validate the data.
Is easy to update if business rules change or underlying tables are restructured. The SP may look the same to the PowerApp/Flow thats using it but it can be completly rewritten - meaning you don't have to rejigger those applications when things change in the database.
So this is how my Power Apps looks like as of now which is connected to Excel Online and each of the sections in this screen like 'Type' , 'Date', 'Description' write back to the Excel Online. Additionally, I have considered the route of developing the stored procedure that has parameters capturing these details.
So my question is how do i define any kind of stored procedure within these 'data cards'/sections to write to the sp, is it just like changing the connection to SQL? I know I can connect to SQL but then it lists out Tables where I want these data cards to write back to, so I am kinda lost.
I will offer here is my step-by-step guide for deploying the gateway and building your first app from SQL. Might help you get started and avoid some of the gotchas with licensing and such.