Solved: Multiuser canvas app using On-Premise SQL Server database

Hi everyone,

I hope that the way I have structured this isn't too long winded. Please let me know if I need to edit this post to remain compliant with forum guidelines and recommendations.

BACKGROUND

I have a canvas app in development that aims to manage data and generate very basic administrative reports about employees within our Department. This app needs to be shared with a 5 member administrative team that will be using the app to

Enter new records for new hires
Edit existing records
Access other apps within the M365 platform (Planner, Outlook, OneDrive for Business)

DATABASE

The app uses a SQL Server database that I have full administrative privileges over
This SQL Server instances can connect to PowerApps through an On-Premise Gateway
Data from PowerApps is transmitted and received predominantly through Edit Form controls that connect to various tables within the database
For development, I have provided the SQL Server connector with my own Enterprise domain credentials to use when accessing the SQL Server (which can authenticate via Windows as well as SQL Server)

KEY QUESTION

I would like to know how best to set up the SQL Server connector in PowerApps to enable 5 people to read/write records to the database simultaneously?

I originally believed that when sharing the app, I would need to create new a new SQL Server connector for each individual and create a corresponding login and set up permissions in SQL Server when sharing the app in order for each individual to have his/her own access to the app as well as the database.

Would this mean I need to create each Edit Form control for each user?

I've been doing some reading and it sounds like SQL Server connections are always implicitly shared.

Does this mean that the best approach is to set up a single service account in SQL Server and use that account's credentials in the PowerApps SQL Server connector to access resources within the database?
What are the implications of several people using the app and concurrently making database requests through one service account?
Are there any implications of using a single service account with regard to the connectors required to access OneDrive, Outlook, Planner, or other applications within M365?

Hopefully you have made it this far down and can share some insights.

If there are any other technical details or clarification I need to provide regarding the app's setup, please don't hesitate to let me know.

Thank you in advance for any information!

Categories:

Connecting to Data

Connectors