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

Handle bulk data

Sometimes, you might 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:

  • To help you manage results as smaller sets, turn on pagination. For more information, see Get bulk data, records, and items by using pagination.

  • Create a stored procedure that organizes the results the way you want.

    When getting or inserting multiple rows, your logic app 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 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