Automate workflows for a SQL database by using Azure Logic Apps

This article shows how you can access data in your SQL database from inside a logic app with the SQL Server connector. That way, you can automate tasks, processes, or workflows that manage your SQL data and resources by creating logic apps. The SQL Server connector works for SQL Server as well as Azure SQL Database and Azure SQL Managed Instance.

You can create logic apps that run when triggered by events in your SQL database or in other systems, such as Dynamics CRM Online. Your logic apps can also get, insert, and delete data along with running SQL queries and stored procedures. For example, you can build a logic app that automatically checks for new records in Dynamics CRM Online, adds items to your SQL database for any new records, and then sends email alerts about the added items.

If you're new to logic apps, review What is Azure Logic Apps and Quickstart: Create your first logic app. For connector-specific technical information, limitations, and known issues, see the SQL Server connector reference page.

Prerequisites

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

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

    Your tables must have data so that your logic app can return results when calling operations. If you use Azure SQL Database, you can use sample databases, which are included.

  • Your SQL server name, database name, your user name, and your password. You need these credentials so that you can authorize your logic to access your SQL server.

    • For on-premises SQL Server, you can find these details in the connection string:

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

    • For Azure SQL Database, you can find these details in the connection string.

      For example, to find this string in the Azure portal, open your database. On the database menu, select either Connection strings or Properties:

      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;

  • Based on whether your logic apps are going to run in global, multi-tenant Azure or an integration service environment (ISE), here are other requirements for connecting to on-premises SQL Server:

    • For logic apps in global, multi-tenant Azure that connect to on-premises SQL Server, you need to have the on-premises data gateway installed on a local computer and a data gateway resource that's already created in Azure.

    • For logic apps in an ISE that connect to on-premises SQL Server and use Windows authentication, the ISE-versioned SQL Server connector doesn't support Windows authentication. So, you still need to use the data gateway and the non-ISE SQL Server connector. For other authentication types, you don't need to use the data gateway and can use the ISE-versioned connector.

  • The logic app where you need access to your SQL database. To start your logic app with a SQL trigger, you need a blank logic app.

Connect to your database

When you add a trigger or action that connects to a service or system for the first time, the workflow designer prompts you to create a connection by providing the necessary information, which varies based on the connection, for example:

  • The name that you want to use for the new connection

  • The name for the system or server

  • Your user or account credentials

  • The authentication type to use

Now, continue with these steps:

Connect to Azure SQL Database or Managed Instance

To access an Azure SQL Managed Instance without using the on-premises data gateway or integration service environment, you have to set up the public endpoint on the Azure 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.

The first time that you add either a SQL trigger or SQL action, and you haven't previously created a connection to your database, you're prompted to complete these steps:

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

    Authentication Description
    Azure AD Integrated - Supports both the non-ISE and ISE SQL Server connector.

    - Requires a valid identity in Azure Active Directory (Azure AD) that 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 - Supports both the non-ISE and ISE SQL Server connector.

    - Requires a valid user name and strong password that are created and stored in your database.

    For more information, see these topics:

    - Azure SQL Security Overview - Authentication
    - Authorize database access to Azure SQL - Authentication and authorization

    Managed Identity - Supports both the non-ISE and ISE SQL Server connector.

    - Requires a valid managed identity that has access to your database, SQL DB Contributor role access to the SQL Server resource, and Contributor access to the resource group that includes the SQL Server resource.

    For more information, see SQL - Server-Level Roles.

    This example continues with Azure AD Integrated:

    Screenshot that shows the "SQL Server" connection window with the opened "Authentication Type" list and "Azure AD Integrated" selected.

  2. After you select Azure AD Integrated, select Sign In. Based on whether you use Azure SQL Database or Azure SQL Managed Instance, select your user credentials for authentication.

  3. 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 this 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 example shows how these values might look:

    Create connection to SQL database

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

The first time that you add either a SQL trigger or SQL action, and you haven't previously created a connection to your database, you're prompted to complete these 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
    Windows Authentication - Supports only the non-ISE SQL Server connector, which requires a data gateway resource that's previously created in Azure for your connection, regardless whether you use multi-tenant Azure or an ISE.

    - Requires a valid Windows user name and password to confirm your identity through your Windows account.

    For more information, see Windows Authentication

    SQL Server Authentication - Supports both the non-ISE and ISE SQL Server connector.

    - Requires a valid user name and strong password that are created and stored in your SQL Server.

    For more information, see SQL Server Authentication.

    This example continues with Windows Authentication:

    Select authentication type to use

  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}

    This example shows how these values might look:

    Create SQL Server connection completed

  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.

Add a SQL trigger

  1. In the Azure portal or in Visual Studio, create a blank logic app, which opens the Logic App Designer. This example continues with the Azure portal.

  2. On the designer, in the search box, enter sql server. From the triggers list, select the SQL trigger that you want. This example uses the When an item is created trigger.

    Select "When an item is created" trigger

  3. If you're connecting to your SQL database for the first time, you're prompted to create your SQL database connection now. After you create this connection, you can continue with the next step.

  4. In the trigger, specify the interval and frequency for how often the trigger checks the table.

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

    This trigger returns only one row from the selected table, and nothing else. To perform other tasks, continue by adding either a SQL 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 connector's reference page.

  6. On the designer toolbar, select Save.

    Although this step automatically enables and publishes your logic app live in Azure, the only action that your logic app currently takes is to check your database based on your specified interval and frequency.

Trigger recurrence shift and drift

Connection-based triggers where you need to create a connection first, such as the SQL trigger, differ from built-in triggers that run natively in Azure Logic Apps, such as the Recurrence trigger. In recurring connection-based triggers, the recurrence schedule isn't the only driver that controls execution, and the time zone only determines the initial start time. Subsequent runs depend on the recurrence schedule, the last trigger execution, and other factors that might cause run times to drift or produce unexpected behavior, for example, not maintaining the specified schedule when daylight saving time (DST) starts and ends. To make sure that the recurrence time doesn't shift when DST takes effect, manually adjust the recurrence so that your logic app continues to run at the expected time. Otherwise, the start time shifts one hour forward when DST starts and one hour backward when DST ends. For more information, see Recurrence for connection-based triggers.

Add a SQL action

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

  1. In the Azure portal or in Visual Studio, open your logic app in Logic App Designer. This example continues the Azure portal.

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

    Add an action to your logic app

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

  3. Under Choose an action, in the search box, enter sql server. From the actions list, select the SQL action that you want. This example uses the Get row action, which gets a single record.

    Select SQL "Get row" action

  4. If you're connecting to your SQL database for the first time, you're prompted to create your SQL database connection now. After you create this connection, you can continue with the next step.

  5. Select the Table name, which is SalesLT.Customer in this example. Enter the Row ID for the record that you want.

    Select table name and specify row ID

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

  6. When you're done, on the designer toolbar, select Save.

    This step automatically enables and publishes your logic app live in Azure.

Handle bulk data

Sometimes, you have to 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. Here's 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 in the Logic App 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, select New step.

  4. Under Choose an action, find and select the Parse JSON action.

  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 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. On the designer toolbar, select Save.

  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.

Troubleshoot problems

Connection problems

Connection problems can commonly happen, so to troubleshoot and resolve these kinds of issues, review Solving connectivity errors to SQL Server. Here are some examples:

  • A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

  • (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

  • (provider: TCP Provider, error: 0 - No such host is known.) (Microsoft SQL Server, Error: 11001)

Connector-specific details

For technical information about this connector's triggers, actions, and limits, see the connector's reference page, which is generated from the Swagger description.

Next steps