Connect to an SQL database from workflows in Azure Logic Apps

Applies to: Azure Logic Apps (Consumption + Standard)

This how-to guide shows how to access your SQL database from a workflow in Azure Logic Apps with the SQL Server connector. You can then create automated workflows that run when triggered by events in your SQL database or in other systems and run actions to manage your SQL data and resources.

For example, your workflow can run actions that get, insert, and delete data or that can run SQL queries and stored procedures. Your workflow can check for new records in a non-SQL database, do some processing work, use the results to create new records in your SQL database, and send email alerts about the new records.

If you're new to Azure Logic Apps, review the following get started documentation:

• What is Azure Logic Apps

• Create an example Consumption logic app workflow in multitenant Azure Logic Apps

• Create an example Standard logic app workflow in single-tenant Azure Logic Apps

Supported SQL editions

The SQL Server connector supports the following SQL editions:

• SQL Server
• Azure SQL Database
• Azure SQL Managed Instance

Connector technical reference

The SQL Server connector has different versions, based on logic app type and host environment.

Logic app	Environment	Connector version
Consumption	Multitenant Azure Logic Apps	Managed connector, which appears in the connector gallery under Runtime > Shared. For more information, review the following documentation: - SQL Server managed connector reference - Managed connectors in Azure Logic Apps
Standard	Single-tenant Azure Logic Apps and App Service Environment v3 (Windows plans only)	Managed connector, which appears in the connector gallery under Runtime > Shared, and built-in connector, which appears in the connector gallery under Runtime > In-App and is service provider-based. The built-in connector differs in the following ways: - The built-in connector can directly connect to an SQL database and access Azure virtual networks by using a connection string without an on-premises data gateway. For more information, review the following documentation: - SQL Server managed connector reference - SQL Server built-in connector reference - Built-in connectors in Azure Logic Apps

Limitations

For more information, review the SQL Server managed connector reference or the SQL Server built-in connector reference.

Prerequisites

• An Azure account and subscription. If you don't have a subscription, sign up for a trial Azure subscription.

• SQL Server database, Azure SQL Database, or SQL Managed Instance.

  The SQL Server connector requires that your tables contain data so that the connector operations can return results when called. For example, if you use Azure SQL Database, you can use the included sample databases to try the SQL Server connector operations.

• The information required to create an SQL database connection, such as your SQL server and database name. If you're using Windows Authentication or SQL Server Authentication to authenticate access, you also need your user name and password. You can usually find this information in the connection string.

  Important

  If you use an SQL Server connection string that you copied directly from the Azure portal, you have to manually add your password to the connection string. For sensitive information, such as this string, make sure to use the most secure authentication flow available. Azure recommends that you authenticate access to Azure resources with a managed identity when possible, and assign a role that has the least privilege necessary.

  If this capability is unavailable, make sure to secure connection strings through other measures, such as Azure Key Vault, which you can use with app settings in Standard workflows. You can then directly reference secure strings, such as connection strings and keys. Similar to ARM templates, where you can define environment variables at deployment time, you can define app settings within your Standard logic app workflow definition. You can then capture dynamically generated infrastructure values, such as connection endpoints, storage strings, and more. For more information, see Application types for the Microsoft identity platform.

  • For an SQL database in Azure, the connection string has the following format:

    Server=tcp:{your-server-name}.database.chinacloudapi.cn,1433;Initial Catalog={your-database-name};Persist Security Info=False;User ID={your-user-name};Password={your-password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

    1. To find this string in the Azure portal, open your database.

    2. On the database menu, under Properties, select Connection strings.

  • For an on-premises SQL server, the connection string has the following format:

    Server={your-server-address};Database={your-database-name};User Id={your-user-name};Password={your-password};

• In Standard workflows, to use the SQL built-in triggers, you must enable change tracking in the table where you want to use the trigger. For more information, see Enable and disable change tracking.

• The logic app workflow where you want to access your SQL database. To start your workflow with a SQL Server trigger, you have to start with a blank workflow. To use a SQL Server action, start your workflow with any trigger.

• To connect to an on-premises SQL server, the following extra requirements apply, based on whether you have a Consumption or Standard logic app workflow.

  • Consumption workflow

    In multitenant Azure Logic Apps, you need the on-premises data gateway installed on a local computer and a data gateway resource that's already created in Azure.

  • Standard workflow

    You can use the SQL Server built-in connector or managed connector.

    • To use Microsoft Entra authentication or managed identity authentication with your logic app, you have to set up your SQL Server to work with these authentication types. For more information, see Authentication - SQL Server managed connector reference.

    • To use the built-in connector, you can authenticate your connection with either a managed identity, Microsoft Entra ID, or a connection string. You can adjust connection pooling by specifying parameters in the connection string. For more information, review Connection Pooling.

    • To use the SQL Server managed connector, follow the same requirements as a Consumption logic app workflow in multitenant Azure Logic Apps. For other connector requirements, review the SQL Server managed connector reference.

Add a SQL Server trigger

The following steps use the Azure portal, but with the appropriate Azure Logic Apps extension, you can also use the following tools to create logic app workflows:

• Consumption workflows: Visual Studio or Visual Studio Code

• Standard workflows: Visual Studio Code

When you save your workflow, this step automatically publishes your updates to your deployed logic app, which is live in Azure. With only a trigger, your workflow just checks the SQL database based on your specified schedule. You have to add an action that responds to the trigger.

Add a SQL Server action

The following steps use the Azure portal, but with the appropriate Azure Logic Apps extension, you can also use Visual Studio to edit Consumption logic app workflows or Visual Studio Code to the following tools to edit logic app workflows:

• Consumption workflows: Visual Studio or Visual Studio Code

• Standard workflows: Visual Studio Code

In this example, the logic app workflow starts with the Recurrence trigger, and calls an action that gets a row from an SQL database.

Connect to your database

When you add a trigger or action that connects to a service or system, and you don't have an existing or active connection, Azure Logic Apps prompts you to provide the connection information, which varies based on the connection type, for example:

• A name to use for the connection
• Your account credentials
• The server or system name
• A connection string
• The authentication type to use

After you provide this information, continue with the following steps based on your target database:

• Connect to cloud-based Azure SQL Database or SQL Managed Instance
• Connect to on-premises SQL Server

Connect to Azure SQL Database or SQL Managed Instance

To access a SQL Managed Instance without using the on-premises data gateway, you have to set up the public endpoint on the SQL Managed Instance. The public endpoint uses port 3342, so make sure that you specify this port number when you create the connection from your logic app.

In the connection information box, complete the following steps:

1. For Connection name, provide a name to use for your connection.

2. For Authentication type, select the authentication that's required and enabled on your database in Azure SQL Database or SQL Managed Instance:

   Authentication	Description
   Connection string	- Supported only in Standard workflows with the SQL Server built-in connector. - Requires the connection string to your SQL server and database.
   Active Directory OAuth	- Supported only in Standard workflows with the SQL Server built-in connector. For more information, see the following documentation: - Authentication for SQL Server connector - Enable Open Authorization with Microsoft Entra ID (Microsoft Entra ID OAuth) - OAuth with Microsoft Entra ID
   Logic Apps Managed Identity	- Supported with the SQL Server managed connector. In Standard workflows, this authentication type is available for the SQL Server built-in connector, but the option is named Managed identity instead. - Requires the following items: --- A valid managed identity that's enabled on your logic app resource and has access to your database. --- SQL DB Contributor role access to the SQL Server resource --- Contributor access to the resource group that includes the SQL Server resource. For more information, see the following documentation: - Managed identity authentication for SQL Server connector - SQL - Server-Level Roles
   Service principal (Microsoft Entra application)	- Supported with the SQL Server managed connector. - Requires a Microsoft Entra application and service principal. For more information, see Create a Microsoft Entra application and service principal that can access resources using the Azure portal.
   Microsoft Entra integrated	- Supported with the SQL Server managed connector. - Requires a valid managed identity in Microsoft Entra that's enabled on your logic app resource and has access to your database. For more information, see these topics: - Azure SQL Security Overview - Authentication - Authorize database access to Azure SQL - Authentication and authorization - Azure SQL - Microsoft Entra integrated authentication
   SQL Server Authentication	- Supported with the SQL Server managed connector. - Requires the following items: --- A data gateway resource that's previously created in Azure for your connection, regardless whether your logic app is in multitenant Azure Logic Apps. --- A valid user name and strong password that are created and stored in your SQL Server database. For more information, see the following topics: - Azure SQL Security Overview - Authentication - Authorize database access to Azure SQL - Authentication and authorization

   The following examples show how the connection information box might appear if you use the SQL Server managed connector and select Microsoft Entra integrated authentication:

   Consumption workflows

   

   Standard workflows

   

3. After you select Microsoft Entra integrated, select Sign in. Based on whether you use Azure SQL Database or SQL Managed Instance, select your user credentials for authentication.

4. Select these values for your database:

   Property	Required	Description
   Server name	Yes	The address for your SQL server, for example, Fabrikam-Azure-SQL.database.chinacloudapi.cn
   Database name	Yes	The name for your SQL database, for example, Fabrikam-Azure-SQL-DB
   Table name	Yes	The table that you want to use, for example, SalesLT.Customer

   Tip

   To provide your database and table information, you have these options:

   • Find this information in your database's connection string. For example, in the Azure portal, find and open your database. On the database menu, select either Connection strings or Properties, where you can find the following string:

     Server=tcp:{your-server-address}.database.chinacloudapi.cn,1433;Initial Catalog={your-database-name};Persist Security Info=False;User ID={your-user-name};Password={your-password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

   • By default, tables in system databases are filtered out, so they might not automatically appear when you select a system database. As an alternative, you can manually enter the table name after you select Enter custom value from the database list.

   This database information box looks similar to the following example:

   Consumption workflows

   

   Standard workflows

   

5. Now, continue with the steps that you haven't completed yet in either Add a SQL trigger or Add a SQL action.

Connect to on-premises SQL Server

In the connection information box, complete the following steps:

1. For connections to your on-premises SQL server that require the on-premises data gateway, make sure that you've completed these prerequisites.

   Otherwise, your data gateway resource doesn't appear in the Connection Gateway list when you create your connection.

2. For Authentication Type, select the authentication that's required and enabled on your SQL Server:

   Authentication	Description
   SQL Server Authentication	- Supported with the SQL Server managed connector and SQL Server built-in connector. - Requires the following items: --- A data gateway resource that's previously created in Azure for your connection, regardless whether your logic app is in multitenant Azure Logic Apps. --- A valid user name and strong password that are created and stored in your SQL Server. For more information, see SQL Server Authentication.
   Windows Authentication	- Supported with the SQL Server managed connector. - Requires the following items: --- A data gateway resource that's previously created in Azure for your connection, regardless whether your logic app is in multitenant Azure Logic Apps. --- A valid Windows user name and password to confirm your identity through your Windows account. For more information, see Windows Authentication.

3. Select or provide the following values for your SQL database:

   Property	Required	Description
   SQL server name	Yes	The address for your SQL server, for example, Fabrikam-Azure-SQL.database.chinacloudapi.cn
   SQL database name	Yes	The name for your SQL Server database, for example, Fabrikam-Azure-SQL-DB
   Username	Yes	Your user name for the SQL server and database
   Password	Yes	Your password for the SQL server and database
   Subscription	Yes, for Windows authentication	The Azure subscription for the data gateway resource that you previously created in Azure
   Connection Gateway	Yes, for Windows authentication	The name for the data gateway resource that you previously created in Azure Tip: If your gateway doesn't appear in the list, check that you correctly set up your gateway.

   Tip

   You can find this information in your database's connection string:

   • Server={your-server-address}
   • Database={your-database-name}
   • User ID={your-user-name}
   • Password={your-password}

   The following examples show how the connection information box might appear if you select Windows authentication.

   Consumption workflows

   

   Standard workflows

   

4. When you're ready, select Create.

5. Now, continue with the steps that you haven't completed yet in either Add a SQL trigger or Add a SQL action.

Handle bulk data

Sometimes, you work with result sets so large that the connector doesn't return all the results at the same time. Or, you want better control over the size and structure for your result sets. The following list includes some ways that you can handle such large result sets:

• To help you manage results as smaller sets, turn on pagination. For more information, see Get bulk data, records, and items by using pagination. For more information, see SQL Pagination for bulk data transfer with Logic Apps.

• Create a stored procedure that organizes the results the way that you want. The SQL Server connector provides many backend features that you can access by using Azure Logic Apps so that you can more easily automate business tasks that work with SQL database tables.

  When a SQL action gets or inserts multiple rows, your logic app workflow can iterate through these rows by using an until loop within these limits. However, when your logic app has to work with record sets so large, for example, thousands or millions of rows, that you want to minimize the costs resulting from calls to the database.

  To organize the results in the way that you want, you can create a stored procedure that runs in your SQL instance and uses the SELECT - ORDER BY statement. This solution gives you more control over the size and structure of your results. Your logic app calls the stored procedure by using the SQL Server connector's Execute stored procedure action. For more information, see SELECT - ORDER BY Clause.

  Note

  The SQL Server connector has a stored procedure timeout limit that's less than 2 minutes. Some stored procedures might take longer than this limit to complete, causing a 504 Timeout error. You can work around this problem by using a SQL completion trigger, native SQL pass-through query, a state table, and server-side jobs.

  For this task, you can use the Azure Elastic Job Agent for Azure SQL Database. For SQL Server on premises and SQL Managed Instance, you can use the SQL Server Agent. To learn more, see Handle long-running stored procedure timeouts in the SQL Server connector for Azure Logic Apps.

Handle dynamic bulk data

When you call a stored procedure by using the SQL Server connector, the returned output is sometimes dynamic. In this scenario, follow these steps:

1. In the Azure portal, open your logic app and workflow in the designer.

2. View the output format by performing a test run. Copy and save your sample output.

3. In the designer, under the action where you call the stored procedure, add the built-in action named Parse JSON.

4. In the Parse JSON action, select Use sample payload to generate schema.

5. In the Enter or paste a sample JSON payload box, paste your sample output, and select Done.

   Note

   If you get an error that Azure Logic Apps can't generate a schema, check that your sample output's syntax is correctly formatted. If you still can't generate the schema, in the Schema box, manually enter the schema.

6. When you're done, save your workflow.

7. To reference the JSON content properties, select inside the edit boxes where you want to reference those properties so that the dynamic content list appears. In the list, under the Parse JSON heading, select the data tokens for the JSON content properties that you want.

Next steps

• Managed connectors for Azure Logic Apps
• Built-in connectors for Azure Logic Apps