Using PowerApps On Premises Data Gateway

By

Nearly two years ago, Microsoft launched PowerApps as a tool that Citizen Developers could use to build simple mobile solutions for accessing their data. We wrote an introduction to PowerApps in December last year that highlighted some of the benefits. In this post, we are going to look at using the PowerApps On Premises Data Gateway to build a PowerApp using On-Premises Data. There are now an endless number  of connections that you can make using PowerApps, that with a few clicks, you can have the data from those connections available in a mobile form on your mobile device, tablet or desktop. Those connections include Twitter, Twilio, SQL Server, Slack, WordPress, Wunderlist, YouTube and of course SharePoint! Open up PowerApps from your Office 365 Tenant and click Add Connection, and you will see long list of possible connections.

PowerApps Data Connection Choices

In this post, we are going to focus on accessing On Premises data using PowerApps. Many organizations still have, and are likely to have for some time, legacy databases which are used for critical operations within their businesses. The databases that I am referring to may be running on SQL On-Premises, Oracle, DB2 or many other database platforms. It may be that for the time being at least, it is just too costly, or there are policies in place restricting you from hosting such data in the cloud. However, you want to make the data available to the cloud, for business users to benefit from.

PowerBI for some time has provided the On-Premises Data Gateway as a service for accessing On-Premises data, and PowerApps can utilize the same technology. The On-Premises Data Gateway can be used to connect to a number of different data sources including SAP HANA, ODBC, SQL Server, Oracle, DB2 and ODBC.

Download the Microsoft On Premises Data Gateway

Allowing your On-Premises data to be available to the cloud is quite straight forward. First, you need to download and install the On-Premises Data Gateway. The installation file can be downloaded from here: https://www.microsoft.com/en-us/download/details.aspx?id=53127

Installing the Microsoft On Premises Data Gateway

Once you have downloaded the Microsoft On-Premises Data Gateway, run the installer executable and go through the steps below:

1. Click Next to the warning regarding the machine being always on.

On Premises Data Gateway Installation

2. Accept the Terms and Conditions and set the installation path.

PowerApps On Premises Data Gateway Installation

3. Set the email address to be used for the gateway. You will need to sign in to your Azure tenant.

PowerApps On Premises Data Gateway Installation

4. In this example, we will register a new gateway on this computer.

PowerApps On Premises Data Gateway Installation

5. Provide a name for your gateway along with a recovery key.

PowerApps On Premises Data Gateway Installation

6. Your gateway will be configured for Power BI, PowerApps and Microsoft Flow. A Gateway can be configured for Logic Apps and Azure Analysis Services.

 

PowerApps On Premises Data Gateway Installation

7. Now that the Microsoft On Premises Data Gateway is configured and running, you can turn your attention to PowerApps. Login to your Office 365 Tenant and select PowerApps from the Office 365 App Launcher. Once PowerApps has launched, select Apps from the left hand navigation pane and, then click the Create an App link in the top right hand corner of your page.

PowerApps On Premises Data Create App

8. On the New App page, click the Phone Layout link under Common Data Service to connect to a data source via your Microsoft On Premises Data Gateway.

PowerApps Create Common Data Service

9. You can now create a new connection to your data source. Click New Connection.

PowerApps New Connection

10. From the list of Data Sources, choose your desired data source. In our case, we will be selecting Microsoft SQL Server.

11. Select the radio button to choose to Connect using On Premises Data Gateway.

12. Set the SQL Server Name, Database Name, Authentication Type (Windows or Basic), the credentials, and select the name of your On Premises Data Gateway that you set in step 5.

PowerApps On Premises Data Gateway SQL Server

`13. Once the connection is established, you can select a Table from the list of tables returned from the database.

PowerApps On Premises Data Gateway Table Selection

14. PowerApps will be launched allowing you to work with your data.

PowerApps Browse Screen

Working with the App

You’ll notice that you now have a phone layout browse screen allowing you to browse, view and edit the data from your desktop, but also if you install the PowerApps phone app, and sign in to your tenant, you will be able to use the data from your phone as well. Before you let users loose on the App, you may want to consider input validation and consider the integrity of your database. In the left hand navigator, your will see that you can customize the browse screen, detail screen and edit screen. Customizations may be to include layout changes, and also validation. In our next blog post, we will focus on making design changes and add some validation logic to our forms.

<Brett/>