Today, I found myself in need of an automated SFTP connection that would reach out to one of our partners, download a file, and then dump it in to a Data Lake for further processing. This meant that I would need to store a private in Azure Key Vault for use in a Logic App. While this was mainly a straightforward process, there was a small hiccup that we encountered and wanted to pass along.

First, we went ahead and generated a public/private key pair using:

ssh-keygen -t rsa -b 4096

where rsa is the algorithm and 4096 is the length of the key in bits. We avoided the ec25519 and ecdsa algorithms as our partner does not support elliptic-curve cryptography. As this command was run on a Mac laptop which already has it’s own ~/.ssh/id_rsa[.pub] key pair, we chose a new filename and location /tmp/sftp to temporarily store this new pair.

The problem arose when we tried to insert the private key data into Key Vault as a secret: the Azure portal does not support multi-line secret entry, resulting in a non-standard and ultimately broken key entry.

The solution was to use the Azure CLI to upload the contents of the private key by doing:

az keyvault secret set --vault-name sftp-keyvault -n private-key -f '/tmp/sftp'

This uploaded the file correctly to the secret titled private-key, which means that we can now add a Key Vault action in our Logic App to pull the secret, without having to leave the key in plain view, and then use it as the data source for the private key field in SFTP - Copy File action.

As an aside, we also created a new secret called public-key and uploaded a copy of sftp.pub just so that 6 months from now if we need to recall a copy of it to send to another partner, it’s there for us to grab.

Michael Howard of Microsoft put out a great post about how easy it is to inadvertently create massive security holes in the form of SQL Injection Vulnerabilities in your HTTP-accessible Azure Logic App by not using the ‘Execute a SQL Query’ action correctly. He also gives some simple examples of how to protect yourself in the process.

To summarize: if you are not using prepared statements or stored procedures, it is extremely trivial for an attacker to construct a query that does anything from truncate or drop tables, to changing data within the database, to getting full remote command execution using a command like SQL Server’s xp_cmdshell.

Please be extremely careful when you’re building your Logic Apps – they may be simple to build but that also means it’s just as simple to make a glaring security mistake that could cost your business time and money.

Step 1 – Find a cat facts API

https://catfact.ninja/

Well that was easy.

Step 2 – Build a serverless, Azure Logic App using Terraform that will connect to the API and spam your co-workers with a new fact every 5 minutes

https://github.com/nexxai/cat-facts/

Ok that part was easy too, but come on, it’s gotta be at least a little difficu–

Step 3 – Create an Office 365 connection that your Logic App can use

Open the Azure Logic Apps blade

You have 60 seconds to manually add a step that connects your Office 365 account to this app. ‘Get Calendars’ requires the least configuration.

Step 4 – Wait for your co-workers’ email clients to play their New Email alert sound

Start laughing, and keep laughing every 5 minutes from now until forever, asserting your feline dominance over your team.

“But that was only 4 steps, where’s number fi

Step 5 – Have Senior PM of Microsoft Azure Functions see your stupid app and tweet about it

Sure, no prob–wait, what?

Today we faced a problem where we needed to interface with a vendor’s SOAP API (*screams in old-person-ese*) which they protect using an internal PKI. They had provided us a certificate to use, but we found that actually using it in the Logic App we built was going to be a little more complicated than we originally expected.

Here’s what we did.

First, the vendor provided us the certificate in .pem format, while Logic Apps expect to use .pfx format in the HTTP actions, so we needed to convert it. Luckily, openssl makes this relatively easy:

openssl pkcs12 -export -out certificate.pfx -in certificate.pem -inkey key.pem -passin pass:examplepassword -passout pass:examplepassword

Next, we need to take the .pfx-formatted certificate and base64 encode it:

cat certificate.pfx | base64

After removing any line breaks to make the result one continuous line of text, we now have a certificate we can pass to the vendor, but we don’t want to store that in the Logic App. It’s not secure and we want secure. What do we do now?

Within Azure, we create a Key Vault, and within that Key Vault we create a secret within which we place the base64-encoded, pfx-converted certificate.

Now we have everything we need to put this all together.

In the Logic App we create an action that reaches out to the Key Vault we created, requests the secret and sets the result as a variable called PFXKey. We then create an HTTP action that uses “Client Certificate” as the authentication method, and the value of the PFXKey variable as the variable. We set the password to the password of the certificate (examplepassword in the example above) and we can now use a POST request type to send the data to the vendor, using Client Certificate authentication, all while keeping the certificate contents and its password secure.