I’ve already shown you how you can build a mobile app for CRM in 15 minutes. This time I will show you how you can create an app to manage an Azure SQL Database. Please read my other blog post first if you don’t know PowerApps yet. As you can read in that post, I initially tried to build an app connecting to an Azure SQL Database unsuccessfully, but after contacting the excellent PowerApps support I fixed it!
Set up an Azure SQL Database
Call me old fashioned, but I’m still using the old Azure Management Portal. Go to SQL Databases, and click New => Custom Create. Fill out the details as follows:
I’ve decided to create a new SQL Database server. On the next screen you will need to fill out the details. Remember your password and login name.
Click the check mark, and wait until your database and server are provisioned. In the meantime you can download SQL Server Management Studio 2016 (SSMS) to be able to manage your database.
The next thing to do is to add your client IP as an exception to the firewall, otherwise you won’t be able to access it. Click on the new created database, and then Manage allowed IP addresses
In the next screen, your current IP should be entered automatically, you just need to add it. Keep in mind that everyone with access to your app must be added as an exception, so you may want to add a range of IP addresses instead of only yours. If you really don’t care about security, just add 18.104.22.168 – 255.255.255.255 to open the database to the whole world.
Create a new table in your database
You will need to create a table with some fields in your database. If you click on your database in the Azure Portal, you’ll see “Connect to your server”. Below you will find the server name. Start up SSMS and enter the server name, and the credentials. You will need to set Authentication to SQL Server Authentication.
Next, add a table to the powerappstest database called Customers and add four fields:
- ID: number, Primary Key (right click => Primary Key), auto increment (set property Identity Specification to Yes)
- FirstName: nchar(10)
- LastName: nchar(10)
- Address: nchar(10)
Yes, I know, nchar(10) is not enough, but it will be easier to show something pretty cool later on. The structure now should look like this:
If you forget to add a Primary Key, you will get an error when connecting from PowerApps. There was an unexpected error while creating your app. Please try again. will show up. After contacting Microsoft, they asked me to do a Fiddler trace. And then I found out the real error, the table must have a PK to work:
Add some data!
Right-click the table, edit it, and add a couple of rows:
Cool, the data has been set up, it’s time to create the app!
Open the Windows 10 app PowerApps, and click New => Start from your data. Go to Available Connections => SQL Azure. From the Azure Portal, click “Show Connection String”, copy the first one (ADO.NET) to Notepad, update the password, and then copy the entire string to “SQL Connection String” in PowerApps.
Click Connect at the bottom right, select “default” as the data set, and then Customers as the table. Then, wait until the magic is ready. You don’t need to change anything, jump right into the app by clicking the Play button at the top right. You will initially see all records in the database:
And if you click on an item, you can update it. Remember that we set each field to nchar(10)? This validation is added automagically to the app! How awesome!
You can share the app within your organisation, please have a look at my previous blog post how to share an app.
And in a couple of minutes you’ve created another mobile app without writing one letter of code.