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:
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 authenticationSQL 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 authorizationManaged 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:

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.
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.netDatabase name Yes The name for your SQL database, for example, Fabrikam-Azure-SQL-DBTable name Yes The table that you want to use, for example, SalesLT.CustomerTip
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:

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:
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.
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 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.netSQL database name Yes The name for your SQL Server database, for example, Fabrikam-Azure-SQL-DBUsername 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:

When you're ready, select Create.
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
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.
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.
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.
In the trigger, specify the interval and frequency for how often the trigger checks the table.
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.
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.
In the Azure portal or in Visual Studio, open your logic app in Logic App Designer. This example continues the Azure portal.
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 mouse over the connecting arrow. Select the plus sign (+) that appears, and then select Add an action.
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.
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.
Select the Table name, which is
SalesLT.Customerin this example. Enter the Row ID for the record that you want.
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.
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:
To help you manage results as smaller sets, turn on pagination. For more information, see Get bulk data, records, and items by using pagination. For more information, see SQL Pagination for bulk data transfer with Logic Apps.
Create a stored procedure that organizes the results the way that you want. The SQL connector provides many backend features that you can access by using Azure Logic Apps so that you can more easily automate business tasks that work with SQL database tables.
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 more information, see SELECT - ORDER BY Clause.
Note
The SQL connector has a stored procedure timeout limit that's less than 2-minutes. Some stored procedures might take longer than this limit to complete, causing a
504 Timeouterror. You can work around this problem by using a SQL completion trigger, native SQL pass-through query, a state table, and server-side jobs.For this task, you can use the Azure Elastic Job Agent for Azure SQL Database. For SQL Server on premises and Azure SQL Managed Instance, you can use the SQL Server Agent. To learn more, see Handle long-running stored procedure timeouts in the SQL connector for Azure Logic Apps.
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:
In the Azure portal, open your logic app in the Logic App Designer.
View the output format by performing a test run. Copy and save your sample output.
In the designer, under the action where you call the stored procedure, select New step.
Under Choose an action, find and select the Parse JSON action.
In the Parse JSON action, select Use sample payload to generate schema.
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.
On the designer toolbar, select Save.
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
- Learn about other connectors for Azure Logic Apps