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:

Supported SQL editions

The SQL Server connector supports the following SQL editions:

Connector technical reference

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

Logic app Environment Connector version
Consumption Multi-tenant Azure Logic Apps Managed connector, which appears in the designer under the Standard label. For more information, review the following documentation:

- SQL Server managed connector reference
- Managed connectors in Azure Logic Apps
Consumption Integration service environment (ISE) Managed connector, which appears in the designer under the Standard label, and the ISE version, which has different message limits than the Standard class. For more information, review the following documentation:

- SQL Server managed connector reference
- ISE message limits
- 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 the 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 free Azure account.

  • 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 an SQL database in Azure, the connection string has the following format:

      Server=tcp:{your-server-name}.database.windows.net,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

    • 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 multi-tenant 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:

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

  2. In the designer, under the search box, select Standard. Then, follow these general steps to add the SQL Server managed trigger you want.

    This example continues with the trigger named When an item is created.

  3. If prompted, provide the information for your connection. When you're done, select Create.

  4. After the trigger information box appears, provide the necessary information required by your selected trigger.

    For this example, in the trigger named When an item is created, provide the values for the SQL server name and database name, if you didn't previously provide them. Otherwise, from the Table name list, select the table that you want to use. Select the Frequency and Interval to set the schedule for the trigger to check for new items.

    Screenshot shows Consumption workflow designer and managed action named When an item is created.

  5. If any other properties are available for this trigger, open the Add new parameter list, and select those properties relevant to your scenario.

    This trigger returns only one row from the selected table, and nothing else. To perform other tasks, continue by adding either a SQL Server connector action or another action that performs the next task that you want in your logic app workflow.

    For example, to view the data in this row, you can add other actions that create a file that includes the fields from the returned row, and then send email alerts. To learn about other available actions for this connector, see the SQL Server managed connector reference.

  6. When you're done, save your workflow. On the designer toolbar, select Save.

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:

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

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

  2. In the designer, follow these general steps to add the SQL Server managed action you want.

    This example continues with the action named Get row, which gets a single record.

  3. If prompted, provide the information for your connection. When you're done, select Create.

  4. After the action information box appears, from the Table name list, select the table that you want to use. In the Row id property, enter the ID for the record that you want.

    For this example, the table name is SalesLT.Customer.

    Screenshot shows Consumption workflow designer and action named Get row with the example table name and empty row ID.

    This action returns only one row from the selected table, and nothing else. To view the data in this row, add other actions. For example, such actions might create a file, include the fields from the returned row, and store the file in a cloud storage account. To learn about other available actions for this connector, see the connector's reference page.

  5. When you're done, save your workflow. On the designer toolbar, select Save.

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:

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

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

Connect to Azure SQL Database or SQL Managed Instance

To access a SQL Managed Instance without using the on-premises data gateway or integration service environment, 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 Microsoft Entra ID Open Authentication (Microsoft Entra ID OAuth)
    - Microsoft Entra ID Open Authentication
    Logic Apps Managed Identity - Supported with the SQL Server managed connector and ISE-versioned 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 and ISE-versioned 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 and ISE-versioned 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 multi-tenant Azure Logic Apps or an ISE.

    --- 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

    Screenshot shows Azure portal, Consumption workflow, and SQL Server cloud connection information with selected authentication type.

    Standard workflows

    Screenshot shows Azure portal, Standard workflow, and SQL Server cloud connection information with selected authentication type.

  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.windows.net
    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.windows.net,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

    Screenshot shows SQL cloud database cloud information with sample values for Consumption.

    Standard workflows

    Screenshot shows SQL cloud database information with sample values for Standard.

  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, SQL Server built-in connector, and ISE-versioned 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 multi-tenant Azure Logic Apps or an ISE.

    --- 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 multi-tenant Azure Logic Apps or an ISE.

    --- 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.windows.net
    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

    Screenshot shows Azure portal, Consumption workflow, and SQL Server on-premises connection information with selected authentication.

    Standard workflows

    Screenshot shows Azure portal, Standard workflow, and SQL Server on-premises connection information with selected authentication.

  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:

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