Learn how to control devices from PowerApps using Raspberry Pi

I have recently purchased a Raspberry Pi 4 to explore IoT with Microsoft 365 platform. The Raspberry Pi is a low-cost credit-card sized computer which can be connected to a monitor, keyboard, mouse and to Internet via Wi-Fi or ethernet port. In addition, the Raspberry Pi has a 40 pin GPIO (General Purpose I/O) connector for us to connect sensors (Input) and to control devices (Output) through a relay. It enables people of all ages to explore computing and to learn how to program in languages like Scratch, Python, .NET core etc. One of the most popular operating system for the Raspberry Pi is Raspbian which is also the official one but there are also other operating systems like Ubuntu, Windows 10 IoT core (Not supported for Raspberry Pi 4). On this blog post, I will cover the different components used to integrate Raspberry Pi with Microsoft 365 service PowerApps & Azure services like Azure functions & Azure IoT hub to control devices. Find below the design and the different components used

  1. Environment Setup
    1. Raspberry Pi setup for IoT with .NET Core
    2. Visual Studio Code setup for remote development
  2. Azure IoT hub
  3. .NET Core Console Application
  4. Azure Function – HTTP Trigger
  5. Power Apps
    1. Custom Connector
    2. Canvas Apps

Environment Setup:

Raspbian OS is based on the Debain Operating System which has been optimized for Raspberry Pi hardware and it is the official one. You can find here some instructional videos on the following link to the install the OS on your Raspberry Pi

https://www.raspberrypi.org/help/noobs-setup/

If you have ordered a Raspberry Pi with a starter kit, most of the sellers would have loaded the Raspbian OS image on the SD card as a part of the kit. Once the OS is installed & configured, it is ready for use with the default username pi and the password is raspberry. Find below the schematic and the GPIO pin out diagram. On the sample code I have used Pin 17 and Pin 18 to control devices

Remote Tools:

There are tools to connect raspberry Pi remotely from your Windows client. Software xrdp provides a graphical interface for the users to remotely connect Raspberry Pi using Microsoft’s RDP mstsc.exe. Follow along this blogpost to set this up on the Raspberry Pi to enable remote connectivity. You can also use PuTTY a SSH tool to remotely connect with Raspberry Pi device. To know the IP address of the device, login to the Router to which the raspberry pi is connected or through the command hostname -I on the command line. To use PuTTY client & VS Code remote development plugin, SSH must be enabled on the Raspberry Pi OS. By default, it is disabled to enable follow the below steps

  1. Launch Raspberry Pi Configuration from the Preferences menu.
  2. Navigate to the Interfaces tab.
  3. Select Enabled next to SSH.
  4. Click OK

Raspberry Pi Setup for IoT with .NET Core:

.NET core is an open source development platform maintained by Microsoft and .NET community on Github. I have chosen .Net core and the programming language C# in which I am comfortable with. There are also python libraries to control GPIO pins in Raspberry Pi. To use .NET core IoT libraries install .NET core 3.1 on Raspberry Pi. Follow the instructions below to install .NET core

  1. Copy the Direct link of the .NET core SDK from the link for Linux ARM32. Based on information gathered from few blogposts .NET core is supported for ARM64 though Raspberry Pi is 64 bit. Get the latest link from https://dotnet.microsoft.com/download/dotnet-core/3.1
  2. Open a Terminal window in Raspberry Pi. Enter the following command to download the .Net core sdk binary

wget https://download.visualstudio.microsoft.com/download/pr/8a2da583-cac8-4490-bcca-2a3667d51142/6a0f7fb4b678904cdb79f3cd4d4767d5/dotnet-sdk-3.1.403-linux-arm.tar.gz

  1. Update the Raspbian OS by entering the following command

sudo apt-get update
sudo apt-get upgrade

  1. Run the following command to make the .NET SDK commands available for the terminal session

mkdir -p $HOME/dotnet && tar zxf dotnet-sdk-3.1.403-linux-arm.tar.gz -C $HOME/dotnet
export DOTNET_ROOT=$HOME/dotnet
export PATH=$PATH:$HOME/dotnet

  1. To make it available permanently on all the sessions. Run the following command to open the .profile file to save the information

sudo nano .profile

  1. Add the following lines at the end of the file by scrolling and then save it (CTRL+S) and then exit using (CTRL+X)

# set .NET Core SDK and Runtime path
export DOTNET_ROOT=$HOME/dotnet
export PATH=$PATH:$HOME/dotnet

  1. Run the command dotnet –info to know the version of the .Net core

Visual Studio Code setup for remote development:

You can develop applications remotely on a Raspberry Pi device using VS Code with the help of a plugin Remote Development which uses SSH to connect. After the plugin in installed, perform the following steps to remotely connect the Raspberry Pi device

  1. Have the IP address of the Raspberry Device ready which will be used to add a SSH host. Use the command hostname -I on the Raspberry Pi’s terminal window will reveal the IP address
  2. Go to the VS code and press CTRL+SHIFT+P together and type Remote-SSH: Connect to Host & select
  1. Click Add New SSH Host
  2. Type ssh pi@x.x.x.x -A and then press Enter. X.X.X.X is the IP address of your raspberry device and pi is the username (Default)
  3. Select the configuration file. I have used default, %USERPROFILE%\.ssh\config on Windows 10
  1. Host will be added. You are now ready to connect remotely provided the SSH is enabled on the Raspberry Pi.

Azure IoT Hub:

IoT hub is a managed service hosted in cloud that acts as a central message hub for bi-directional communication from the device to the cloud and the cloud to the device. There is a also a Free-Tier limited to one per subscription which can add up to 500 devices and 8000 msgs/day as of today based on the Pricing calculator. Go through the Microsoft documentation about IoT hub. Create a IoT Hub for us to send a message to the Raspberry Pi device for us control the device as per the instruction given in this article. After the IoT hub is created

  1. A device must be registered with your IoT hub before it can connect. There are different ways to register a device like using Azure Cloud shell, in this case we will use portal. Click IoT Devices under the Explorers blade on the IoT hub and click on + New, enter the Device ID and click save.
  1. Copy the Primary key of the registered device
  2. Copy the Hostname from the IoT Hub Overview blade
  3. These values will be used later in the .NET console application

Device Explorer:

It is a tool which helps you to manage devices by connecting to the IoT hub you have just created, it can be also done from the Azure portal, Azure Cli etc. It is very easy to connect to the IoT hub using the connection string. Download the device explorer from the https://aka.ms/aziotdevexp

To get the connection string, click Shared access policies under Settings blade and click iothubowner policy. Copy the Connection string-primary key and paste it on the Configuration section of the Device explorer and click Update as shown below

To send a message to the device click the tab Messages to Device and for registering new devices click Management.

.NET Core Console Application:

The Microsoft .NET core team also has a .NET core IoT library. The package System.Device.Gpio supports GPIO pins to control sensors (Pin Mode: Input) and devices like relay, LED’s (Pin Mode: Output). In this case we will be using the Pin no 17 & 18 to turn on or off a LED with Pin mode set as Output.

Setup for controlling devices:

To control a LED, connect a 220 ohm resistor to the long lead and the other end to a GPIO Pin (17 & 18) & the short LED lead to any one of the GPIO Ground.

In my setup I have used a Breadboard, GPIO Extension board & GPIO extension cable. GPIO Pin’s 17 & 18 are used, there are many other pins for us to use. Look at the GPIO pin schematics for more details on the pins. There are also relays designed for Raspberry Pi which helps controlling real devices, there are different relays module (4 channel, 8 channel, 10 channel etc) available in the market.

Connect Remotely to Raspberry Pi using VS code:

Connect the VS code to the Raspberry Pi using SSH by using keyboard shortcut CTRL+SHIFT+P and click Remote-SSH: Connect to Host and click the IP of the raspberry PI or the hostname based on the VS code setup (SSH Host) we have done earlier

After the password (Default: raspberry) is entered. If you see on the left bottom corner of the VS code with SSH: IP Address or host name, it is then successfully connected

In the terminal window you can enter all bash commands in context of Raspberry Pi.

VS Code Plugin:

To add a package from VS code to the Console app project install a plugin Nuget Packet manager. You can also use the CLI command on the terminal window to add the package but the plugin will help us to add different packages using the UI. All the packages will be installed on Raspberry Pi device as shown below

I also recommend you install to C# and CodeTour extensions (code walkthrough for the code I’ve used for this sample project).

Create the first Console Application to control a LED:

Follow the below steps to control a LED connected to GPIO PIN 22 using the .NET IoT package System.Device.Gpio

  1. On the VS Code terminal window, enter the command dotnet new console to create a new console application
  2. Add the package System.Device.Gpio using the nuget package manager plugin by CTRL+SHIFT+P > NuGet Package Manager: Add Package
  3. Add the following code to the Program.cs file
using System;
using System.Device.Gpio;

namespace DemoProject-GPIOControl
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Turning on Light from Pin 22");
            using var controller = new GpioController();
            controller.OpenPin(22, PinMode.Output);
            controller.Write(22, PinValue.High);
            Console.ReadKey();

        }
    }
}

  1. Run the command on the Terminal window dotnet run. There will be an unauthorized exception as below
Unhandled exception. System.UnauthorizedAccessException: Setting a mode to a pin requires root permissions.
 ---> System.UnauthorizedAccessException: Access to the path '/sys/class/gpio/gpio17/direction' is denied.
 ---> System.IO.IOException: Permission denied
  1. Enter the following command on the terminal window to provide root permission for Pin no 22: /usr/bin/gpio export 22 out. This command has to be executed everytime you restart the Raspberry Pi unless you provide root permissions to the account which could be done by setting a value on the root configuration file.
  2. Now run the dotnet console app using the command dotnet run which will turn on the LED light connected to PIN 22. The output voltage on PIN will be 3.2 volt if the pinvalue is set to High and will be Zero if its set to Low.
  3. Code controller.Write(22, PinValue.Low); will turn off the light
  4. To remotely debug on the Linux Arm, follow the instruction on this article.
  5. To disconnect in VS code, click File > Close Remote Connection

Console application connected to Azure IoT hub:

There is a .NET SDK for Microsoft Azure IoT to enable development using .NET and we will be using the package Microsoft.Azure.Devices.Client to connect client devices to Azure IoT hub. The other package used in this project is System.Devices.Gpio.

Use the Hostname of Azure IoT Hub, Device ID, Primary key of the Device copied earlier during the setup and the GPIO pins as shown below

private const string IotHubUri = “YourIoTHub.azure-devices.net”;
private const string deviceKey = “Your Key”;
private const string deviceId = “Your device ID”;
private const int Pin1 = 17;
private const int Pin2 = 18;

In the code the

  • Method deviceClient.ReceiveAsync() receives a message from the IoT hub queue
  • Method Encoding.ASCII.GetString(receivedMessage.GetBytes()) reads the message
  • Method deviceClient.CompleteAsync(receivedMessage, _ct) deletes the message from the queue

Do not forget to run the commands /usr/bin/gpio export 17 out and /usr/bin/gpio export 18 out based on the pins you are controlling. Then run the dotnet application using the command dotnet run. Now send a message from the Device explorer or from Azure portal IoT device explorer ON1 or Off1 to turn On/Off the LED connected to PIN 17 and ON2 or Off2 to turn On/Off the LED connected to PIN 18.

On this example we have used Cloud to device messages which sends a one way notification but you can also use Direct Methods & Device Twin to control devices, go through the following documentation from Microsoft with guidance to send cloud to device communications using different methods

https://docs.microsoft.com/en-us/azure/iot-hub/iot-hub-devguide-c2d-guidance

Find here the sample of the console application from GitHub. Now the console application is ready, let us create the Azure Function app to use it in the PowerApps.

Azure Function App – HTTP Trigger:

I’ve used a Consumption plan Function app which gets triggered on a HTTP request to send a message to IoT hub registered device using the method ServiceClient.SendAsync from the package Microsoft.Azure.Devices to send a one way notification to the registered Raspberry Pi device. The message will be sent to the device on the HTTP request as query string (Parameter name: name)

  1. Create a Function App from the Visual Studio 2019, I’ve used VS 2019 but you can also use VS Code
  2. Add the HTTP trigger with authorization Level of the Function App as Function
  3. Add the Nuget Package Microsoft.Azure.Devices
  4. Have the connection string handy for the iothubowner policy used on the device explorer.
  5. Copy the following Code:
 using System;
 using System.IO;
 using System.Threading.Tasks;
 using Microsoft.AspNetCore.Mvc;
 using Microsoft.Azure.WebJobs;
 using Microsoft.Azure.WebJobs.Extensions.Http;
 using Microsoft.AspNetCore.Http;
 using Microsoft.Extensions.Logging;
 using Newtonsoft.Json;
 using Microsoft.Azure.Devices;
 using System.Text;
 using System.Net;
  
 namespace FunctionApp_IoT
 {
     public static class Function1
     {
         static ServiceClient serviceClient;
         static string connectionString = "HostName=YourIoTHub-env.azure-devices.net;SharedAccessKeyName=iothubowner;SharedAccessKey=Yourkey";
         static string targetDevice = "Your Device ID";
         [FunctionName("Function1")]
         public static IActionResult Run(
             [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
             ILogger log)
         {
             log.LogInformation("C# HTTP trigger function processed a request.");
  
             string name = req.Query["name"];
  
             serviceClient = ServiceClient.CreateFromConnectionString(connectionString);
  
             SendCloudToDeviceMessageAsync(name).Wait();
             
             return new OkObjectResult(new { status = "Light turned On or Off" });
             
         }
         private async static Task SendCloudToDeviceMessageAsync(string condition)
         {
             var commandMessage = new
              Message(Encoding.ASCII.GetBytes(condition));
             await serviceClient.SendAsync(targetDevice, commandMessage);
         }
     }
  
 } 
  1. Publish the function app to Azure. Test it by sending HTTP requests using Postman tool or browser. The function API is ready, we are now ready to call the function in PowerApps
  2. Function app URL will be https://yourfunctionappsubdomain.azurewebsites.net/api/Function1?code=authorizationcode. Since I’ve chosen authorization level as function there will be a code

PowerApps:

So far we have progressed till a HTTP API endpoint using serverless which sends a message to the Raspberry Pi through the IoT hub, if we have to call this API on PowerApps we will have to create a custom connector which allows you to connect to any RESTful API endpoint. Bear in mind that to use a PowerApp which has a custom connector, the users should have a premium license.

Custom Connector:

Let us go ahead and create the custom connector, you can find here on the GitHub repo for the swagger definition file for creating the custom connector. Download the file and go to your Power Platform environment and click to the Custom Connectors link under Data.

Click the Import an OpenAPI file under New custom connector and import the Swagger definition file you have downloaded from the repo

Once it is imported, change the host on the General tab based on the function app URL and the Security will have authentication type as API Key and on the Definition tab there will be one action which be called from the PowerApps to control devices. After the settings are configured you can create the connector by clicking the link Create connector. You can test the connector by creating the connection by passing in the Code parameter of the function app and pass the message to test the operation. Make sure the console app is running in order to receive the message & to turn on/off the device.

PowerApps Canvas App:

Once the custom connector is created you can use it on the PowerApps canvas app by creating a connection to the connector like below

After the connection is created and added on the app, you can use on the PowerApps controls like Toggle, button etc to turn on/off the devices using the code ‘IOT-ControlDevice’.ControlDevice({name: “on1”}) / ‘IOT-ControlDevice’.ControlDevice({name: “off1”}). If a toggle control is used, the code will be something like this

If(
    ToggleL1.Value = true,
    'IOT-ControlDevice'.ControlDevice({name: "on1"}),
    'IOT-ControlDevice'.ControlDevice({name: "off1"})
)

Voila, now you were able to control devices from PowerApps.

Summary: On this post we have seen how to integrate Azure IoT with PowerApps and to control devices through a PowerApps. This is just a sample, you can extend this example based on your needs. Hope you have found this informational & interesting. Let me know any feedback or comments on the comment section below

Reference:

https://www.hanselman.com/blog/visual-studio-code-remote-development-over-ssh-to-a-raspberry-pi-is-butter

https://edi.wang/post/2019/10/6/azure-remote-controlled-light-with-net-core-30-on-raspberry-pi

https://docs.microsoft.com/en-us/azure/iot-hub/iot-hub-csharp-csharp-c2d

https://github.com/Microsoft/vscode-azure-iot-toolkit/wiki/Quickstart-.NET

https://docs.microsoft.com/en-us/connectors/custom-connectors/define-blank

https://jussiroine.com/2020/06/developing-remotely-on-raspberry-pi-4-and-linux-using-visual-studio-code

How to use a sample PCF component in your Power Apps

If you are PowerApps developer and wanted to extend the capabilities by bringing in third party or community driven PCF (Power Apps Component Framework) components, you can find lot of samples from the Power Apps community website PCF.gallery, Power Apps Community and from Microsoft for Model driven and Canvas apps.

Sample components from Microsoft

If you are new to component framework, I recommend going through the documentation from the following link:

https://aka.ms/pcfdocs

The PowerApps component framework enables the developers to create code components for model-driven and canvas apps. I have recently used a control from the PCF gallery community site, let’s see how to package and deploy a sample control to the Power Apps environment and then consume it on your Canvas app. There are two methods to deploy a code component:

  1. Import the solution in to CDS
  2. Power Apps CLI

To follow along the blog post, have the following available and installed on your environment

  1. Install Power Apps CLI and Node.js
  2. Access to Power Apps CDS Environment
  3. Developer Command prompt for Visual Studio 2017 or 2019
  4. Power Platform Administrator
  5. Enabling the PowerApps component framework on canvas applications

Method 1: Import the solution in to CDS:

For this post, I have chosen the React Face pile component from Microsoft Power Apps samples github repo. Follow the steps to create the solution ZIP file to be imported on the solutions gallery. If you already have the solution package, directly proceed to the Step 10.

Step 1: Download as a ZIP package and extract to a folder on your computer or git clone from the Microsoft Github repository. I have downloaded on C:\ PCF\Controls\sample-controls

git clone https://github.com/microsoft/PowerApps-Samples.git

Step 2: Open the Developer command prompt and navigate to the folder on the computer where you have downloaded the React Face pile component using the cd folder-path-react-facepile-component command e.g folder-path: C:\ PCF\Controls\sample-controls\PowerApps-Samples\component-framework\TS_ReactStandardControl

Step 3: Install all the required dependencies by running the command npm install

Step 4: Create a folder (e.g ReactStandardControlSolution) on the root of the React face pile component project (e.g C:\ PCF\Controls\sample-controls\PowerApps-Samples\component-framework\TS_ReactStandardControl) either manually or using the command mkdir ReactStandardControlSolution

Step 5: Navigate to the created folder by using the command cd ReactStandardControlSolution

On your command prompt, you should now be on e.g C:\ PCF\Controls\sample-controls\PowerApps-Samples\component-framework\TS_ReactStandardControl\ ReactStandardControlSolution

Step 6: Create a new solution project using the following command. The solution project is used for bundling the code component into a solution zip file that is used for importing into Common Data Service.

pac solution init –publisher-name developer –publisher-prefix dev

The Published-name and publisher-prefix values should be unique to your environment

Step 7: Add the reference using the command shown below. This reference informs the solution project about which code components should be added during the build. The path should to the root of the downloaded react face pile component and not to the newly created solution folder

pac solution add-reference –path C:\ PCF\Controls\sample-controls\PowerApps-Samples\component-framework\TS_ReactStandardControl\

Step 8: To generate the ZIP package, enter the following command

msbuild /t:build /restore

Step 9: The generated ZIP file will be available on \bin\debug\ folder once the build is successful

Note: Make sure there is no spaces on the folders you create to avoid deployment issues

Reference:

https://docs.microsoft.com/en-us/powerapps/developer/component-framework/import-custom-controls

https://docs.microsoft.com/en-us/powerapps/developer/component-framework/use-sample-components

Step 10: Now it’s time to import the solution to the solutions gallery by signing into Power Apps and select Solutions from the left navigation. On the command bar, select import and then browse to the Zip file solution created from the above steps. After the solution is imported successfully, the solution is available to use in Power Apps canvas and Model driven apps.

Reference: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/import-update-export-solutions

Let’s see the next method to deploy the code component

Method 2: Power Apps CLI:

In the previous method Power Apps CLI was used to generate the solution package and then the solution was imported to the gallery, on this method the code component will be directly pushed to the CDS service instance using the CLI push command.

Step 1: Create an authentication profile to the CDS instance by executing the following command on a command prompt, it’s not necessary to open a VS command prompt.

pac auth create –url https://xyz.crm.dynamics.com

To get the url sign into Power Apps and select your environment which has CDS in the top right corner and the environment you are planning to deploy the code component. Select the settings button in the top right corner and select Advanced settings. Now copy the URL from the webbrowser which should look like below

https://orgchangedhere.crm4.dynamics.com/main.aspx?settingsonly=true

The URL is https://orgchangedhere.crm4.dynamics.com/

Once your profile is successfully created, you should see the following message on your command prompt

Step 2: Navigate to the root folder of the custom component project using the cd folderpath command which has the .pcfproj file (e.g C:\ PCF\Controls\sample-controls\PowerApps-Samples\component-framework\TS_ReactStandardControl)

Step 3: Install all the required dependencies by running the command npm install

Step 4: Run the following command to push the code components to the CDS instance

pac pcf push –publisher-prefix contoso

Note: The publisher prefix that you use with the push command should match the publisher prefix of your solution in which the components will be included.

Reference:

https://docs.microsoft.com/en-us/powerapps/developer/component-framework/import-custom-controls#deploying-code-components

List of common PAC commands

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/view-download-developer-resources

The component is now ready to be used in the Canvas or a Model driven app after the code deployment using Method 1 or Method 2.

To add the component in a Canvas App:

Follow along then the documentation from Microsoft

https://docs.microsoft.com/en-us/powerapps/developer/component-framework/component-framework-for-canvas-apps#add-components-to-a-canvas-app

Find below the sample controls I’ve added on the Power App canvas app

To add the component in a Model Driven app:

https://docs.microsoft.com/en-us/powerapps/developer/component-framework/add-custom-controls-to-a-field-or-entity

Summary: You can also create a custom component from scratch or extend the functionality from the available samples based on your needs. Hope you have found this informational & helpful in some way. Let me know any feedbacks or comments on the comment section below

Multiple ways to access your On-premise data in Microsoft 365 and Azure

If your organization is using a hybrid cloud environment, this post will shed some light to integrate on-premise resources with Microsoft 365 & Azure services. Hybrid integration platforms allows enterprises to better integrate services and applications in hybrid environments (on-premise and cloud). In this blog post, I will write about the different services & tools available with in Microsoft Cloud which allows you to connect or expose your On-premises data or application in Office 365. There are still many enterprise organizations on Hybrid mode due to various factors. It can be a challenging task to integrate your on-premises network but with right tools & services in Office 365 & Azure it can be easier. Find below the high-level overview & some references on how to

  1. Access your on-premise data in Power Platform & Azure Apps (Logic Apps, Analysis Services & Azure Data factory)
  2. Programmatically access your on-premise resources in your Azure Function app
  3. Access on-premise resources in Azure automation account
  4. Expose your on-premise Application or an existing WEB API in Office 365 cloud

Access on-premise data in Power Platform & Azure Apps (Logic Apps, Analysis Services & Azure Data factory):

The on-premises data gateway allows you to connect to your on-premises data (data that isn’t in the cloud) with several Microsoft cloud services like Power BI, Power Apps, Power Automate, Azure Analysis Services, and Azure Logic Apps. A single gateway can be used to connect multiple on premise applications with different Office 365 applications at the same time.

At the time of writing, with a gateway you can connect to the following on-premises data over these connections:

  • SharePoint
  • SQL Server
  • Oracle
  • Informix
  • Filesystem
  • DB2

To install a gateway, follow the steps outlined in MS documentation Install an on-premises data gateway. Install the gateway in standard mode because the on-premises data gateway (personal mode) is available only for Power BI.

Once the data gateway is installed & configured its ready to be used in the Power platform applications.

  1. PowerApps
  2. PowerAutomate
  3. PowerBI

The other catch the gateway is not available for the users with Power Automate/Apps use rights within Office 365 licenses as per the Licensing overview documentation for the Power Platform. Data gateways can be managed from the Power Platform Admin center.

Shane Young has recorded some excellent videos on this topic for PowerApps & PowerBI.

To use in

  1. Azure Logic Apps
  2. Azure Analysis service
  3. Azure Data Factory

create a Data Gateway resource in Azure.

High Availability data gateway setup:

You can use data gateway clusters (multiple gateway installations) using the standard mode of installation to setup a high availability environment, to avoid single points of failure and to load balance traffic across gateways in the group.

No need to worry about the security of the date since all the data which travels through the gateway is encrypted.

Data gateway architecture:

Find below the architecture diagram from Microsoft on how the gateway works

I recommend you to go through On-premises data gateway FAQ.

Integration Service Environment:

As per the definition from Microsoft an integration service environment is a fully isolated and dedicated environment for all enterprise-scale integration needs. When you create a new integration service environment, it’s injected into your Azure Virtual Network allowing you to deploy Logic Apps as a service in your VNET. The private instance uses dedicated resources such as storage and runs separately from the public global Logic Apps service. Once this logic apps instance is deployed on to your Azure VNET, you can access your On-premise data resources in the private instance of your Logic Apps using

  • HTTP action
  • ISE-labeled connector for that system
  • Custom connector

For the pricing of ISE, refer this link.

Programmatically access your on-premise resources in your Azure Function app

As you all know Azure Functions helps in building functions in the cloud using serverless architecture with the consumption-based plan. This model lets the developer focus on the functionality rather than on infrastructure provisioning and maintenance. Okay let’s not more talk about what a Function app can do but let us see on how to connect to your on-premise resources (SQL, Biztalk etc) within your function.

During the creation of a Function app in Azure, you can choose the hosting plan type to be

  • Consumption (Serverless)
  • Premium
  • App Service plan

Consumption based plan is not supported for the on-premise integration so while creating the app the hosting plan has to either premium or app service based plan & the Operating system has to to be Windows. On-premise resources can be accessed using

  1. Hybrid Connections
  2. VNet Integration

Hybrid Connections:

Hybrid Connections can be used to access application resources in private networks which can be on-premise. Once the Function app resource is created in Azure, go to Networking section of the App service to setup & configure. Go through the documentation from Microsoft for the detailed instructions to set this up.

How it works:

The Azure Hybrid Connection represents a connection between Azure App Service and TCP endpoint (host and port) of an on-premise system. On the diagram below Azure Service Bus Relay receives two encrypted outbound connections. One from the side of Azure App Service (Web App in our case) and another from the Hybrid Connection Manager (HCM). HCM is a program that must be installed on your on-premise system. It takes care of the integrations between the on-premise service (SQL in this case) with Azure Service Bus Relay.

Once the setup is done, you can create a connection string in Appsettings.json file or from Azure function app interface of your function app. After this you can access the data in your function app code.

I’ve found a couple of interesting blogs about this setup.

VNet Integration:

In the Networking features of the App service, you can add an existing VNET. An Azure Virtual Network (VNet) is a representation of your own network (private) in the cloud. It is a logical isolation of the Azure cloud dedicated to your subscription.

In Azure Vnet you can connect an on-premise network to a Microsoft VNet, this has been documented from Microsoft here. Once there is integration between your Azure Vnet & on-premise network and the VNet is setup on your function app you are set to access on-premise resources in your function app.

Access on-premise resources in Azure automation account:

Azure Automation is a service in Azure that allows you to automate your Azure management tasks and to orchestrate actions across external systems from right within Azure. Hybrid runbook worker feature allows you to access on-premise resources easily. The following diagram from Microsoft explains on how this feature works

I’ve written a blogpost recently about this feature for automating on-premise active directory.

Expose your on-premise Application or an existing WEB API in Office 365 cloud:

Azure Active Directory’s Application Proxy provides secure remote access to on-premises web applications (SharePoint, intranet website etc). Besides secure remote access, you have the option of configuring single sign-on. It allows the users to access on-premise applications the same way they access M365 applications like SharePoint Online, PowerApp, Outlook etc. To use Azure AD Application Proxy, you must have an Azure AD Premium P1 or P2 license.

How it works:

The following diagram from Microsoft documentation shows how Azure AD and Application Proxy works

Find below documentations on how to

  1. Add an on-premises application for remote access through Application Proxy in Azure Active Directory
  2. Secure access to on-premises APIs with Azure AD Application Proxy
  3. Use Azure AD Application Proxy to publish on-premises apps for remote users
  4. Deploy Azure AD Application Proxy for secure access to internal applications in an Azure AD Domain Services managed domain

Once the connector service is installed from your Azure AD application proxy, you can add an on-premise app as shown below

The above step will register an application with App registrations.

Summary: I’ve given some overview about the different services & tools to connect & integrate on-premise resources with Microsoft cloud. Hope you like this post & find it useful. Let me know any feedback or comments on the comment section below

Restricting Power Apps users from viewing or updating the SharePoint List content from SharePoint GUI

There are many developers & power users using SharePoint lists as a data source to store data submitted from a Power App. There should be various reasons for this, please find mine below

  • Enterprise users with license (E5, E3, E1) has access to Power App & SharePoint as a service
  • SharePoint is a standard connector & not a premium one which means the user would be able to access SharePoint with the license they have assigned. Its not required for the users to have an additional license (App based license or user based license) to access the premium connectors like Azure SQL etc
  • There are many triggers & actions available for SharePoint connector

Let’s say on the Power App you have developed or developing has many custom logics on different screens. For e.g data being stored on different lists on click of a button in a Power App screen/form. If the user has to store data to SharePoint from Power App, user should have at the least edit/contribute access which means the user to be added on the out of the box site members group.

If the power app user gets to know the SharePoint site url which is connected as a data source in the Power App, the user would be able to access all the lists from the site by going to the Site contents page or by directly going to the list view url. This might create an issue because the user might edit, create or delete an item from the SharePoint interface rather from the Power Apps interface. As an app owner, you would like to avoid this. There is a way to avoid this by creating a custom permission level for the app users in SharePoint. Please find the steps below

  1. Go to the Site Permissions
  2. Click Permission Levels on the Ribbon
  3. Click an existing permission level “Contribute” from the list.
  4. You will be navigated to Edit Permission level for “Contribute”
  5. Scroll the page to the bottom & click on the button “Copy Permission Level”
  6. Name the permission level as “Power Apps – Custom Permission” & give some meaningful description
  7. Remove/Uncheck the permission “View Application Pages” under the category “List Permissions”
  8. Scroll down and click “Create” button

The custom permission level is ready, let’s assign this to a new SharePoint group. Find steps below to create a new group & assign the custom permission level to the group

  1. Go to the Site Permissions
  2. Click “Create Group” on the ribbon
  3. Name it as “Power App Users”
  4. Change the Owner field to the Site Owners group – Optional Step
  5. Scroll down & select the Permission level created above
  6. Click Create

The group is ready, add the power app users to this group. This access will not allow the members of this group to access the Site contents (Application Page), view forms or views (Allitems.aspx) but they would be able to create/update from Power Apps. Technically the user would still be able to access/modify the list content programmatically using CSOM/JSOM etc, but the GUI in SharePoint is blocked. They will receive an access denied message while trying to access the list view/form or site contents page.

Note:
After the users are added to the group which has the restricted permission level, make sure the users are not part of others groups like Members, Visitors etc. You can confirm this from the Check Permissions on the link https://domain.sharepoint.com/sites/sitename/_layouts/15/user.aspx

Summary: You can play around with the different types of permissions while creating the permission level for Power Apps users, grant only the required permissions. A site owner will have access to create or update permission level. Microsoft has very detailed documentation on permission levels. In a SharePoint site, the permissions are inherited from the Site>Lists>Item. Based on the need, the permissions can be broken at any level. Hope you find this post useful. Let me know if there is any comments or feedback by posting a comment below.

Automate the backup of your Flow & Power Apps canvas application

Microsoft Power Automate & Power Apps have turned quite important & popular service now a days within Office 365, there are many users (developers & IT pro users) who are creating business applications leveraging these services very quickly but if an app or a flow is deleted by mistake then its not an easy job to spin it back up with out involving Microsoft support. In this blog post, am going to give you some ideas to back up a Flow & Power Apps automatically with the help of connectors available in Power automate. Once it is backed up, I will show you steps to restore from the back up.

Once I’ve deleted a flow accidentally and I was not able to restore it back, I have recreated the complete flow with all the steps which took some time. I will show you steps to back up & restore your Flow & Power Apps.

  • Backup & Restore your Flow
  • Backup & Restore your Power Apps

Backup & Restore your Flow:

A flow could be backed up by copying the flow definition & connection references with the help of an action “Get Flow” under the flow connector “Flow Management”. With the flow definition & connector references we would be able to restore it back whenever needed. Thanks to John Liu for the tips & guidance on showing us a way to copy the flow definition. Before showing you steps to get the flow definition & connection references, I will show you some examples on the type of information it stores on these fields.

Flow Definition:

It has details about the triggers & actions used with in a flow in a JSON format.

Flow Definition

Triggers: Information about the trigger used in the flow (Instant, Automatic, Scheduled)

Actions: Information about all actions used in the flow

Connection References:

It has details about all connections used against each action

Connection References

Add the action “Get Flow” under connector “Flow Management”, which has the fields flow definition & connection references. This action would be able to get only the flows to which you have access to. If you don’t see the flow under the drop down, enter the Flow ID by selecting custom value.

Now let’s store the Flow definition & connection references to a file in some location so that we would be able to retrieve the files for restoration. On this example I will use “One Drive for business connector” action “Create File” to store the content in two files but you can also get it stored on SharePoint Document Library, Azure Blob, GitHub etc. The extension of these files can also be .txt but I’ve used .json.

One Drive for Business connector

Restore Flow:

Flow action “Create Flow” helps us to restore flow from the flow definition & the connection references stored on One drive. Only the file content should be passed for the Flow Definition, connectionReferences and not the actual file.

Backup & Restore your Power App:

A Power App could be backed up by creating a MSAPP file for the PowerApp to be backed up. The file with extension .msapp is just a ZIP file which has all the definitions of the app. There are open source tools (Review Tool, Theme editor, App merger, Phone to tablet converter) available in github which helps us to peek at the definitions of the .msapp file.

PowerApps Review Tool

PowerApps for App Makers connector has an action called “Get App” which helps us to get all the information related to the specified app. This action has a field called “readonlyValue”. It points to a link that has the PowerApps msapp file. With the help of the premium connector HTTP, we will make a GET request to the readonlyValue link (Blob storage path). This step allows you to retrieve the contents of the msapp file which could then be stored as a backup file in Onedrive, SharePoint, Github etc. Lets now look at the contents of the msapp file, change the extension of the .msapp file to .zip file and open it.

AssetsAll the media files (Images, Videos, Audio)
ControlsInformation about all the controls (First Party, Custom components etc) used in the different screens of the app. Data is in JSON format.
ReferencesInformation about Datasources, Templates (Label, Gallery, Textbox etc), Resources (Images etc), Themes used in the app etc
ResourcesPublish information, user locale etc
Properties.jsonInformation of the app like Author, PowerApp name, no of screens etc
Properties.json file

Add the action “Get App”, enter the App ID of the Power App

Get App action

This action retrieves all the information of the app including the “readonlyvalue” as explained above. Using this result we will make a GET request using the HTTP connector to retrieve the app data in this Flow.

HTTP (Premium connector)

Add the action “Create file” in OneDrive for Business connector and set the body from HTTP connector. This allows you to save your app to OneDrive for Business. As said earlier you can instead store it to other storage like Azure Blob Storage, SharePoint document library, GitHub etc.

Restore Power App:

The msapp file is with us now, lets now restore the PowerApp. Create a Blank app from the Power Apps studio then open the.MSApp file via the File > Open > Browse menu option. This enables you to select the .MSApp file from your local file system then save & publish the App. The app is now back. Let me know on the comment section if there is a automated way to restore the PowerApp from the MSApp file.

Summary: On this blog post, we have seen ways to backup & restore your app & flow using a Flow. If you are an Power App/Power Automate environment admin (Premium license), you could backup all the apps & flow available in the environment using the Apps for Admin connector. There are Power Shell cmdlets available for Admins & creators which could also be used. If you have a more complex business application including PowerApps Canvas, multiple flows, custom connector, Model driven apps, entity etc, solutions is recommended. Exporting a solution as a package is very easy by click of a button, restoring is also quite an easy task. Hope you find this post useful, let me know your feedback on the comments section.

Managing users for a Power App with SharePoint as a data source

In this blog post, I am going to show you how to manage users for a Power App which has SharePoint as a data source. Let’s take an example, you’ve built a Power App application which has its data stored in SharePoint. After the application is developed, you want to share the app with some users. To do so you’ll have to give the user access to

  • PowerApps
  • Access to the Data source (SharePoint in this case), it could be Read/Write based on the use case

Only after the user has been granted access, they would be able to use the application. To setup the process for managing users we are going to use an action “Edit App Role Assignment” under the connector “PowerApps for App Makers” in the flow. Find the steps to executed to make this happen

  1. Create an Interface/screen for the Admins in PowerApps (custom role) to maintain (Add/Remove) the users of the application
  2. Flow for granting access to the users
    1. Action to grant access to the PowerApps
    1. Action to add the user to the SharePoint site

Step 1: Create an Interface for the Admins in PowerApps to manage the users for the application

Create a list (User Roles) on the SharePoint site which acts as a data source for application with the below schema to maintain the users

ColumnType
UserNamePerson or Group
RoleChoice (value: Admin, User)
RoleStatusSingle line of text (Default value: Add)
List Schema for UserRoles

Add a screen on the Power App as shown on the image below. I’ve given some information on the image the type of controls I’ve added on the screen.  This screen will be shown only to the Admin role, the navigation to this screen could be based on click of a button placed somewhere on the screen. The visibility of the button to navigate to the admin interface could be set based on the data (Role) from the list User Roles list. In this example, I will have two roles an Admin and User. Find the below screen in PowerApp built for managing the users

PowerApp Admin Interface screen

Once the user name and the role has been entered on the section 1, click add which adds the user information to the list User Roles. Once a user is added, the flow will be triggered which has actions to grant access for a Power App.

Step 2: Flow for granting access to the users

I will be using a flow to grant access to PowerApp & SharePoint site. Let’s build the automated flow with trigger “When an item is created or modified” connected to the list User Roles

Automated Flow Trigger

Add the action “Get User profile”, the input should be the email address of the user who must be given access to PowerApp. This step is required to get the user guid, to be given on a later step for granting access to PowerApp action

Get User profile

Now Add the action “Edit App Role Assignment” under the connector “PowerApps for App Makers”, this action is in preview mode by the time I am writing this post. To get more details on the different actions and its parameters with this connector go through this link. Make sure the connection to this action has access (Owner/Co-Owner) to share an App to a user.  You would be able to get the GUID (App ID) of the Power App by going to the details section of an app.

Edit App role assignment flow action

Now let’s add an action to grant the user access to SharePoint site. I would be using a flow action “Send an HTTP request to SharePoint” to call a Rest API (POST) to get the user added to the SP site groups (Members/Viewers/Visitors). The connection for this action should have access (Site Owner) to share the site. Find the rest api details to add the user to a group

URI: _api/web/sitegroups(groupId)/users

Method: POST

Request Body: {‘LoginName’:’i:0#.f|membership|Emailaddressoftheuser’}

To get the SharePoint group id, navigate to the url https://domainname.com/sites/sitename/_layouts/15/user.aspx and click the group name and copy the URL which will have the groupid at the end.

https://domainname/sites/sitename/_layouts/15/people.aspx?MembershipGroupId=3

Find the action with the configurations

SP HTTP request action

Execute the flow by adding the user to the list from the PowerApps interface, the flow gets triggered which will in turn grant the user access to PowerApp & SharePoint. Tada!!!

To revoke the access to a user, if you scroll back to the admin interface image for managing the user there is a delete button. Assume that the delete button click will update the column status on the User Roles list to “Revoke” for a user which would then trigger the same flow. On the same flow add a Switch which would then based on the value in the RoleStatus (Add/Revoke) column, add steps to revoke the access. This could be achieved in multiple ways. For e.g After the delete button is clicked the item on the UserRoles list can be deleted which will in turn call a flow created using the trigger “When an Item is deleted” with actions to revoke access

Action to Revoke Power App access, its the same action we used for adding user to an app but with some different parameters as shown below

Edit Role assignment – Remove user

The rest api details for revoking the user access from a SharePoint group

URI: _api/web/sitegroups(groupId)/users/removeByLoginName

Method: POST

Request Body: {‘loginName’:’i:0#.f|membership|Emailaddressoftheuser’}

The action to grant access to Power App can also be called from a Power App by creating a connection to the data source “PowerAppsforAppMakers” as below

Editapproleassignment from powerapp

Summary:This approach would help the super users of the app to manage user permissions for the Power App within Power Apps interface. On this post we have seen adding the user permission to SharePoint data source. Based on needs there should be ways to add users to different data sources if there’s an api endpoint or with the help of standard connectors available in Power Platform. Let me know any feedback or comments on the comment section below