You’re offline. This is a read only version of the page.
105
We are currently hitting the 5,000-item limitation in SharePoint lists and want to move our data layer to SQL Server. Our SQL databases are hosted on-premises, and we’re evaluating the use of the On-Premises Data Gateway to bridge Power Platform apps (Power Apps, Power Automate, and SharePoint Framework) with SQL Server.
Our main objectives are:
Install and configure the On-Premises Data Gateway.
Enable secure and reliable connectivity to SQL Server databases (Dev/UAT and Prod).
Ensure integration with Power Apps (via SQL connector), Power Automate (SQL actions like Get Rows/Insert Row), and SPFx (via REST APIs or custom connectors).
Address licensing and security considerations (e.g., Premium license requirements, admin rights for gateway installation).
Questions to the community:
What are the best practices for installing and configuring the On-Premises Data Gateway for SQL Server?
Are there specific security considerations we should keep in mind (admin roles, Azure AD, token-based access, etc.)?
Has anyone integrated SPFx web parts with SQL Server through the gateway? If yes, how was the experience?
Any guidance on licensing (Premium connector requirement) for this setup?
What are the best practices for installing and configuring the On-Premises Data Gateway for SQL Server?
--This would require going to the Fabric / Power BI Side to talk to them as this isn't that support channel. I can say from personal experience.. there is no real such things as "best practices" respectively, as its an install.. Its a simple install, configure it, create its counterpart in the cloud and secure it, use it per se, but there are definitely some things to avoid and connection configurations to take into account, but this is related primarily to the app and SQL not so much the gateway exactly.
Use Microsoft SQL Server securely with Power Apps - Power Apps | Microsoft Learn
Now the other thing is whether you run it in Personal Mode or Recommended Mode. You are a business, so its a no brainer, its Recommended mode since you want to connect using power apps and have others do it, versus personal which would just be available to literally you only.
Of course you will have to make sure your DMZ allows the port traffic in and out etc but thats a Firewall Infrastructure issue not a gateway issue.
Also make sure you pick the best region or support it in multiple regions
Are there specific security considerations we should keep in mind (admin roles, Azure AD, token-based access, etc.)?
--Again not really a power platform question, but I really recommend you start here
On-premises data gateway architecture | Microsoft Learn
On-premises data gateway FAQ | Microsoft Learn
Pricing for services supported by the data gateway | Microsoft Learn
Has anyone integrated SPFx web parts with SQL Server through the gateway? If yes, how was the experience?
In all my decades I have never been asked to do this with a SharePoint extension... I am sure.. maybe someone tried it, but I do not know why they would per se since the SharePoint HTML DOM.. I dunno I'd have to check if you can even do it, versus embedding a site component so I cannot answer this one much.
Any guidance on licensing (Premium connector requirement) for this setup?
--The gateway itself has no impact on connectors whether they be premium or not, its the platform (PP) and the back end you are connecting too that determines it.
Connect to SQL Server from Power Apps overview - Power Apps | Microsoft Learn
SQL Server - Connectors | Microsoft Learn
Do not ask me why Logic apps is listed as Standard but Power Apps is Premium.. lol it makes no "logical" sense to me
If these suggestions help resolve your issue, Please consider Marking the answer as such and also maybe a like.
Thank you!
Sincerely, Michael Gernaey
Share
Report
All responses (
Answers (
