Connect to your on-premises data sources from PowerApps using on-premises data gateway

We are excited to announce the availability of on-premises data access from PowerApps using the on-premises data gateway! This has been one of the top requested features by our users. The data gateway can be deployed centrally and allows you to manage data and helps connect securely from PowerApps, so you can benefit from your existing on-premises data investments without the need to move any data. As part of our business application platform, you can now use a single gateway across PowerApps, Microsoft Flow and Power BI.

This blog post will walk you through the scenario of creating a connection to your SQL Server database using the gateway as well as managing your gateways at powerapps.com. Once you have an on-premises connection created, you can use it to create an app from data from PowerApps or start from a blank app and import the on-premises connection as a data source for your app – just like you would with any other cloud connection.

How can I create a connection to my on-premises SQL Server?

You can create connections in PowerApps in multiple ways.

1. From powerapps.com

2. When creating app from data

3. When creating an app from blank and adding a new connection as a data source

4. When opening an app created by another user and shared with you

This blog post will focus on #1 – which is creating an on-premises connection from powerapps.com. The connection creation experience for #2 to #4 is similar to #1.

In your browser, navigate to powerapps.com and sign in with your work or school account. Once signed in, go to “Connections” under the “Manage” section in the left navigation menu.

Click or tap on “New connection” on the top right,

 

And select “SQL Server” connector.

 

Select “Connect using on-premises data gateway” option to connect to your database on-premises. Provide server name, database name, authentication type, user name and password. SQL Server connector supports using service accounts and we recommend you use a service account. You can then share this connection with other users who use the app, and this way users of the app dont need to be granted permissions at the database.

You will also be asked to choose a gateway if you don’t already have one available to you. You can either contact your IT Admin to install a gateway and provide you access, OR, you choose to install a gateway yourself. To see more details and learn how to install and configure a Gateway, click here.

Once you have the gateway configured, click or tap on “Add connection”. That’s it! The name of your SQL connection is “<server name> <database name>”. You now have a connection to your on-premises data source and you can use this connection like any other cloud connection to create apps in PowerApps!

How can I manage my on-premises connections?

Once you have created your on-premises connection, you will be able to see the connection in your list of connections at powerapps.com. You can go to “Connections” under the “Manage” tab to see the list of your connections.

You can select your on-premises connection from the list to view details of the connection as well as edit the connection.

How can I manage my on-premises data gateway?

You can install an on-premises data gateway to transfer data quickly and securely between PowerApps and a data source that's not in the cloud.

To view all gateways for which you have administrative permissions, and manage permissions and connections for those gateways, go to powerapps.com and sign in with your work or school account. Once signed in, do go “Gateways” under the “Manage” section in the left navigation menu.

Select a gateway that you want to manage permissions and connections on. If you don’t have a gateway available to you, you can install and configure a Gateway.

To add/remove users to the gateway, switch to the “Users” tab, add new users and specify the permission level for the user using the drop down. You can also choose the connection types you want to allow the users to have access to, on your gateway.

In order to view the list of all connections created on the gateway, switch to the “Connections” tab. You will be able to see all the SQL connections created on this gateway.

Connections to SQL Server use a service account and are sharable. So, you can select a SQL connection and switch to the “Share” tab of the connection in order to see the list of all users using the shared SQL connection.

Next steps

Now that you’ve learnt how to create connections to on-premises data sources and manage gateways, you can try creating and sharing apps that use on-premises connections. To learn more about on-premises connections and gateways, you can visit PowerApps help page here.

Have feedback or asks?

We would love to hear your feedback regarding this new functionality. Does this meet your requirements to connect to on-prem? What are the other on-premises data sources you want to connect to? Are there other features you need to connect to on-premises? We are continuously adding new features and functionality and your feedback will help us prioritize our work!

Please reach out to us via the PowerApps community site. You can also share your feedback from within PowerApps using the “Send feedback” button in the upper right hand corner next to your name. We look forward to hearing from you!

Thanks!

PowerApps team