Azure Logic Apps are a cloud-based service where you can create low code workflows to automate your business processes or to integrate different applications or services. The article How To Embed Your Azure Logic Apps in a Metadata-driven Data Platform gives a detailed introduction to Logic Apps and also explains how you can take it a step further by integrating them in a metadata-driven framework. At the end of the article, the following Logic App workflow was created:

Figure 1: The starting point for the Logic App in this article

A stored procedure is executed on an Azure SQL Database. The connection to this database was configured using SQL Server Authentication. The goal of this article is to show you how you can connect using managed identities instead, which was left as an exercise to the reader in the previous article.

I recommend you to go through this article first if you don’t have a solid understanding of Logic Apps, or if you want to follow along as an exercise. It’s not necessarily a prerequisite to understand the concepts of this article and if you’re just interested in learning how managed identities work for Logic Apps, then keep on reading.

What are Managed Identities and why should you use them?

When developing applications or services that use multiple Azure resources, it’s not uncommon that those resources have to communicate with each other. Often the developer has to manage the secrets (such as usernames and passwords), credentials, certificates and keys to establish secure connections between those resources. For example, when you want to connect from an Azure Data Factory pipeline to an Azure SQL Database, you need to create a connection and specify a username and password. You can store all those credentials securely in Azure Key Vault, but it’s always extra steps to implement and retrieve those credentials. And what if a password changes? Or if an account key is rotated? Then you have to remember to update those in the Key Vault as well.

With Managed Identities, all this is avoided. A Manage I dentity is an object in Azure Active Directory (Azure AD), which can be used by the Azure resources to establish the connections (as long as Azure AD authentication is supported). All the credentials are managed by Azure (hence the name), so you don’t need to keep track of passwords or anything. Think of a managed identity as an identity impersonating an Azure service when it connects to another service.

In the example of ADF and Azure SQL Database, you would create a Managed Identity associated to the Data Factory instance, add this as a user to the database and give that user the appropriate permissions. In the ADF pipeline, you just have to choose to connect with a Managed Identity and everything else is taken care of for you.

Figure 2: Using a Managed Identity in Azure Data Factory

There are actually two types of Managed Identities:

• System Assigned Managed Identities. This is the one used in Figure 2. For each instance of an Azure resource, you can assign one system managed identity (MI). This MI will bear the name of the resource itself, as you can see in Figure 2. Since the name is unique, you can have only one system-assigned MI.
• User Assigned Managed Identities. Here you create the MI yourself and it’s not tied to one single resource. A user assigned MI can be assigned to multiple Azure resources, allowing for easy reuse.

Depending on your use case, you choose the type that suits your needs. Personally, in most cases I use the system assigned managed identities as they’re specifically tied to the lifecycle of the resource. If the resource is deleted, so is the MI. It also makes it easier to track which resources have access to another resources (for example as users in a database), since the name of the resource is the name of the identity.

In the case of our Logic App, we could use a system assigned MI if there was only one Logic App. But what if we have the same Logic App in multiple environments (for example a development, test and production environment)? If you want to use system assigned MI, you would need to change the name of the Logic App in each environment. Or what if you have 50 Logic Apps that need access to your database? Are you going to create 50 different users in the database? A more elegant solution would be to have one single user assigned managed identity to connect to the database, and let all Logic Apps use this MI. For these reasons, we’re going to use user assigned managed identities in this article.

If you’re not convinced yet to use managed identities, check out the blog post Why Managed Identities are Awesome.

Using a Managed Identity in the Logic App

In this section I will go through the process of setting up a managed identify for a Logic App.

Create a New Managed Identity

The first step is to create a user assigned MI. In the Azure Portal, I search for “managed identities” as shown in Figure 3:

Figure 3: Search for Managed in the Azure Portal

I click on Managed Identities, which leads me to an overview of all existing managed identities in my environment.

Figure 4: Managed Identities Overview

I click on Create in the top left corner, which brings me to a new screen where I can specify the necessary info.

Figure 5: Configuration of the User Assigned MI

In the configuration, I specify the same subscription, location and resource group as my Logic App and then I give the MI a unique name. When I click on Create at the bottom, Azure will run a validation check. If it passes, I can click on Create again to start the provisioning of my new MI.

Figure 6: Create New User Assigned Managed Identity

In the Logic App page itself, I go to the Identity section. There I have the option to assign a system assigned MI or a user assigned MI.

Figure 7: Identity Section of a Logic App

I click on User assigned to go to configure the corresponding tab. I click on Add to assign my previously created user assigned MI.

Figure 8: Assigning the MI to the Logic App

In the pop-up, I can search for the MI I created earlier, as shown in Figure 8. I select the MI and click on Add at the bottom.

Create a Connection Using the Managed Identity

Now that the Logic App has an identity, I can configure it to use that identity to connect to my Azure SQL Database. In Figure 1 you can see there’s a connection defined with the name “sql”. This is an API Connection resource which can be found in the resource group of the Logic App.

Figure 9: The SQL API Connection

In the API connection itself, I tried to edit the connection information directly. In the dropdown for Authentication Type, there’s an option to use the Logic Apps Managed Identity.

Figure 10: Editing an API Connection

However, when I try to save my changes, an error is thrown with no further explanation.

Figure 11: Failure Editing API Connection

It seems I must create a new connection for my Logic App. In the Logic App editor, I go to the “Execute stored procedure” activity and choose to change the connection.

Figure 12: Change Connection for the SQL Action

In the Connection menu, I choose the option to create a new connection.

Figure 13: Create new connection

In the next step, I simply need to specify a name for my new connection and set the authentication type to Logic Apps Managed Identity.

Figure 14: Configure the connection to use the managed identity

When the connection is changed, it’s possible the server name is set to “default” and the database name to “use connection settings”. The name “Test” is listed between brackets as it was the name of the database in the previously used connection. However, the connection itself doesn’t store any actual connection information. It only knows it has to use the Logic App managed identity.

Figure 15: Default server and database name

You can manually override these defaults, but a better option would be to pass the server and database name as parameters. This way, if we deploy our Logic App to another environment, we don’t have to change both names manually but rather supply different parameter values.

In the HTTP trigger, I add two extra fields to the JSON request:

Figure 16: Extra fields in the JSON request body

The total request body now becomes:

With those two new fields added to the HTTP trigger, I can now go back to the “Execute stored procedure” action and configure the server and database name fields to use the values from the request body. I do this by selecting the relevant fields from the dynamic content list.

Figure 17: Using dynamic content to parameterize an action

Configuring Permissions in the Database

The Logic App is now configured to use the managed identity to connect to the Azure SQL Database, but without the appropriate permissions the stored procedure will not be executed.

I log into my database using an account that has the necessary permissions to create a new user and assign permissions to that user. First I create a user with the name of the managed identity:

Next, I assign a bunch of permissions that will allow the newly created user to connect to the database, read and write data, truncate tables (which requires db_ddladmin permissions) and execute stored procedures.

Testing the Logic App

The Logic App can now be tested. In the editor, I select “Run with payload”, which allows me to specify a JSON request body for the HTTP trigger.

Figure 18: Run the Logic App with a specified payload

The Logic App runs without error, and it now connects to the Azure SQL Database using the user assigned managed identity. That is one password less that needs to be stored and managed.

Conclusion

By using managed identities, we can allow a Logic App to connect to another Azure resource without specifying any password, credential, or account key. This results in a better security practice and less management overhead. After all, the less passwords we need to remember, the better.

When your environment has many Logic Apps, it’s advisable to use user assigned managed identities. In this case, you only need to create one managed identity and reuse it between your Logic Apps.