Connect to SQL Server or Azure SQL Database from 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, and workflows that manage your SQL data and resources by creating logic apps. The connector works for both SQL Server on premises and for Azure SQL Database in the cloud.

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

If you don't have an Azure subscription, sign up for a free Azure account. 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, see the SQL Server connector reference.

Prerequisites

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

  • An Azure SQL database or a SQL Server database

    Your tables must have data so that your logic app can return results when calling operations. If you create an 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 Azure SQL Database, you can find these details in the connection string, or in the Azure portal under the SQL Database properties:

      "Server=tcp:<yourServerName>.database.windows.net,1433;Initial Catalog=<yourDatabaseName>;Persist Security Info=False;User ID=<yourUserName>;Password=<yourPassword>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

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

      "Server=<yourServerAddress>;Database=<yourDatabaseName>;User Id=<yourUserName>;Password=<yourPassword>;"

  • Before you can connect logic apps to on-premises systems, such as SQL Server, you must set up an on-premises data gateway. That way, you can select the gateway when you create the SQL connection for your logic app.

Add SQL trigger

In Azure Logic Apps, every logic app must start with a trigger, which fires when a specific event happens or when a specific condition is met. Each time the trigger fires, the Logic Apps engine creates a logic app instance and starts running your app's workflow.

  1. In the Azure portal or Visual Studio, create a blank logic app, which opens Logic Apps Designer. This example uses the Azure portal.

  2. In the search box, enter "sql server" as your filter. From the triggers list, select the SQL trigger that you want.

    For this example, select this trigger: SQL Server - When an item is created

    Select "SQL Server - When an item is created" trigger

  3. If you're prompted for connection details, create your SQL connection now. Or, if your connection already exists, select the Table name that you want from the list.

    Select table

  4. Set the Interval and Frequency properties, which specify how often your logic app checks the table.

    This example only checks the selected table, nothing else. To do something more interesting, add actions that perform the tasks you want.

    For example, to view the new item in the table, you might add other actions, such as create a file that has fields from the table, and then send email alerts. To learn about other actions for this connector or other connectors, see Logic Apps connectors.

  5. When you're done, on the designer toolbar, choose Save.

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

Add SQL action

In Azure Logic Apps, an action is a step in your workflow that follows a trigger or another 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 Visual Studio, open your logic app in Logic Apps Designer. This example uses the Azure portal.

  2. In the Logic App Designer, under the trigger or action, choose New step > Add an action.

    Choose "New step", "Add an action"

    To add an action between existing steps, move your mouse over the connecting arrow. Choose the plus sign (+) that appears, and then choose Add an action.

  3. In the search box, enter "sql server" as your filter. From the actions list, select any SQL action that you want.

    For this example, select this action, which gets a single record: SQL Server - Get row

    Enter "sql server", select "SQL Server - Get row"

  4. If you're prompted for connection details, create your SQL connection now. Or, if your connection exists, select a Table name, and enter the Row ID for the record that you want.

    Enter the table name and row ID

    This example returns only one row from the selected table, nothing else. To view the data in this row, you might add other actions that create a file with fields from the row for later review, and store that file in a cloud storage account. To learn about other actions in this connector or other connectors, see Logic Apps connectors.

  5. When you're done, on the designer toolbar, choose Save.

Connect to your database

Before your logic app can access any service, you must create a connection between your logic app and that service. If you didn't previously create this connection, you're prompted for connection information when you add a trigger or action for that service to your logic app. The Logic Apps Designer provides an easy way for you to create this connection directly from your logic app.

Connect to Azure SQL Database

  1. When the SQL trigger or action prompts you for connection information, follow these steps:

    1. Create a name for your connection.

    2. Select your SQL server, and then select your database.

      The database list appears only after you select your SQL server.

    3. Provide your user name and password for your server.

      You can find this information either in the Azure portal under your SQL database properties or in your connection string:

      "User ID=<yourUserName>"
      "Password=<yourPassword>"

    This example shows the connection information for a trigger, but these steps work for actions too.

    Create Azure SQL Database connection
    Asterisks (*) indicate required values.

    Property Value Details
    Connection Name <my-sql-connection> The name for your connection
    SQL Server Name <my-sql-server> The name for your SQL server
    SQL Database Name <my-sql-database> The name for your SQL database
    Username <my-sql-username> The user name for accessing your database
    Password <my-sql-password> The password for accessing your database
  2. When you're done, choose Create.

  3. After you create your connection, continue with Add SQL trigger or Add SQL action.

Connect to SQL Server

Before you can select your gateway, make sure that you already set up your data gateway. That way, your gateway appears in the gateways list when you create your connection.

  1. When the SQL trigger or action prompts you for connection information, follow these steps:

    1. In the trigger or action, select Connect via on-premise data gateway so that the SQL server options appear.

    2. Create a name for your connection.

    3. Provide the address for your SQL server, then provide the name for your database.

      You can find this information in your connection string:

      • "Server=<yourServerAddress>"
      • "Database=<yourDatabaseName>"
    4. Provide your user name and password for your server.

      You can find this information in your connection string:

      • "User ID=<yourUserName>"
      • "Password=<yourPassword>"
    5. If your SQL server uses Windows or Basic authentication, select the authentication type.

    6. Select the name for your on-premises data gateway that you previously created.

      If your gateway doesn't appear in the list, check that you correctly set up your gateway.

    This example shows the connection information for a trigger, but these steps work for actions too.

    Create SQL Server connection
    Asterisks (*) indicate required values.

    Property Value Details
    Connect via on-premise gateway Select this option first for SQL Server settings.
    Connection Name <my-sql-connection> The name for your connection
    SQL Server Name <my-sql-server> The name for your SQL server
    SQL Database Name <my-sql-database> The name for your SQL database
    Username <my-sql-username> The user name for accessing your database
    Password <my-sql-password> The password for accessing your database
    Authentication Type Windows or Basic Optional: The authentication type used by your SQL server
    Gateways <my-data-gateway> The name for your on-premises data gateway
  2. When you're done, choose Create.

  3. After you create your connection, continue with Add SQL trigger or Add SQL action.

Process data in bulk

When 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, you can use pagination, which helps you manage those results as smaller sets.

Set up pagination

For some connectors and their actions that retrieve multiple items, your results might exceed the connector's default page size. In this case, the action returns only the first page of results. For example, the default page size for the SQL Server - Get rows action is 2048, but might differ based on other settings. To make sure you get all the records, turn on the Pagination setting for that action. This setting has your logic app ask the connector for the remaining records, but return all the results as a single message when the action finishes.

Here are just some connectors where you can turn on pagination for specific actions:

Here is an example for the Get rows action:

  1. To find whether the action supports pagination, open the action's Settings.

    On the action, open "Settings"

  2. If the action supports pagination, change the Pagination setting from Off to On. To make sure that the action returns a minimum set of results, specify a value for Limit.

    Specify that the action return a minimum number of results

  3. When you're ready, choose Done.

Create a stored procedure

When getting or inserting multiple rows, your logic app can iterate through these items by using an until loop within these limits. But, sometimes your logic app has to work with record sets so large, such as thousands or millions of rows, that you want to minimize the costs for calls to the database.

Instead, you can create a stored procedure that runs in your SQL instance and uses the SELECT - ORDER BY statement to organize the results the way you want. 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 solution details, see these articles:

Connector-specific details

For technical information about this connector's triggers, actions, and limits, see the connector's reference details.

Next steps