Create PowerApps Application with On-premises SQL Server

PowerApps is a one of the powerful low code applications from Microsoft, which helps to develop millions of applications within a minute. Only platform which allows technical and non-technical to implement application with their requirements. Power platforms can be developed to receive data from different data sources like Excel, SQL server, share point resources and data from different cloud storages like Google, OneDrive, etc.

Considering OnPrem data sources we do traditional coding to connect the application with the data source. But here we have a possibility to develop a PowerApps and receive data from On Prem SQL server. In this article I will show you how to connect OnPrem SQL to PowerApps.

Pre-Requites
1) OnPrem SQL Server
2) PowerApps Subscription
3) On-Premises Data Gateway
4) Internet Connection

On-Premises Data gateway for Power Apps

On-Premises gateway is a bridge which provides most secure communication in between local sources to cloud applications. To know and learn more about On-Premises data gate way: What is an on-premises data gateway? — Power Apps | Microsoft Learn

SQL Server on Windows 10 Machine

I have SQL database installed on my Windows 10 machine, which is a local machine with the active internet connection. If you are new to SQL server installation and configure, I have shared some other posts which will show you how to install and configure SQL server 2022 in your test environment.

Download SQL Server 2022: SQL Server 2022 Trial (microsoft.com)

To Confirm your Installed Version: SELECT @@VERSION

 

I have created a table named Demo Data and inserted some sample data

 

Let’s configure the On-Premises Data Gateway

 

To Download the On-Premises Data Gateway for PowerApps please download the Gateway from below link — Downloads | Microsoft Power Apps.
Once it’s downloaded install it to the local machine, it will take some time to install. When installation succeed it will prompt a dialog box to enter the Microsoft account details. Use the same account which you used to create PowerApps.
In the next step create a Gateway with the valid security and recovery key. Here I named my gateway as DEMOPOWERAPPS

 

Create Configure button to apply the configurations and close the dialog box, now it’s done.

 

Now it’s time to build the app in PowerApps

Go to PowerApps portal to make the app — make.powerapps.com

 

I am going to create the PowerApps with SQL data source

Click the SQL and add the New Connection and select the connection source as SQL database (SQL Server).

In the authentication type select SQL Server Authentication from the drop-down list. In the source selection pane select Connect using on-premises data gateway as data source for SQL server

 

SQL Server Name — Windows 10 Local PC Name
SQL Database Name- POWERAPPSDEMO
Username- demouser
Password-[Your SQL User Password Here]

 

In the bottom you need to select the installed gateway, here it will show the gateways you installed with the Microsoft account which you used to create the PowerApps.

 

Here my installed DEMOPOWERAPPS gateway is listed and it’s showing, if your gateway is not listed, please refresh the gateway list. Once you select the correct gateway click create to link the data source to PowerApps.

It will show the available tables once the gateway connection is succeeded, here my table DemoData is showing. I am going to use the DemoData table to connect with PowerApps.

 

In case in the same database if you want to change or create new table you can either create it from here or choose different one.
Click connect to link the On Prem database with PowerApps, It’s done and app is automatically created now.

 

Without doing any additional changes in the app I will see the preview of the app to check to see whether it’s communicating correctly with the SQL database available at my laptop.

The data which I entered manually using SQL commands are appearing here, it means it’s perfectly connected with the on prem SQL database.

 

By adding an edit form in the PowerApps you can be able to insert the data from PowerApps to On Prem SQL Server.

Just try this by yourself and let me know if you have any issues in connect On Prem SQL server to Power apps.

Happy Coding!!