Category: Uncategorized

Securing calls to Logic Apps using Logic Apps

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.

Headers and calling Logic Apps from APIm

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.

Access Querystring in Logic App

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:

Connecting to a PostgreSQL database from Azure

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.

How to only poll data on specific weekdays using the WCF-SQL adapter

There are a lot of solutions to this particular question. The need is that we only poll data from a database on Sundays.

This might be solved using a stored procedure that only returns data on Sundays. It might also be solved by using the famous schedule task adapter to schedule the poll for Sundays. You can also do some cool coding thing using a custom pipeline component that rejects data on all other days but Sundays. Your scenario might be very well suited for one of these solutions, the scenario presented by my colleague Henrik Wallenberg did not fit any of those.

The scenario

A database is continuously updated thru out the week but we need the export data from a specific table every Sunday at 6pm. We cannot use the schedule task adapter nor stored procedures. We decided to try to trick BizTalk using the PolledDataAvailableStatement in the WCF-SQL adapter on a receive port. Turns out it works! Here is how.

Please note that this does not work if you cannot use ambient transactions.

According to this post, you must set Use Ambient Transaction = true if you need to us a polledDataAvailableStatement. This seems really odd to me but after receiving feedback about this article I know that it is true.

The solution

  1. Create the receive location and polling statement.
  2. Find the setting PolledDataAvailableStatement
  3. Set it to: SELECT CASE WHEN DATEPART(DW, GETDATE()) = 1 THEN 1 ELSE 0 END
  4. Set the polling interval to 3600 (once an hour).
  5. Apply your settings.
  6. Set the Service Window to only enable the receive location between 6pm and 6:30 pm. 
  7. Now the receive location will only poll once a day and only execute the polling statement on Sundays.

More information

How does this work? It is very simple really. The property PolledDataAvailableStatement (more info here) needs to return a resultset (aka a SELECT). The top leftmost, first if you will, cell of this resultset must be a number. If a positive number is returned, then the pollingstatement will be executed, otherwise not.

The SQL statement uses a SQL built-in function called DATEPART with a parameter value of “dw”, which returns “Day Of Week”. More information here.
Day 1 is by default in SQL Server a Sunday, because Americans treat days and dates in a very awkward way. There might be some tweaking to your statement in order to make Sunday the 7th day of the week. So the statement SELECT CASE WHEN DATEPART(DW, GETDATE()) = 1 THEN '1' ELSE '0' END returns a 1 if it is day 1 (Sunday). This means that the pollingstatement will only be executed of Sundays.

We then set the pollinginterval to only execute once an hour. This, together with the service window, will make sure the statement only executes once a day (at 6pm) as the receive location is not enabled the next hour (7pm). You could update the SQL statement to take the hour of the day into consideration as well but I think it is better to not even execute the statement.

The downside

This is not a very reliable solution though. What if the database was unavailable that one time during the week when data is transported? Then you have to either wait for next week or manually update the PolledDataAvailableStatement to return a 1, make sure the data is transported and then reset the PolledDataAvailableStatement again.

In conclusion

It is a very particular scenario in which this solution is viable and even then it needs to be checked every week. Perhaps you should consider another solution. Thanks to Henrik for making my idea a reality and testing it out. If you want to test it out for yourself, some resources to help you can be found here: InstallApp Script