Recently I was looking into the On-Premises gateway for Powerapps functionality and how we can send data to on-premise sql database. Here we will see,
- How to setup the On-Premises gateway.
- Create a new On-Premise gateway connection.
- Setting up a flow to send data to the On-Premises database.
How to setup the On-Premises gateway
The detailed step to setup the On-Premise gateway can be found here. Here I am including the screenshot / steps went through to setup mine.
- Download the Installer from the site https://powerapps.microsoft.com/en-us/downloads/
2. Install the On-Premises gateway
3. Key in the Name and the Recovery key
4. Complete the setup
Check the Status of the gateway after restarting the service either using the UI or using the command “net start / stop PBIEgwService“.
Create a new On-Premises gateway connection
- Once the on-premises gateway is setup with the right account, go to the powerapps application and it will display the gateway created on the previous step.
2. Create a new connection from the Connection tab
Click “New connection”
Select the “SQL Server” connection
Select “On-Premises data gateway” and enter the database details.
Enter the Username and Password required to connect to the database and the Gateway.
Complete the setup and the connection is displayed as below.
Setting up a flow to send data to the On-Premises database
Create a Flow on your tenant to with a trigger on Item created and action with the SQL connector.
Setup the action as to insert a new row.
Configure to store the title of the list item created and selecting the table.
Complete the Flow setup.
Right, the gateway, connection and flow setup is completed. Now let’s create a new list item on the configured list.
Create a new list item on the color calendar list.
The data is synced to the onpremises database using the above configured flow.
