Summary

To break this series apart into more manageable chunks, in this installment, we’ll focus on setting up the API in Azure API Management (APIM) and the custom connector. If you missed the first installment where we set up our On-Premises Data Gateway and Azure Logic App, please check it out here.

Scenario

The company has an on-premises SQL Server database that contains customer data that needs to be available to various apps built on the Power Platform and/or Azure.

Assumptions

  1. That you have a sample of the data your Logic App returns. For this write-up, we’ll use the data returned from the Logic App we created in our previous lab.

High-Level Steps

  1. Create an Azure API in Azure API Management (APIM) that provides access to the Logic App, and can be called from various Power Apps and Power Automate Flows
  2. Create a Custom Connector

Azure API Management

In this section, we’ll walk through setting up an API to the Logic App we created in our prior installment.

Why use Azure API Management (APIM)?

Azure APIM provides a single place for managing your company’s APIs. It can be used to selectively expose data and services to employees and partners by applying authentication and even usage limits.

Create the API

  1. Create an Azure API Management service instance in Azure. You can follow steps to do that here. In the Azure search bar, type API, and select API Management services. Provide the necessary information and click the Create button. Once complete, your API Management service should show as ‘on-line’.APIM 1Azure API Image 2Azure API Image 3
  2. Click to open your new APIM service. Select API’s from the blade that opens, and either select the Logic App tile or search APIs for Logic App and select it.
    Azure API Image 4
  3. Assuming you created the Logic App in the first installment of this series, select that Logic App from the “Select Logic App to import” blade that opens to the right.
    Azure API Image 5
  4. When that is done being created, you should notice an operation that was created automatically called manual-invoke. Select that operation and click the pencil edit button in the Frontend section.
    Azure API 6 Image
  5. Provide a meaningful display name if you’d like. Change the URL to a GET with a “/onpremcustomers” as a resource path.
  6. On the Query tab at the bottom, add the CustomerId (integer) query parameter.
    Azure API Image 7
  7. On the Responses tab, select the 200 OK response that should already be there. Add the details for an application/json representation using the sample data output from the Logic App created in the previous exercise. I also provided a definition of “customerresponse”.
    Azure API Image 8Aure API 11 Image
  8. On the Settings tab, add an API URL suffix, select a Product, and click save. You will want to make note of the Product you select. I am using “Unlimited” in my example. Later, when providing an API key for a custom connector, you’ll need to remember which product your API is using.Azure API 12 Image
  9. Now we’re ready to test this out. On the Test tab, enter a value for the Customer Id parameter and click Send. If all goes well you should get a 200 response back with, hopefully, some data.
    Azure API Image 13Azure API Image 14

Power Apps Custom Connector

  1. From the Azure API Management Overview blade, open the Developer portal (legacy). At the time of this writing, the new Developer portal was too buggy to use.
    Connector Image 1
  2. Click on the API we just created.
    Connector 2 Image
  3. Using the API definition button, download/copy and save the Open API 2 (JSON) file.
    Connector Image 3
  4. In Power Apps, go to Data  Custom Connectors  New Custom Connector Import an OpenAPI file. Import the downloaded JSON file from the previous step, provide a name for your connector and click Continue.
    Connector Image 5Connector Image 6
  5. Click through the General and Security tabs and make sure they match what you’re expecting (see screenshots).
    Connector Image 7Connector 9 Image
  6. On the Definition tab, if you see a parameter in the Body area, you can delete it. Then click Create connector at the top.
    Connector 11 Image
  7. On the Test tab, you’ll first need to create a new Connection. You will need the API key for the subscription you used in the APIM. In my case, I used “Unlimited”. (Click the ellipses to the right of the subscription and select Hide/Show Keys to see the key and copy it.)
    Connector 11.5 ImageConnector 12 Image
    Connector 15 Image
    Connector 13 Image
  8. Navigate back to the Custom Connector – Test tab and test out your Custom Connector.
    Connector 14 ImageConnector 16 Image

Conclusion

I hope this was helpful in demonstrating just how quickly you can better secure your Azure Logic Apps with Azure API Management, as well as how quickly you can spin up a custom connector that can then be distributed and used by other apps and Flows across your organization to connect to the Logic App through the new API.

This blog is part of a series of hands-on labs on leveraging the Power Platform and Microsoft Azure. To break this series up into more manageable chunks, in this installment, we’ll focus on setting up the On-Premises Gateway that will connect to our local SQL Server and the Azure Logic App that will use the new gateway to connect to our local SQL Server 2017 and return customer data using an HTTP request and response.

We’ll wrap it up by using Postman to test our Logic App. If you don’t have Postman installed, you can download it here.

Stay tuned for the next lab in this series, Azure API Management, and Custom Connectors.

Scenario

The company has an on-premises SQL Server database that contains customer data that needs to be available to various apps built on the Common Data Service.

High-Level Steps

  1. Install and configure an On-Premises Data Gateway to connect to the local SQL Server
  2. Create an Azure Logic App to connect to the SQL Server through the gateway

On-Premises Data Gateway

As you can imagine, there are already plenty of good write-ups on installing and configuring an On-Premises Data Gateway, including an official version from Microsoft. It is very straight forward, but I’m here to offer you a couple of pro-tips.

Pro Tip #1: Pay attention to the account you use for installing the gateway. This account needs to belong to the same Azure tenant as the account you’ll use for building your Azure Logic Apps that will use this gateway.

Pro Tip #2: Pay attention to the region that is automatically selected when installing the gateway. For this example, the gateway needs to be installed in the same region as your Azure Logic App.

Azure Logic App

Now I’m going to step you through building an Azure Logic App that can be executed using a simple HTTP Request. The Logic App will accept a single parameter (CustomerId), or no parameter at all. If no id is presented, the Logic App will return all customer rows. Otherwise, it will filter on the passed in CustomerId.

  1. Create a new Azure Logic App.Logic App
  2. When the deployment of the Logic App is complete, start with the common trigger “When HTTP request is received”.Logic Apps Designer
  3. Add the following JSON schema for the Request Body and click Save. Once you save the Logic App you should see the HTTP POST URL field populated automatically.
    {
    "properties": {
        "CustomerId": {
          "type": "integer"
        }
    },
     "type": "object"
    }

  4. Add an Initialize variable step named CustomerIdVariable, type string, and set the value to the passed in CustomerId parameter.
  5. Add a new Condition step to the Logic App (Controls -> Condition) and change the operator to “Or”. Add a second line row to the condition step and configure as follows.
      1. CustomerIdVariable is equal to null
      2. CustomerIdVariable is equal to 0
      3. Where null is a function, and CustomerIdVariable is set from the Dynamic content. Save the Logic App.
  6. In the “true” block, add a SQL Server, Get rows (V2) action. We will need to set up a new connection here so let’s do that. Since we were sure to set up the On-Premises Gateway in the same Azure Subscription, you should see the gateway automatically available in the Connection Gateway dropdown. We are using Windows Authentication for authenticating to the SQL Server.
  7. Once the connection is created, set the server name, database name, and table name on the SQL Server Get rows (V2) action. Save the Logic App.
  8. Repeat this process for the False side of the conditional, using the same connection that was created in a previous step. When creating this action, click the Add new parameter button and add a Filter Query parameter.
  9. Set the Filter Query parameter to powerappscontactid eq CustomerIdVariable where the CustomerIdVariable is filled in using the Dynamic content. Save the Logic App.
  10. After the Get rows (V2) action in the False side of the conditional, add another conditional. Use the following expression for the conditional.
    length(body(‘Get_rows_(V2)_2’)?[‘value’]) is equal to 0.
    Where the first part is an expression.
  11. For this new conditional we’ll leave the False side empty, but we want to add a Response Request action to the True side. Configure the Response to use Status Code 200, and [] as the Body. Save the Logic App.
  12. Finally, outside of the conditionals, we’ll add another Response Request Action with a Status Code of 200. For this response, we’ll use Dynamic content and send back the ‘values’ from both SQL Server Get rows (V2) actions.
  13. The Logic App should look like this.
  14. Time to save and test your Logic App. We’re going to use Postman to do that since we haven’t set up our API yet in Azure API Management. Open up Postman and start a new request. Use HTTP Post and the URL from the “HTTP Request Received” trigger on your Logic App. I also tweaked that URL to include our CustomerId query string parameter.

Once the URL is put into Postman, the other parameters should be auto-populated in your Postman environment. Here is a snippet of the URL that shows how I included the CustomerId, followed by a screenshot of the Logic App run (after completion), the Postman results, and the contents of the table displayed in SQL Server Management Studio.

…triggers/manual/paths/invoke?CustomerId=2&api-version=2016-10-01…

Conclusion

I hope this helped demonstrate how quickly you can start querying data in your on-premises data stores from a cloud resource like an Azure Logic App. Please keep in mind that you’ll most likely want to secure this Logic App since right now, anyone can send a request to it. We’ll cover that in more detail in another write-up.