Get started with the Azure SQL Database connector

Using the Azure SQL Database connector, create workflows for your organization that manage data in your tables.

With SQL Database, you:

  • Build your workflow by adding a new customer to a customers database, or updating an order in an orders database.
  • Use actions to get a row of data, insert a new row, and even delete. For example, when a record is created in Dynamics CRM Online (a trigger), then insert a row in an Azure SQL Database (an action).

This topic shows you how to use the SQL Database connector in a logic app, and also lists the actions.

Note

This version of the article applies to Logic Apps general availability (GA).

To learn more about Logic Apps, see What are logic apps and create a logic app.

Connect to Azure SQL Database

Before your logic app can access any service, you first create a connection to the service. A connection provides connectivity between a logic app and another service. For example, to connect to SQL Database, you first create a SQL Database connection. To create a connection, you enter the credentials you normally use to access the service you are connecting to. So, in SQL Database, enter your SQL Database credentials to create the connection.

Create the connection

Prerequisites

  • An Azure account; you can create a free account
  • An Azure SQL Database with its connection information, including the server name, database name, and username/password. This information is included in the SQL Database connection string:

    Server=tcp:yoursqlservername.database.windows.net,1433;Initial Catalog=yourqldbname;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

    Read more about Azure SQL Databases.

Note

When you create an Azure SQL Database, you can also create the sample databases included with SQL.

Before using your Azure SQL Database in a logic app, connect to your SQL Database. You can do this easily within your logic app on the Azure portal.

Connect to your Azure SQL Database using the following steps:

  1. Create a logic app. In the Logic Apps designer, add a trigger, and then add an action. Select Show Microsoft managed APIs in the drop down list, and then enter "sql" in the search box. Select one of the actions:

    SQL Azure connection creation step

  2. If you haven't previously created any connections to SQL Database, you are prompted for the connection details:

    SQL Azure connection creation step

  3. Enter the SQL Database details. Properties with an asterisk are required.

    Property Details
    Connect via Gateway Leave this unchecked. This is used when connecting to an on-premises SQL Server.
    Connection Name * Enter any name for your connection.
    SQL Server Name * Enter the server name; which is something like servername.database.windows.net. The server name is displayed in the SQL Database properties in the Azure portal, and also displayed in the connection string.
    SQL Database Name * Enter the name you gave your SQL Database. This is listed in the SQL Database properties in the connection string: Initial Catalog=yoursqldbname.
    Username * Enter the username you created when the SQL Database was created. This is listed in the SQL Database properties in the Azure portal.
    Password * Enter the password you created when the SQL Database was created.

    These credentials are used to authorize your logic app to connect, and access your SQL data. Once complete, your connection details look similar to the following:

    SQL Azure connection creation step

  4. Select Create.
  5. Notice the connection has been created. Now, proceed with the other steps in your logic app:

    SQL Azure connection creation step

Use a trigger

This connector does not have any triggers. Use other triggers to start the logic app, such as a Recurrence trigger, an HTTP Webhook trigger, triggers available with other connectors, and more. Create a logic app provides an example.

Use an action

An action is an operation carried out by the workflow defined in a logic app. Learn more about actions.

  1. Select the plus sign. You see several choices: Add an action, Add a condition, or one of the More options.

  2. Choose Add an action.
  3. In the text box, type “sql” to get a list of all the available actions.

  4. In our example, choose SQL Server - Get row. If a connection already exists, then select the Table name from the drop-down list, and enter the Row ID you want to return.

    If you are prompted for the connection information, then enter the details to create the connection. Create the connection in this topic describes these properties.

    Note

    In this example, we return a row from a table. To see the data in this row, add another action that creates a file using the fields from the table. For example, add a OneDrive action that uses the FirstName and LastName fields to create a new file in the cloud storage account.

  5. Save your changes (top left corner of the toolbar). Your logic app is saved and may be automatically enabled.

Technical Details

SQL Database actions

An action is an operation carried out by the workflow defined in a logic app. The SQL Database connector includes the following actions.

Action Description
ExecuteProcedure Executes a stored procedure in SQL
GetRow Retrieves a single row from a SQL table
GetRows Retrieves rows from a SQL table
InsertRow Inserts a new row into a SQL table
DeleteRow Deletes a row from a SQL table
GetTables Retrieves tables from a SQL database
UpdateRow Updates an existing row in a SQL table

Action Details

In this section, see the specific details about each action, including any required or optional input properties, and any corresponding output associated with the connector.

Execute stored procedure

Executes a stored procedure in SQL.

Property Name Display Name Description
procedure * Procedure name The name of the stored procedure you want to execute
parameters * Input parameters The parameters are dynamic and based on the stored procedure you choose.

For example, if you're using the Adventure Works sample database, choose the ufnGetCustomerInformation stored procedure. The Customer ID input parameter is displayed. Enter "6" or one of the other customer IDs.

An asterisk (*) means the property is required.

Output Details

ProcedureResult: Carries result of stored procedure execution

Property Name Data Type Description
OutputParameters object Output parameter values
ReturnCode integer Return code of a procedure
ResultSets object Result sets

Get row

Retrieves a single row from a SQL table.

Property Name Display Name Description
table * Table name Name of SQL table
id * Row id Unique identifier of the row to retrieve

An asterisk (*) means the property is required.

Output Details

Item

Property Name Data Type
ItemInternalId string

Get rows

Retrieves rows from a SQL table.

Property Name Display Name Description
table* Table name Name of SQL table
$skip Skip Count Number of entries to skip (default = 0)
$top Maximum Get Count Maximum number of entries to retrieve (default = 256)
$filter Filter Query An ODATA filter query to restrict the number of entries
$orderby Order By An ODATA orderBy query for specifying the order of entries

An asterisk (*) means the property is required.

Output Details

ItemsList

Property Name Data Type
value array

Insert row

Inserts a new row into a SQL table.

Property Name Display Name Description
table* Table name Name of SQL table
item* Row Row to insert into the specified table in SQL

An asterisk (*) means the property is required.

Output Details

Item

Property Name Data Type
ItemInternalId string

Delete row

Deletes a row from a SQL table.

Property Name Display Name Description
table* Table name Name of SQL table
id* Row id Unique identifier of the row to delete

An asterisk (*) means the property is required.

Output Details

None.

Get tables

Retrieves tables from a SQL database.

There are no parameters for this call.

Output Details

TablesList

Property Name Data Type
value array

Update row

Updates an existing row in a SQL table.

Property Name Display Name Description
table* Table name Name of SQL table
id* Row id Unique identifier of the row to update
item* Row Row with updated values

An asterisk (*) means the property is required.

Output Details

Item

Property Name Data Type
ItemInternalId string

HTTP Responses

When making calls to the different actions, you may get certain responses. The following table outlines the responses and their descriptions:

Name Description
200 OK
202 Accepted
400 Bad Request
401 Unauthorized
403 Forbidden
404 Not Found
500 Internal Server Error. Unknown error occurred
default Operation Failed.

Next steps

Create a logic app. Explore the other available connectors in Logic Apps at our APIs list.