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.

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

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:

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:

Next, click on New Connections:

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

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 type,  Username & 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

 > Continue to part two of this post <