091317_1042_TestingtheA1.png

Securing passwords in Logic Apps

The history

In the beginning, storing passwords, usernames and so on in a Logic App was not very secure. You had to store the value in clear text in the Logic App “code behind”, or do some magic using a file on a blob storage and a function. That was before the wonderful service called KeyVault.

If you have experience of BizTalk in the past, then you can view a KeyVault much the same way as the SSODB; an encrypted place to store keys and values. You are not limited to just storing user credentials, you can store other things like endpoint addresses, settings, certificates and even access it using REST, but this post focuses on using the KeyVault as a storage for user credentials. More information about the KeyVault service can be found here, and here is how to get started.

NOTE: You do not need any KeyVault preconfigured. The instructions below will setup a basic one.

The scenario

You are calling an API that uses Basic Auth (aka username and password). The authentication is therefore per call and must be supplied every time the service is called. You do not want to store the username and password in clear text in the Logic App.

The Logic App is only a wrapper for the call to the service (for demo purposes).

You are using Visual Studio to develop and deploy your Logic App.

NOTE: It is possible to achieve the same result without using Visual Studio but this post does not cover that.

The Logic App without KeyVault


As you can see the password and username is clearly visible to anyone accessing the Logic App, so any Logic App contributor can access it. The JSON for it can be downloaded here. Just paste it into a new project, replacing the old text in the LogicApp.json-file.

This is how you do it

Logic Apps tooling in Visual Studio comes prepared to use KeyVault, the only tricky part is to add parameters that will make the tooling use KeyVault. We are going to make use of this together with the Logic Apps ARM template in Visual Studio. There is a lot of text here but I am sure that if you do this once you will fully understand it. Take your time to get it right.

Add parameters

Open the Logic App as JSON and scroll to the top. The Logic App always starts with a parameter for the Azure Resource Manager: the name of the logic app. Here we will add two new parameters: ExternalSupplierUsr and ExternalSupplierPwd. Add the following JSON before the first parameter.

“ExternalSupplierUsr”: { “type”: “securestring” },

“ExternalSupplierPwd”: { “type”: “securestring” },

Note the type: securestring. This will tell the tooling that we would like to use the KeyVault.

The updated JSON file can be downloaded here.

Configure the parametersfile and create the KeyVault

Next we need to make room for the keys. Save the Logic App, right-click the project in the solution explorer and choose Deploy and then the name of your project. The usual dialog appears. Fill it out and the click the Edit Parameters button. The new dialog should look something like this

See those little keys to the right. These are shown because we used the securestring type. Click the top one.

If you already have a KeyVault, you can use that. Let’s create a new one.

Click the link saying Create KeyVault using PowerShell. This will point you to this page on github.

Open the PowerShell ISE and copy + paste the powershell code into it. Update the code to your needs. I will create a KeyVault called SuperSecretVault in West Europe in its own resource group. I highly recommend this, use a separate group for your KeyVaults.

My finished script will look like this:

#Requires -Module AzureRM.Profile
#Requires -Module AzureRM.KeyVault

#Login and Select the default subscription if needed
Login-AzureRmAccount
Select-AzureRmSubscription -SubscriptionName ‘your subscription name goes here’

#Change the values below before running the script
$VaultName = ‘SuperSecretVault’

#Globally Unique Name of the KeyVault
$VaultLocation = ‘West Europe’

#Location of the KeyVault
$ResourceGroupName = ‘KeyVaultGroup’

#Name of the resource group for the vault
$ResourceGroupLocation = ‘West Europe’

#Location of the resource group if it needs to be created
New-AzureRmResourceGroup -Name $ResourceGroupName -Location $ResourceGroupLocation -Force
New-AzureRmKeyVault -VaultName $VaultName -ResourceGroupName $ResourceGroupName -Location $VaultLocation -EnabledForTemplateDeployment

Execute it and wait.

Use the KeyVault

Now go back to Visual Studio. You have to close down all dialogs except the first one. Click the little key icon again, next to the parameter called ExternalSupplierUsr

Select your subscription, select your vault and choose <Create New>

Give it a name, I will use SecretExternalSupplierUsr, and then set the value “SuperSecretUserName” for the username. Click Ok and repeat the process for the ExternalSupplierPwd (all the way back and press the little key again). Name your Logic App SecurePasswordsInLogicApps and it should look something like this:

Click Save to save the configuration into the parameters.json file. We are not going to deploy it yet but you can look at it to see what was updated.

Use the parameters in the Logic App

Here is the tricky part. You must add parameters in the JSON behind the Logic App. This is pretty hardcore and make sure to know where to type what.

Start by opening the JSON file for the Logic App, not in the designer but the whole file. Scroll down to the bottom. Here you will find the first parameters-clause. You should enter the value of the parameters at the top. At deploy time, the resource manager will take the value of the parameters given in the KeyVault and just paste them here. Since this part is never shown in the Logic App code behind, this is ok. Think of this as values being compiled into “the DLL of your Logic App”.

Make sure you use good names for these parameters. They do not have to be the same as those at the top but the name must be the same from now on. I updated my JSON-file to look like this.

“parameters”: {
“SupplierAPIUsr”: {
“value”: “[parameters(‘ExternalSupplierUsr’)]”
},
“SupplierAPIPwd”: {
“value”: “[parameters(‘ExternalSupplierPwd’)]”
}
}

 

My updated JSON file can be downloaded here.

Setup the Logic App with parameters

If you would simply paste in [parameters(‘ExternalSupplierUsr’)] in your Logic App, a deployment will replace the parameter with a value and therefor making it visible in the Logic App code behind. We have to send the value into the Logic App as a secure string.

Scroll up to the next parameters-clause. Mine are at row 87. Here you declare two new parameters, with the same name as the parameters you just declared at the bottom of the file. After update, my file looks like this:

“parameters”: {
“SupplierAPIUsr”: {
“type”: “SecureString”
},
“SupplierAPIPwd”: {
“type”: “SecureString”
}
},

 

My updated JSON file can be downloaded here.

We have now set up parameters to retrieve the value sent in using the two parameters at the bottom.

Use the parameters

The last step is to use the parameters in the Logic App. This is very simple since there is an array in the Logic App called Parameters.

Scroll up and find the username and passwords for the external API. Mine are at rows 69 and 70. Update the values to use parameters.

I updated the file to look like this:

“authentication”: {
“type”: “Basic”,
“username”: “@parameters(‘SupplierAPIUsr’)”,
“password”: “@parameters(‘SupplierAPIPwd’)”
}

The final file can be downloaded from here.

Deploy and test

Deploy your Logic App just like you usually do and then test it using Postman. We get an error back because the service called does not exist.

Look at the results

If you look at the run, you will see that this has a downside. Not all values sent as secure strings are sanitized.

But at least the password is not in clear text.

Now open the code behind of the Logic App and you can see that the values of the parameters are never shown! This is awesome!

The good thing

This gives you and your team a uniform, and secure, way to keep your passwords in check. Use different KeyVaults for different environments (one for test and one for prod) and you will be good to go.

The bad thing

Since the value of the KeyVault is only accessed when the Logic App is deployed, you must redeploy the Logic App if you need to update a value in the KeyVault. For instance, say we need to update the password in the Logic App used here, then you first need to update the KeyVault (use the portal) and then you need to redeploy the Logic App. That way the new value is picked up by the Azure Resource Manager and the Logic App is updated.

091317_1042_TestingtheA1.png

InvalidTemplateDeployment in Azure RM

Using scripting when deploying Logic Apps, and the surrounding bits is very useful. If you have set something up it is very easy to just script it and save it locally or under your templates.

I stored mine locally and got the error above when deploying. My thoughts where “An error in the template? The template that was generated for me? This is not good”.

I tried opening the template file and found some minor upper and lower case errors but that did not do it.

The solution was to get more information! You need to access your subscription’s activity logs. You can find it in the left side menu or by searching for “Activity Log” in the expanded menu.

The starting query should return your failed validation of the template.

Click on the row of the failed validation (it is not a link strangely) and choose to show the info as JSON.

Scroll down to the end of the message. Under the tag “properties/statusMessage” you will find the full story. In my case (I am ashamed to say) the name of the storageaccount was invalid.

091317_1042_TestingtheA1.png

SQL Server Edition Upgrade might fail

What happened?

A while back I tasked myself with automating an SQL server edition upgrade using PowerShell.

I ran into some problems. I made sure the upgrade was as /s (silent) as possible and so I only got a very rudimentary progress bar. The upgrade would seem to take a long time and after two hours of waiting I decided that the upgrade had “hung”. I repeated the upgrade but kept a look at the log file.

What was wrong?

Looking into the log file I found that the thing that seemed to hang was this row:

Waiting for nt event ‘Global\sqlserverRecComplete’ to be created

How to solve it?

Searching for it online I found several reasons for this and one (unsupported) option stood out, simply skip the rules-check.

If the upgrade fails in this way, simply add the following to your PowerShell string:

/SkipRules=Engine_SqlEngineHealthCheck

The implications

Some images on Azure has SQL Server evaluation edition installed by default. You usually want to upgrade these to Developer Edition, using the built in Edition Upgrade functionality.

If you run into the “hang” issues you have to upgrade SQL server without checking the rule SQLEngineHealthCheck.