Get A Quote

How to Connect CRM 2016 On-Premise Version With PowerApps

How to Connect CRM 2016 On-Premise Version With PowerApps

Today, the business world relies heavily on mobile technology. We don’t just work from cubicles, offices and desktop computers; we work from subways and taxis, coffee shops, and basically anywhere that we are. We have the luxury of being able to tackle nearly any work-related task from our phones. It’s not unheard of, however, that some business tasks still have to be done on computers. Mobile apps for business tasks have lagged behind. Those apps that do exist have not always been as inclusive or available as desktop solutions. Microsoft saw this issue and presented a remedy: Microsoft PowerApps, which is part of Microsoft Dynamics 365.

But what is PowerApps? Essentially, it’s a simple way to create the apps, specifically, their need, while leveraging the rich and diverse data sets that are provided with the Dynamics 365 Business and Enterprise Editions. Developers can quickly create the applications they need with data they already have, be it on-premises. This is CRM solutions that are built by a company for their own usage. Some companies prefer not to let a third party vendor handle their data. This is especially true of companies handling sensitive information such as banks and insurance companies. 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 within your organization’s physically located Server, 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, we’ll demonstrate how to create an app which can connect to on-premises SQL database in PowerApps. An 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.

To Create App in PowerApps, Login with your Microsoft account from here

After logged into PowerApps select “Connections” for creating the connection to SQL server on-premises.


Once you click on “Connections” You will have this screen, by clicking on “New Connection” You can connect to different third-party connections like Dynamics 365, Dynamics NAV, SQL Server.

different third-party connections

Here we are going to connect to SQL Server. Once you click on “SQL Server” it will popup dialog for configuration of SQL Server.

configuration of 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

parameters required for the connection

Once your connection is configured properly you will have server name or database name on listing page. For Creating an App, you need to choose PowerApps studio then Open the application, click on New and choose the Phone Layout under Start with blank canvas or template.

choose PowerApps studio then Open the application

This opens up a designer screen with a blank App screen at the center.

Add a Textbox and place it at the top of the screen. Set the properties of the textbox.

For adding an editable form select Insert tab > Forms > Edit to the screen and place it below the Textbox covering the rest of the area.

Textbox covering the rest of the area

The Form part in the screen will have an option to connect to data. Click on Connect your form to data to bind Connection which you have configured on Connection Panel. On the right hand side, under data sources tab, select the SQL server connection created in the previous step, select default and then choose the table School and click on.

table School and click on

After the connection, set the Form’s Item property to Defaults (‘[dbo].[School]’).

Form customization part will be open in the right pane where you’ll be able to add the required fields in the App. Select the required fields from the fields list. The selected fields get added to your App’s screen.

customization part

To run this app click on “Play” button, you will have all binded data as shown on the screen.

Microsoft is simplifying the process of producing software for business purposes, by offering a way for enterprise customers to quickly create business-related desktop and mobile apps. PowerApps is a service for building and using Custom business applications which connect to your data and work across the web and mobile without the time and expense of custom software development. With the help of power, apps feature like Data security and privacy control, it is quite easy to maintain the data. The applications created on Power app platform are modern, cross-platform and responsive.

Rate This Post

3.9/5 Based on 12 reviews
Read by636
Avatar photo

Written by Parth Patel

Parth Patel is a Microsoft Certified Solution Associate (MCSA) and DotNet Team Lead at CMARIX, a leading ASP.NET MVC Development Company. With 10+ years of extensive experience in developing enterprise grade custom softwares. Parth has been actively working on different business domains like Banking, Insurance, Fintech, Security, Healthcare etc to provide technology services.

Hire Dedicated Developers

Ready to take your business to new heights? Our team of dedicated developers is here to make your dreams a reality!



    Have an Interesting Project?
    Let's talk about that!