Showing results for 
Search instead for 
Did you mean: 

PowerApps with SQL server on-premises – Using the Gateway, Part Two

> Continued from part one of the post <


Create an App: You can choose to use PowerApps studio for Windows (requires installation) or PowerApps studio for Web for creating an App.


Let’s consider using PowerApps studio for windows. Open the application, click on Newand choose the Phone Layout under Start with blank canvas or template:




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 following properties of the textbox: Color = RGBA(255, 255, 255, 1), Fill = RGBA(0, 119, 255, 1), FontWeight = FontWeight.Semibold, Text = “New Lead”


Add an Editable Form (Under Insert tab > Forms > Edit) to the screen and place it below the 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. 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 New_Leads and click on Connect.untitled3


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


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 gets added in your App’s screen.



You’ll be able to re-size the text boxes or input controls if required.


Add another screen to the App (Under Home tab click on New Screen). Select the newly created screen from the left pane and add a Text box control. Set the Text Property to The lead information was submitted successfully! and Align property to Align.Center.




Now we have 2 screens in the App. You can see the names of the screens in the Left pane.



Select Screen1, click on Add a custom card option at the bottom within the screen.


Add a Text box control in the new card. Set the Auto Height property of the new text box control to true, and set its Text property to this formula: Form1.Error (The text box will show any errors from the form. This also requires the form name to be Form1)


Add a Button control in the card. set the button’s Text property to Submit and set the button’s OnSelect property to SubmitForm(Form1); If(Form1.ErrorKind = ErrorKind.None, Navigate(Screen2, ScreenTransition.None)) (This formula tries to insert the data entered to SQL table. If there is any error while writing to SQL, the Error message will be displayed on the app otherwise the screen 2 of the app will be displayed informing the user that write to SQL was successful)


The App is now complete and is able to accept user input and save it in SQL table.




In SQL server, run the following simple query Select * From [PowerApps Test].[dbo].[New_Leads] and no rows will be returned.



Run the App created in PowerApps, enter all the information within the App and click on Submit.



In SQL server, run the same select query (Select * From [PowerApps Test].[dbo].[New_Leads]) again and you’ll find the newly inserted row in SQL:



Microsoft continues to enhance the features and capabilities of PowerApps! Have fun building!


It does not seem like it is possible to connect PowerApps to an on-premise SQL Server view. Is this a feature that is being actively worked on? Does the PowerApps team have an ETA on releasing such a feature?

Hi, thanks for your tutorial, but I have a problem:

When I try to set OnSelect property on submit button, I cant set two things in there. With that lane:
SubmitForm(Form1); If(Form1.ErrorKind = ErrorKind.None, Navigate(Screen2, ScreenTransition.None))

that you wrote on that post, SubmitForm(Form1);   give me an error. It makes me to think... is that lane setted on OnSelect button property? or is in another place?

second question:
I'm trying to set an error screen if a collect function fails, but it never detects an error here:


If(Form1.ErrorKind = ErrorKind.None; (Collect ('[dbo].[New_Leads]_2';{dni : DataCardValue1.Text; fecha: DateValue1.SelectedDate; nombre : DataCardValue2.Selected.Value ; telefono: DataCardValue3.Text} )); Navigate(Screen2; ScreenTransition.None))


any idea?


thanks a lot and have a nice day.

Good Article. I am using same concept and build the app but Gatway in my case is my machine. In next Couple week or so, my organisation is moving Azure Sql. So if anyone can give me guide to how to migrate it from Sql server (on premises) to Azure Sql DB with all connection details intact to repective tables(from azure DB). Alos we are moving current Company1 O365 to Company2 O365.



good article ...



The On-Premsises Data Gateway does not work on SQL Sever for Government.

MS rep confirmed this after we waited 2 months for an answer.

We hope this will be resolved soon - as we own the PowerApps and SQL Server  licenses but can NOT access our own data stored on our provate network.


(2) I have been using PowerApps for almost 1 year about 6-8 hours a day on projects with Sql Server 2016 for commercial client.  Sometime since last release (Sept?) the data gateway became flaky on a PowerApps form published in July that was working great.  I did a test a new form/screen - have 1 one text box to key in a customer #, and a data table using a filter on the Sql Server Customer table using Customer # (pk).   About every 5th time the data table shows the correct data - most of the time blank.



Meet Our Blog Authors
  • PowerApps Community Mentor 2018, Practicing surgeon and former Professor of Surgery, University of Illinois, Chicago.
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 12 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • My name is Timothy Shaw and I create digital solutions using the Power Platform, Office 365, and Azure SQL and handle the IT for a small company in the energy sector. When not building the future :), I enjoy playing guitar, good (or really bad!) sci-fi, Xbox therapy, and hanging with my wife and son. Twitter: @ShortForTim