cancel
Showing results for 
Search instead for 
Did you mean: 

PowerApps with SQL server on-premises – Using the Gateway, Part One

On-premises data gateway allows you to access on-premises data with PowerApps!

 

The gateway is a bridge from your app to the on-premises or virtual machine where your data is stored. On-premises means that your data is stored on a server physically located within your organization, not in the cloud. A virtual machine acts as a private cloud-based server. The gateway can be deployed centrally and you can connect to your on-premises data and manage it securely from PowerApps.

 

In this blog, I’ll demonstrate how we can create a simple App in PowerApps which can connect to on-premises SQL database.  The App is created to capture Leads information from the user’s phone and the submitted data is saved to SQL database which is on-premises.

 

Creating Demo SQL database: Create a new database in on-premises SQL server called PowerApps Test and create a new table within the database called New_Leads.

capture1

 

The New_Leads table should have the following columns with ID field as an auto-incremental primary key:

capture10

 

Installing the Gateway: Sign into PowerApps (https://powerapps.microsoft.com/en-us/) and under settings, click on Gateways to download and install the on-premises gateway:

capture5

Steps to Install and sign into the gateway are available at: https://powerapps.microsoft.com/en-us/tutorials/gateway-reference/

 

Install and setup a gateway with name PowerApps Gateway in the same machine where the SQL Server is located.

 

Creating the connection to SQL server on-premises: When logged into PowerApps click on Connections:

capture2

 

Next, click on New Connections:

capture3

 

Search for SQL from the list of services and click on SQL Server:

capture4

 

In the window that opens, select the right options and fill up the parameters required for the connection.

  • Select the option Connect using on-premises data gateway
  • Type in the SQL server name, Database name, Authentication typeUsername & Password
  • Under Choose a gateway option, select the gateway that is setup in the previous step
  • Click on create to create the connection to on-premises SQL database

 

capture8

 > Continue to part two of this post <

Comments

Hi Team, 

 

Can power app be hosted in the Hub Spoke architecture. Hub being a cloud server on shore and spoke should any remote server like offshore installations or in ships, etc.

 

And user of the app are in remote locations and any implementations happenend on similiar basis till now. 

Meet Our Blog Authors
  • PowerApps Community Mentor 2018, Practicing surgeon and former Professor of Surgery, University of Illinois, Chicago.
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 12 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • My name is Timothy Shaw and I create digital solutions using the Power Platform, Office 365, and Azure SQL and handle the IT for a small company in the energy sector. When not building the future :), I enjoy playing guitar, good (or really bad!) sci-fi, Xbox therapy, and hanging with my wife and son. Twitter: @ShortForTim