This thing drove me crazy. Batsh*t insane, but it is all better now because I found the solution.

First off, thanks to Mark Brimble for volunteering his help on how he solved this issue as well as a very useful blog post on XML to JSON in Liquid templates.

The issue

You need to call a SOAP service that returns XML and you want to use Liquid templates instead of XSLT, because it is new and fresh and fun. You use the SOAP passthru as it is the most flexible way of calling a SOAP service.

The response body might look a little like this:

<Soap:Envelope xmlns:Soap="http://schemas.xmlsoap.org/soap/envelope/">
  <Soap:Body>
    <ns0:PriceList_Response xmlns:ns0="myNamespace">
      <PriceList>
        <companyId>1</companyId>
        <prices>
          <price>
            <productId>QWERTY123</productId>
            <unitPrice>1900.00</unitPrice>
            <currency>EUR</currency>
          </price>
        </prices>
      </PriceList>
    </ns0:PriceList_Response>
  </Soap:Body>
</Soap:Envelope>

You transform the payload into JSON and what to put it thru a Liquid template.

{
  "Soap:Envelope": {
    "@xmlns:Soap": "http://schemas.xmlsoap.org/soap/envelope/",
    "Soap:Body": {
      "ns0:PriceList_Response": {
        "@xmlns": "myNamespace",
        "PriceList": {
          "companyId": {
            "#text": "1"
          },
          "prices": {
            "price": {
              "productId": "1",
              "unitPrice": "19.00",
              "currency": "DKK"
            }
          }
        }
      }
    }
  }
}

How do you access the “price” property, in this payload? This will not work:

{{ content.Soap:Envelope.Soap:Body.ns0:PriceList_Response.prices.price.unitPrice }}

The colon is a special char
in Liquid templates and I found no way of \”escaping\” it.

The solution

The way I solved this was by using the [ and ] characters to encapsulate the name, as a string, of the property I wanted to access. That way you can access the property or object using whatever string you like. This means you can access the unitprice above like this:

{{ content.['Soap:Envelope'].['Soap:Body'].['ns0:PriceList_Response'].prices.price.unitPrice }}

I was so happy not to be insane anymore.

A tip

Accessing properties deep in a document can be quite tasking if you need to use a sting like that to get property values in your Liquid template. Use the ability to assign an object to a variable. This means that you can get a Liquid template that looks like this for assigning prices using the payload above:

{% assign price = content.['Soap:Envelope'].['Soap:Body'].['ns0:PriceList_Response'].prices.price.unitPrice %}
"prices": [
    {
        "ProdId": "{{price.productId}}",
        "ProdPrice": "{{price.unitPrice}}",
        "Curr": "{{price.currency}}"
    }
]

There is always this thing with Logic Apps and network-based security. You need the big ISE version of it to make that happen. The cost of that might be too high for your needs. Instead of giving up on Logic Apps and turning to Azure Functions you should consider the options available to you.

I like keeping things within the SaaS or PaaS layer and if I can steer away from IaaS, I will.

This is not a complete list of ways of securing your Logic Apps, but rather a list of what is easily available to you out of the box.

Logic apps are publicly available … but

Yes! This is true and is also a very useful feature when you need it. All you have to do is publish a Logic App with an HTTP trigger and you are good to go. You even get a string secured by a signature. If this signature is not formatted correctly the Logic App cannot be called.

However, if that signature gets compromised, you have problems. Giving out complex keys is one way of handling security that we refer to as “security thru obfuscation”, and that might not be enough.

Limit the ways the Logic App can be called

In the Logic app settings you can find “Workflow settings”:

If you click that you find a page where you can set Access Control and that is what you are looking for.

Look at the Allowed inbound IP addresses, you have a dropdown with three levels: Any IP, Only Other Logic Apps, Specific IP ranges. Lets take a look at what you can do with these.

Any IP

This is fairly obvious, and it simply allows calls from everywhere. As long at the caller uses the correct signature, an instance is started. This is the default and does not add any security.

Only Other Logic Apps

This is interesting as it only allows this Logic App to be started by another Logic App. This means that it is a sub-process of that Logic App. The very useful, and perhaps not that obvious, feature of this is that it only allows calls from the same Azure Subscription. This means that even if someone has the full signature and calls the Logic App from their own Logic App, the call is not authenticated. So you can view the boundary of the Azure Subscription as a network boundary.

A test

I colleague and me tested this. I created a simple Logic App that called a Logic App he had outside the same subscription, but we made sure to use the same Azure Region. He then updated the settings to only allow other Logic Apps. This is the result:

We then updated his Logic App to allow from Any IP, and this was the result:

As you can see, I got an error. This error is not a security error though, but rather an error due to me not supplying the correct data in the call. Note that the address clearly points to the public address of the Logic App.

Specific IP-ranges

This feature is used in cases where you know, who is calling your Logic App and you are sure that IP will never change. I personally never use this other when calling Logic Apps from VMs that I know will use a particular IP-address. If you need to handle calls and user based on IPs and such, I suggest you use API management in front of the Logic App. The new consumption-based tier fits very well with this scenario.

Calling Logic Apps from Azure API management is easy and a great way to bring some order to your APIs.

However, when you call a Logic App, it returns a lot of headers that contain information that you want to hide from your API consumers. In order to do that, simply add the following to your policy for outbound:

<outbound>
        <base />
        <set-header name="x-ms-tracking-id" exists-action="delete" />
        <set-header name="x-ms-request-id" exists-action="delete" />
        <set-header name="x-ms-workflow-run-id" exists-action="delete" />
        <set-header name="x-ms-correlation-id" exists-action="delete" />
        <set-header name="x-ms-client-tracking-id" exists-action="delete" />
        <set-header name="x-ms-trigger-history-name" exists-action="delete" />
        <set-header name="x-ms-execution-location" exists-action="delete" />
        <set-header name="x-ms-workflow-id" exists-action="delete" />
        <set-header name="x-ms-workflow-version" exists-action="delete" />
        <set-header name="x-ms-workflow-name" exists-action="delete" />
        <set-header name="x-ms-workflow-system-id" exists-action="delete" />
        <set-header name="x-ms-ratelimit-burst-remaining-workflow-writes" exists-action="delete" />
        <set-header name="x-ms-ratelimit-remaining-workflow-download-contentsize" exists-action="delete" />
        <set-header name="x-ms-ratelimit-time-remaining-directapirequests" exists-action="delete" />
        <set-header name="x-ms-ratelimit-remaining-workflow-upload-contentsize" exists-action="delete" />
</outbound>

If you want to, you can add it at the API level to override the headers for all operations, or you can set it at an even higher level. Just add it under All APIs just to be sure that you do not send Logic App headers back to the API consumer.

A quick search of the internet gives you inconsequential results for how to get the querystring in a standard request-response Logic App. Can it be done? Yes! How? Here is how:

The queries object

There is an object called “queries”. This object contains all of the querystring that is sent to the logic app. This is awesome because it means you can use the Logic App without constantly using POST and a body. Sometimes you just want to send a couple of values, like for instance an employee-identifier.

The object is under “triggeroutputs”, so accessing it is easy:

@triggerOutputs()[‘queries’][‘paramname’]

You can either access the value directly or store it in a variable for later use.

If you need to handle null-values, you add a ?-sign where you usually do but omitting that gives you a good way to handle missing values. If your Logic App needs a value to function, you have to make sure it is submitted.

Handling missing values

Here is one way of doing it

This will send a HTTP 400 back if the variable cannot be assigned. That way we know, further down that the EmployeeID is set and the backend system can be called correctly.

The “Create variable shape” has this code to get the value from the querystring:

There is an elephant in the room, or at least a logotype with an elephant. The product is called PostgreSQL and is a capable, open source, relational database engine. More info can be found here.

To tame this beast, you can find several ways of hosting it, AWS and Azure both have offerings and you can also choose different packages on the Azure Marketplace for hosting it on its own server. Personally, I always go for the PaaS version of anything.

In this scenario a colleague of mine used the PaaS version, but hosted on AWS, and we needed to connect PowerBI to it, which means using the on premise data gateway, which is a bit of a personal favorite of mine. The setup might seem to have a bit too many moving parts, but the requirements where clear: make the data in this database available to PowerBI.

The setup

From left to right, PowerBi connects to the on premise data gateway (OPGW) server using HTTPs. The gateway server is hosted in Azure and contains the data gateway. The data gateway connects to the PostgreSQL database service on Amazon. The OPGW server is allowed access by configuring the local service firewall and by sending the correct credentials.

The OPGW needs to be updated with binaries to enable connectivity to any PostgreSQL database. It is supported out of the box, it just needs the right binaries to work. Luckily, they are available on GitHub.

Since we are using the OPGW to expose the data to PowerBI, the same OPGW can be used from Logic Apps to access the same database server.

Note that the OPGW server could be placed in Amazon as well, we just didn’t want to.

The steps

Make sure to read ALL these steps before starting. There are some things which might take time and needs to be planned.

Besides the obvious ones of getting a PostgreSQL database, and an Azure VM the steps where as follows:

1. Install the OPGW on the server.
2. Download the binaries for enabling connectivity with PostgreSQL from GitHub. At the time of writing the current version is 4.0.3
3. Install the binaries and restart the OPGW service.
4. Register the OPGW in the Azure subscription.
5. Get the settings for the PostgreSQL server from Amazon. The most important setting is called EndPoint and should look like: yourservername.randomkey.datacenterlocation.rds.amazonaws.com
6. The OPGW does not allow for non encrypted connectivity, like other tools like the popular PostgreSQL administration tool pgAdmin. Since you must encrypt you have to harden your Amazon database using this guide.
7. You should now be able to connect to your database straight from PowerBI. Make sure you use the right credentials, and data gateway.

Debugging

If you cannot connect you can always press that little button in PowerBI called “Test all connections”. That usually gives you good information. Here are some possible mistakes:

1. You have submitted the wrong credentials.
2. The firewall is not configured in Amazon.
3. The OPGW server is not running.

The error message from PowerBi is usually very helpful and to get more information, try to install pgAdmin on your OPGW server to make sure it can connect to the Amazon database.

Making your design

To make something publishable in PowerBI you have to create it PowerBI Desktop. Here, you can connect to the PostgreSQL database directly without any OPGW servers. These are the steps:

1. Download and install the binaries from GitHub (steps 2 and 3 above).
2. Create a systemwide DSN for your Amazon database, using the endpoint and credentials.
3. Create a new datasource in PowerBI and point to the new DSN.

Happy designing.