Rajeev Pentyala – Microsoft Power Platform

In this post, lets see how to build a Canvas App with data from on-premise SQL Server.

Using Gateways ,data can be transferred quickly and securely between a canvas app and an on-premises data source, such as an on-premises SQL Server database or an on-premises SharePoint site.

Lets get started to configure a Gateway and read the on-premises SQL Data which acts as a Canvas App’s ‘Data Source’.

Prerequisites:

• SQL Server with a Database and a simple table.

• Dynamics 365 subscription. Subscribe to 30 days trail here.

Configure a ‘Gateway’ to on-premise SQL Server:

We need to install ‘On-Premises Data Gateway’ service on the machine with ‘SQL Server’.

• Connect to PowerApps maker portal using your Dynamics 365 credentials.
• Go to Data -> Gateways and click on ‘+New gateway’ button.

• You will be redirected to downloads page and click on ‘Download’ under ‘On-Premises Data Gateway’ section.

• Download and click on the ‘GatewayInstall’ application.

• Provide your Dynamics 365 email (i.e., User ID) and click ‘Sign in’ to connect to Power platform.

• Provide your desired gateway name,recovery key and click ‘Configure’

• You will end up with below success screen.

• We are done with Gateway configuration.
• If you go to the ‘Services’ on your machine, you should see ‘On-premises data gateway service’ up and running.

• Go back to PowerApps portal and refresh the ‘Gateways’ and you should see the configure gateway.

Create a Canvas App:

As we completed ‘Gateway’ configuration, the next step to create a Canvas app and connect to on-premises SQL server using the ‘Gateway’.

• Click on ‘+Create’ and choose ‘Canvas app from blank’

• Provide the ‘App name’ and click on ‘Create’

• Now we need to establish the connectivity between Canvas App and SQL Server.
• Click on ‘Connect to data’ and select ‘SQL Server’ from the list.

• Choose ‘Connect using on-premises data gateway’ option and provide the SQL server, Gateway details and click ‘Create’.

• Select the table (i.e.,Contact) and click ‘Connect’ which creates a new ‘Data source’.

• Now add a Gallery component and map the ‘Data source’.

Additional Details:

• To install the ‘Gateway’, machine should have minimum configuration mentioned here.
• Gateway installs as ‘On-premises data gateway service’ on the server. Make sure this service is running all the time.
• Along with service, a desktop application by name ‘On-premises data gateway’ gets installed. You can enable logging and manage settings using this application.

🙂