Power platform is all built around data. Luckily, this data can reside anywhere, thanks to the Connections feature provided by the platform. If you want your Apps to interact with data from a local database hosted on a server somewhere then this is how you do it. This has a lot of potential from master data management to compliance to regulations and many more. I will go through a simple example starting from creating the database then to building a Canvas App around the data.
Step 1: Create the database and a table (if you don’t have one already). In your server, create a database or use an existing one. In this case, I created a DB called Master Accounts.
To simulate a CRUD operation later, I created a table called Accounts in this database using the following script. Make sure to specify a Primary Key or Canvas App will make your app read only without the ability to add/delete records.
Step 2: Create a user that can access this database. This is achieved by creating a “login” in the security database in your server and assigning this login the created database.
Step 3: To give the Power Platform the ability to interact with you local database, you need to install the On-Premises Data gateway. Notice that this gateway can be configured to work with all the Power Platform apps or only Power BI, of course we need the former option. After you are done, the gateway interface will look like the following image.
Step 4: Sign in with you Power Platform Admin Account:
When done, this is what you should get:
Step 5: Connect to the database from Power Platform. Visit make.powerapps.com and on the left pane, select Data and Connections. Select the New Connection from the command bar.
In the new Connection wizard, select the type of connection to be SQL Server and you should see a window similar to the following:
Of course, you can authenticate in different way, but I choose the SQL Server Authentication mechanism. Plug the values we created in the first two steps. Make sure that the correct gateway is selected.
If all is well, you should see a new connection in the list, mine looks like this:
Now let’s go and create a canvas app from data and see the magic!
Step 6: Create a Canvas App starting from SQL Server Data.
Select the “Accounts” table we created before and hit Connect:
And now you should end up with a Canvas app the can perform CRUD Operations on the local database. It will require some redesign though:)
The nice thing is that this connection is a Power Platform Connection now so you can use it with Flow!
This capability opens a lot of doors for the organizations that are hesitant to move their data up in the clouds, so go experiment with it and use it!
Transform 365 