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

Automate the provision of On-Premise AD Account – Part 2

This post is in continuation to my previous post Automate the provision of Azure AD Account & License assignment – Part 1 for creating account in Azure active directory using Power Automate. On this post I will highlight the feature available in Azure Automation account which can be leveraged to create an On-premise AD account. Refer to this post for the usage of Azure automation account to interact with SharePoint online in Microsoft 365 using Power Automate.

Azure Automation is so easy to setup for automating tasks that interacts with

  • Azure (Azure AD, SQL etc)
  • M365 services (SharePoint etc). 

Automation runbooks in Azure might not have access to resources in other clouds or in your on-premises environment because they run on the Azure cloud platform. To access local resources like On-premise Active Directory which lives behind the firewall, there is a feature within Azure Automation called as Hybrid runbook worker. Azure Automation Hybrid Workers extends Azure Automation into your private networks and allows running runbooks that interacts with resources such as on-premises Active Directory, SharePoint etc.

Hybrid Runbook Worker feature to run runbooks directly on the

  • Computer in On-premise network
  • Any secured network like a virtual machine in Azure behind the firewall
  • Cloud services like AWS etc

that’s hosting the role and against resources in the environment to manage local resources. Refer to this documentation about Hybrid runbook worker for more information.

The following image from Microsoft documentation illustrates this functionality:

Pre-requisite:

  1. Azure Subscription to create
    • Automation Account
    • Log Analytics Workspace
  2. Server (Windows or Linux based)
    • Internet access
    • Port: Only TCP 443 required for outbound internet access

The deployment instructions from Microsoft for

I’ve found a really interesting video on Youtube from Travis Robert regarding this topic to set this up on Windows workstation.

Once the Hybrid runbook worker is setup, you can write PowerShell script to create account in On premise Active directory. Find the link to the script to add a user to Active directory in Onpremise. The parameters for the AD account (attributes like name, location, jobtitle, manager etc) to the runbook can be passed from a SharePoint List in Office 365 which could then be triggered using a Flow.

Summary: I was not able to give you a complete walkthrough but I hope had given some pointers to connect on-premise AD. Forgive my ignorance if I have made some mistakes since I don’t have much experience with IT infrastructure. Hope you find this post useful & informational. Let me know if there is any comments or feedback below.

Access Active Directory user profile attributes using Graph API

Using graph API you can access all the Active directory attributes. The me endpoint gives your profile information https://graph.microsoft.com/v1.0/me. To get a specific user’s information the endpoint should be https://graph.microsoft.com/v1.0/users/useremailaddress

For getting any specific AD attribute you can pass the required attribute as a query string https://graph.microsoft.com/v1.0/me?$select=jobTitle,department,displayName

In bigger active directory implementation, there will also be information stored on the Extension attributes, to get the information you will have to pass the name of the attribute “OnPremisesExtensionattributes” as a query string in the format as

 https://graph.microsoft.com/v1.0/me?$select=jobTitle,department,displayName, OnPremisesExtensionattributes

Graph explorer is a nice tool to test the Graph endpoints. Refer the documentation from Microsoft on the different available AD endpoints

Graph Explorer

The beta ME endpoint https://graph.microsoft.com/beta/me gives more information of the user

The User profile service Rest API endpoint in SharePoint http://siteurl/_api/SP.UserProfiles.PeopleManager/GetMyProperties does not provide all the active directory information. For e.g Location, OnPremisesextensionAttributes etc information is not available. SharePoint UPS synchronizes the AD data of all users in schedule basis. So the other option is to use the Graph Endpoint if you need those information for your application customization.

Endpoint point to get a specific attribute is

https://siteURL/_api/SP.UserProfiles.PeopleManager/GetMyProperties?$select=PictureUrl,AccountName

For more information about different endpoint, refer this documentation from Microsoft.

Automate the provision of Azure AD Account & License assignment – Part 1

A decade back I was part of a team to automate the On & Offboarding process of employees for a customer using .NET framework, it had a module to provision user accounts in an on-premise environment. I still remember having used couple of dll’s for Active directory 2003 & exchange 2007 to create AD & Email account. It was not easy but nowadays with the Office 365 in place its so easy to create account & enable different Office 365 services (Exchange, SharePoint, Yammer etc) for a user in Azure Active directory. This example will be applicable for the Organization which does not have On-premise Active directory. Organizations having On-premise active directory, the user account’s will be synchronized from On-premise AD to Azure AD. On this post I am going show you how to

  1. Create Azure AD account & assign license using Power Automate
  2. Assign License using Graph Endpoint

Create Azure AD account & assign license using Power Automate:

There is a Power Automate action Create user under the connector Azure AD which helps us to create account in Azure AD but there is no action as of now to assign individual license to a user but we can overcome this by adding the user to the AD security group which has a license assigned to it.

There is a flow action Add user to group under the same connector for adding the user to the security group, all the members of the group will get the license assigned on that group. The Azure AD connector does not return custom attributes of Azure AD. For e.g you can’t assign a value to a custom AD attribute with the Create user action, if you want to assign a custom attribute or an attribute which is not exposed in the Create User action then the account has to be created using PowerShell. There are ways to call a PowerShell script from Azure Automation Runbooks with the help of a flow action.

Other Azure AD actions apart from the above screenshot which could be of use are

  • Create group
  • Get group members
  • Get groups of a user
  • Get user
  • Remove Member from Group
  • Update user

There are templates available in Power automate template section which helps you create account based on the information from the SharePoint List, based on HTTP request etc

Prerequisite:

  • Permissions on Azure AD:
    • Group.ReadWrite.All
    • User.ReadWrite.All
    • Directory.ReadWrite.All
  • Security group with license assigned

For assigning a license to Security group, go to Azure AD Admin center. Follow this documentation from Microsoft to assign license to a group.

You can also turn off certain services from the license to the group, for e.g Turning off the Power App service for the user

You can also use dynamic groups for assigning license to a user, if you have dynamic group based license assignment to a user then you could ignore the step on the flow to add user to the security group. Dynamic groups works based on rules to determine group membership, for e.g if a user has an AD attribute set for Department. In this case the AD user created with certain department will get automatically added to the group which will in turn assign a license to the user.

Let’s now create the flow, I have used an Instant flow with trigger Manually Trigger a flow. Add the action Create user from the connector Azure AD

Now add the action Add user to group, the Group Id should be for the Security group which has a license assigned to it. The User Id field should be dynamic value Id from the previous action Create user.

To get the group Id, go to Azure AD

Run the flow. Once the flow runs successful the user account will be provisioned on Azure Ad with a license.

Assign License using Graph Endpoint:

There is a beta graph endpoint to assign license to a user. Find the Microsoft documentation for more information

All types of license (E5, E3, PowerApps, Power etc) has a Service Plan id also called as SKU id. Find the list of SKU id’s on this link if your tenant has procured the license for the service

 To get the available service plan or SKU ID, make a GET call to the endpoint https://graph.microsoft.com/v1.0/subscribedSkus & also from the beta endpoint of the user https://graph.microsoft.com/beta/me

Once the sku id are available based on the type of license to be assigned, you will have to make a POST call to

Endpoint URL: https://graph.microsoft.com/beta /users/testuser10@mydevashiq.onmicrosoft.com/assignLicense

Request Body:

{
  "addLicenses": [
        {
            "disabledPlans": [],
            "skuId": "b05e124f-c7cc-45a0-a6aa-8cf78c946968"
        },
        {
            "disabledPlans": [],
            "skuId": "a403ebcc-fae0-4ca2-8c8c-7a907fd6c235"
        }
  ],
  "removeLicenses": []
}

The first SKU id is for Enterprise Mobility & Power BI (Free)

To remove the license for a user, use the collection removeLicenses. This graph endpoint to assign license can also be called from a Flow.

Summary: You can also use a HTTP request trigger in the Flow for integrating with other applications. On next post I will write about creating account in On-premise Active Directory. Hope you find this post useful & informational. Let me know if there is any comments or feedback below.