Hello Info Technicians. In this blog we will see how to connect Azure Logic Apps and SQL Server in a Azure VM.

It is very important connectivity for many of us. Initially I too struggled and found a way to do it. Let’s see that step by step.

Resources needed:

• Azure Logic Apps (Standard)
• Azure VM (SQL Server)
• Managed Identity

Diagram:

Azure Logic Apps and SQL Server on an Azure VM

Procedure:

We will create a SQL Server Virtual Machine and a Logic App Standard, connecting them via a Virtual Network (VNET). For access, we will use a User-Managed Identity, which will be added to SQL Server as an Entra ID authentication method.

Note: Microsoft Entra authentication is not available for SQL virtual machine offer SQL2019 and sku Developer. Microsoft Entra authentication is available starting SQL Server 2022.

Create SQL Server VM:

Go to Azure Portal and search for SQL virtual machines

Create new SQL virtual machine. Here, I’m using Free SQL Server License: SQL Server 2022 Developer on Windows Server 2022

Note: The same VM can be available in two services: Virtual Machine and SQL Virtual Machine. The Virtual Machine represents the actual configurations of the VM, while the SQL Virtual Machine is specific to SQL Server configurations.

Subnet Creation:

1. Go to the VM’s Virtual Network and create a new subnet.
2. Provide a name, set the size to 32 addresses, and click Add.

Logic Apps Creation:

1. Search for Logic Apps and create a Workflow Plan Logic App (if it is App Service Environment then VNET integration should be added in ASE).
2. Go to Networking and click Virtual Network Integration.
3. Click Not Configured, then Add VNET Configuration.
4. Select Subnet and click Connect

Managed Identity:

Managed identities in Azure are a feature of Microsoft Entra ID that simplifies the management of credentials for applications running on Azure services. There are two types of managed identities:

• System-assigned: Tied to a specific Azure resource (e.g., a virtual machine). When the resource is deleted, the identity is also deleted.
• User-assigned: A standalone Azure resource that can be assigned to multiple resources. It exists independently of the resources to which it is assigned.

For this we are going to use User Managed Identity.

Managed Identity Creation:

1. Go to the Azure Portal and search for “Managed Identities“
2. Click Create. Then, select the Subscription, Resource group, Region, and provide a Name.
3. Click Review + Create.

Adding Managed Identity to Entra ID Directory Reader:

1. Go to Entra ID → Roles and Administrators → All Roles → Directory Reader.
2. Search for the User Managed Identity and click Add.

Adding Managed Identity to the Resource:

1. Go to Virtual Machine and Logic Apps.
2. Click Identity → User assigned and Add the created user identity.

Enabling Entra ID Authentication to SQL Virtual Machine:

1. Go to SQL virtual Machine → Security Configuration → Enable Microsoft Entra Authentication.
2. Add User Managed Identity → Apply.
3. Restart the machine once done.

Adding Entra ID Authentication (User Identity):

1. Connect to SQL Server VM via RDP (Download RDP file and connect using user name and password)

2. Open SSMS (pre-installed) and connect using Windows Authentication.

3. Security → Logins → right click and New Login → Add the User Identity to Login name and select Microsoft Entra ID authentication → Go to Server Roles tab and check sysadmin → click OK.

I have a sample user database in this database server.

Connecting from Logic Apps (Standard):

For this demo, the Logic App (Standard) Workflow Service Plan is used.

1. Select the Execute Query connector and provide the required values in the Stateful Workflow.
   Note: Go to Managed User Identity and copy the Identity Client ID.

• Connection Name – Give the connection name of your choice
• Authentication Type – Choose Managed Identity
• Managed Identity Type – Choose User assigned
• Identity Client Id – Go to user managed identity and copy the client identity
• Server Name – IP Address of the machine (SQL Server VM)
• Database Name- The database you want to connect

Run the Workflow (Logic App) and check the Run History

Executing the Stored Procedure:

1. Select the Execute Stored Procedure action. It will connect to the existing connection that was used earlier.
2. You’ll be able to see the available stored procedures (such a nice feature!).
3. Let’s save and run the workflow.

Run History:

Limitations:

1. Need to check the feasibility or explore alternative ways to connect to SQL Server in a VM, as Entra ID authentication supports only SQL Server 2022.
2. Only one User Managed Identity is supported. Need to explore grouping the Managed Identity or to add multiple Managed Identities.

Thanks for checking out this blog! Feel free to leave any suggestions or questions in the comments section below.