When building solutions in a cloud-based platform, you may need to connect the apps you are designing to data systems that exist within your secured, on-premises environment. When building apps on the K2 platform hosted in the cloud, there are multiple options for connecting to data sources that exist in both on-premises and cloud configurations. The purpose of this article is to detail the steps to use the Microsoft Azure platform to work with on-premises data from SharePoint 2013/2016 and Microsoft SQL Server in K2.
Several different technologies used in this approach allow you a no-code method to expose on-premises data and are all native capabilities in both the K2 platform and integration capabilities provided by the Microsoft Azure platform, including:
Each technology works in combination with the others to allow you to surface data that lives in on-premises systems into cloud-based K2 applications as in this diagram.
High Level Configuration Steps
The On-Premises Data Gateway is a product available for Microsoft customers that allows you to expose data into cloud offerings such as K2 Appit. The LOB data is currently limited to Microsoft SharePoint 2013/2016 and Microsoft SQL Server. Details on how to obtain, install and configure the On-Premises Data Gateway can be found in the Microsoft topic Install the on-premises data gateway for Logic Apps
Once you make the connection to on-premises data sources, you can design an Azure Logic App to interact with those data systems (i.e. query Microsoft SQL Server or create a SharePoint list item). To interact with this Logic App you need to ensure that it is triggered with an HTTP event as pictured in the example here:
In this example, the Get rows action queries an on-premises SQL Server instance using the On-Premises Data Gateway, and selects records from the Sales.Customers table. The recordset is converted into JSON in the Response activity which is returned to the caller.
Details on how to ensure the Logic App is HTTP accessible can be found in the Microsoft topic Call logic apps by creating and configuring trigger endpoints
To expose the Logic App in a format that the K2 platform can use, you must create an API Management endpoint that you then use the K2 REST service broker to call. More details on how to configure and deploy API Management are available in the Microsoft topic Manage your first API in Azure API Management
In this example, we start by configuring the underlying API from the Logic App that was created in the previous step:
Microsoft Azure Logic Apps only allow for POST operations to be recognized by the Logic App runtime. To produce an endpoint that the K2 platform can use to query and retrieve data, change the expected operation on the front-end of the API Management from a POST to a GET via the Form-based editor option as pictured here.
Next, map the front-end GET operation to the back-end POST operation via the Backend configuration using the Form-based editor option as pictured here.
Now that a REST API is available in Azure API Management, download the Swagger definition using the Developer Portal of the Azure API Management application as pictured here.
You can find the option to get the Swagger file using the API definition button.
The Swagger file generated for this example:
With the Azure API Management REST endpoint exposed as a K2 SmartObject, the on-premises data can be used in applications built in K2, including both workflow and forms. Execute a SmartObject method directly or build a form based on the SmartObject.
Additional & Linked Resources