Connect to an SQL database from workflows in Azure Logic Apps

Applies to: Azure Logic Apps (Consumption + Standard)

This article 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
Standard Single-tenant Azure Logic Apps Managed connector, which appears in the designer under the Azure label, and built-in connector, which appears in the designer under the Built-in label and is service provider based. The built-in version differs in the following ways:

- The built-in version doesn't have triggers. You can use the SQL managed connector trigger or a different trigger.

- The built-in version can connect directly to an SQL database and access Azure virtual networks. You don't need 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 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};

  • 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 logic app workflow

    • Standard logic app workflow

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

      • To use the built-in connector, you can authenticate your connection with either a managed identity, Azure Active Directory, 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 blank logic app workflow in the designer.

  2. Find and select the SQL Server trigger that you want to use.

    1. On the designer, under the search box, select Standard.

    2. In the search box, enter sql server.

    3. From the triggers list, select the SQL trigger that you want.

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

      Screenshot showing the Azure portal, Consumption logic app workflow designer, search box with "sql server", and "When an item is created" trigger selected.

  3. Provide the information for your connection. When you're done, select Create.

  4. After the trigger information box appears, specify the interval and frequency for how often the trigger checks the table.

  5. To add other properties available for this trigger, open the Add new parameter list and select those properties.

    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 logic app workflow in the designer.

  2. Find and select the SQL Server action that you want to use.

    This example continues with the action named Get row.

    1. Under the trigger or action where you want to add the SQL action, select New step.

      Or, to add an action between existing steps, move your pointer over the connecting arrow. Select the plus sign (+) that appears, and then select Add an action.

    2. Under the Choose an operation search box, select Standard.

    3. In the search box, enter sql server.

    4. From the actions list, select the SQL Server action that you want.

      This example uses the Get row action, which gets a single record.

      Screenshot showing the Azure portal, workflow designer for Consumption logic app, the search box with "sql server", and "Get row" selected in the "Actions" list.

  3. Provide the information for your connection. When you're done, select Create.

  4. If you haven't already provided the SQL server name and database name, provide those values. Otherwise, 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.

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

    Screenshot showing Consumption workflow designer and the "Get row" action with the example "Table name" property value 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:

  • 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 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.
    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
    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 Azure Active Directory Open Authentication (Azure AD OAuth)
    - Azure Active Directory Open Authentication
    Service principal (Azure AD application) - Supported with the SQL Server managed connector.

    - Requires an Azure AD application and service principal. For more information, see Create an Azure AD application and service principal that can access resources using the Azure portal.
    Azure AD Integrated - Supported with the SQL Server managed connector and ISE-versioned connector.

    - Requires a valid managed identity in Azure Active Directory (Azure AD) 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 - Azure AD 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 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 Azure AD Integrated authentication:

    • Consumption logic app workflows

      Screenshot showing the Azure portal, workflow designer, and "SQL Server" cloud connection information with selected authentication type for Consumption.

    • Standard logic app workflows

      Screenshot showing the Azure portal, workflow designer, and "SQL Server" cloud connection information with selected authentication type for Standard.

  3. After you select Azure AD 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 logic app workflows

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

    • Standard logic app workflows

      Screenshot showing 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 won'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 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.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 logic app workflows

      Screenshot showing the Azure portal, workflow designer, and "SQL Server" on-premises connection information with selected authentication for Consumption.

    • Standard logic app workflows

      Screenshot showing the Azure portal, workflow designer, and "SQL Server" on-premises connection information with selected authentication for Standard.

  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 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 a new action.

  4. In the Choose an operation box, find and select the action named Parse JSON.

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

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

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

  8. To reference the JSON content properties, click 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