SQL Server

Microsoft SQL Server is a relational database management system developed by Microsoft. Connect to SQL Server to manage data. You can perform various actions such as create, update, get, and delete on rows in a table.

This connector is available in the following products and regions:

Ensure your account is correctly set up and provisioned as a SQL AAD user prior to using Active Directory authentication in the SQL Server connector. Follow these instructions to do so.

Note that if you are using non-AAD connection types you should provide the same Server name/Database name as it defined in connection (or Use connection settings option).

To use this connector in Power Apps, learn more here.

Known issues and limitations

The followings are some of the known limitations of using SQL connector

1. Stored Procedures cannot be invoked from Power Apps.
   • One workaround is to create a flow and invoke the flow from Power Apps.
2. Insert and update to a table does not work if you have a SQL Server side Trigger defined on the table. To workaround this issue, you can do either of the following:
   • Use a Stored Procedure or Native Query
   • Remove the Trigger from your SQL table
3. When invoking a Stored Procedure on an on-premises SQL Server, we have the following limitations:
   • Output values for OUTPUT parameters are not returned. You can still specify input values for OUTPUT parameters.
   • Return value is not available.
   • Only the first result set is returned.
   • Dynamics schemas are not supported for result sets.
4. A Primary Key is required for the following operations:
   • GetItem
   • UpdateItem
   • DeleteItem
5. When invoking triggers, we have the following limitations:
   • A ROWVERSION column is required for OnUpdatedItems
   • An IDENTITY column is required for OnNewItems
6. The response size limit is 8MB through on-premises SQL Server.
7. The request size limit is 2MB through on-premises SQL Server.
8. SQL native query is not supported for on-premises SQL Server.
9. The minimum supported version for on-premises SQL Server is SQL Server 2005.
10. The following data types cannot be used as query option predicates:
    • date
    • datetime
    • datetime2
    • smalldatetime
11. Currently multiple result sets support is limited for SQL native query execution: only result sets, up to the first empty one result set, will be returned. As a workaround Stored Procedure usage is proposed.
12. The result set schema after executing SQL queries and stored procedures should contain unique non-empty column names.
13. The result set schema for any operation inside the stored procedure that returns some set of results (e.g. any inner SELECT statement) should contain unique non-empty column names.
14. If SQL query/stored procedure execution time exceeds 110 seconds, actions will timeout.
15. For INSERT and UPDATE operations, the connector will return the updated rows. This requires that the connection will need to have permission to read data from the SQL Server as well.
16. Private endpoint of SQL Server can only be accessed within ISE. For more information, see Logic Apps documentation.
17. Setting "Force Encryption" is not supported. By default, SQL Database and SQL Managed Instance secure customer data by encrypting data in motion with Transport Layer Security (TLS). For more information, see Azure SQL Security Overview.
18. Using Service Principal to connect to SQL Server is not supported.
19. Due to current authentication pipeline limitations AAD Guest users are not supported to use AAD type connections to SQL Server. As a workaround please use Sql/Windows auth type connections.

Special throttling limits

SQL connector has different throttling limits in different cases. In the following table, Native operations include SQL stored procedure and SQL Query; CRUD are for Create, Read, Update and Delete operations.

Power Apps data type mappings

Power Apps delegable functions and operations for SQL Server

These Power Apps operations, for a given data type, may be delegated to SQL Server for processing (rather than processing locally within Power Apps).

1. Direct date filters do not work for SQL Server. However, you can create a calculated column that will work. For instance, ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])) and then filter on the calculated number column.
2. The 'in' (substring) operator. Supported for ("string value" in <column>), but not for (<column> in "string value").
3. StartsWith. Supported for (StartsWith(<column>, "string value")), but not for (StartsWith("string value", <column>)).
4. EndsWith. Supported for (EndsWith(<column>, "string value")) but not for (EndsWith("string value", <column>)). If a char(10) column has a value of "hello", EndsWith(<column>, "llo") will return false, and that's by design. A char(10) column has 10 characters.
5. An expression such as Filter('[dbo].[MyOrders]', !IsBlank(CustomerId)) won't delegate to the server. However, this is semantically close to Filter('[dbo].[MyOrders]', CustomerId <> Blank()), which does delegate to the server. These expressions differ in that the second expression won't treat the empty string ("") as Empty. These expressions aren't equivalent, but the latter might work for your purposes. You can’t use this method for the Guid data type.
6. Len. Power Apps delegates the Len function, but it might not work as you expect. A char(10) column with value "hello" will always have, in SQL Server, a length of 10. But Power Apps will treat that string as having a length of 5, which could cause discrepancies and confusion. You shouldn't use char/nchar on the server; use varchar/nvarchar instead.

Get rows (V2) action Aggregation transformation parameter detail

Only the following Aggregation transformations are supported:

• filter - Allows filtering input set. Supports the same expressions as $filter Example: filter(ColName1 eq 1)

• aggregate - Allows aggregation using one of following methods: average, max, min, sum, countdistinct Example: aggregate(ColName2 with max as MaxValue)

You can combine several operators separated by forward slashes to express that they are consecutively applied, i.e. the result of each transformation is the input to the next transformation. E.g. filter(ColName1 eq 4)/aggregate(ColName2 with sum as MaxWorkItemId)

Using formalParameters

Execute a SQL query [DEPRECATED] and Execute a SQL query (V2) actions allows usage of formal parameters. They are defined as key-value pairs, where key is parameter name and value is SQL data type (like INT or NVARCHAR(255)). Once name and data type is defined, a new input field for a value will be shown.

Connector in-depth

For more information about the connector, see the in-dept section.

Creating a connection

The connector supports the following authentication types:

Azure AD Integrated

Auth ID: oauth

Applicable: All regions except US Government (GCC)

Use Azure Active Directory to access your SQL database.

Azure AD Integrated (Azure Commercial)

Auth ID: oauthCom

Applicable: US Government (GCC) only

Use Azure Active Directory to access your SQL database in Azure Commercial.

Azure AD Integrated (Azure Government)

Auth ID: oauth

Applicable: US Government (GCC) only

Use Azure Active Directory to access your SQL database in Azure Government.

SQL Server Authentication

Auth ID: sqlAuthentication

Applicable: All regions

Use an account name and password to access your SQL database.

Windows Authentication

Auth ID: windowsAuthentication

Applicable: All regions

Use an on-premise Active Directory account name and password to access your SQL database.

Default [DEPRECATED]

Applicable: All regions

This option is only for older connections without an explicit authentication type, and is only provided for backward compatibility.

Throttling Limits

Actions

Delete row (V2)

Delete row [DEPRECATED]

Execute a SQL query (V2)

Execute a SQL query [DEPRECATED]

Execute stored procedure (V2)

Execute stored procedure [DEPRECATED]

Get row (V2)

Get row [DEPRECATED]

Get rows (V2)

Get rows [DEPRECATED]

Get tables (V2)

Get tables [DEPRECATED]

Insert row (V2)

Insert row [DEPRECATED]

Transform data using Power Query

Update row (V2)

Update row [DEPRECATED]

Triggers

When an item is created (V2)

When an item is created [DEPRECATED]

When an item is modified (V2)

When an item is modified [DEPRECATED]

Definitions

TablesList

Table