SQL Server Connector

Analogy categoryAnalogies

Connecting to Azure SQL and On-Premises SQL Server

The SQL Server connector lets Power Apps read and write directly to Azure SQL Database or a traditional on-premises SQL Server instance, which matters for organizations with existing line-of-business databases they don't want to migrate into Dataverse. For Azure SQL, the connection is direct over the internet using SQL authentication or Azure AD authentication; for an on-premises SQL Server, you must install and configure an On-Premises Data Gateway on a machine inside the corporate network, which securely relays queries between the Power Platform cloud service and the internal database without requiring the database itself to be exposed to the internet.

🏏

Cricket analogy: Connecting to an on-premises database through a gateway is like an overseas cricket board needing an accredited local liaison officer to relay communications with a touring team, rather than the touring board contacting the host ground directly.

sql

-- A stored procedure exposed to Power Apps for creating an order and returning its new ID
CREATE PROCEDURE dbo.usp_CreateOrder
    @CustomerId INT,
    @ProductId INT,
    @Quantity INT
AS
BEGIN
    INSERT INTO dbo.Orders (CustomerId, ProductId, Quantity, OrderDate)
    VALUES (@CustomerId, @ProductId, @Quantity, GETDATE());

    SELECT SCOPE_IDENTITY() AS NewOrderId;
END

Using Stored Procedures and Views

Once connected, Power Apps exposes eligible tables, views, and stored procedures as callable data sources: a view like vw_ActiveCustomers behaves much like a read-only table in a gallery, while a stored procedure such as usp_CreateOrder appears as a function you call directly by name with parameters, for example usp_CreateOrder.Run(comboCustomer.Selected.ID, comboProduct.Selected.ID, Value(txtQty.Text)), which is often the cleanest way to encapsulate complex business logic (multi-table inserts, transaction handling, computed values) on the database side instead of replicating that logic across several Power Fx formulas.

🏏

Cricket analogy: Calling a stored procedure with parameters is like a captain calling a specific, pre-agreed fielding plan by name (e.g. 'Plan B for the death overs') rather than re-explaining every fielder's position from scratch each time.

Stored procedures called from Power Apps must return a result set (even a trivial SELECT like the SCOPE_IDENTITY() example) if you want to capture output; procedures that only perform DML with no SELECT still work for writes but return no usable value in the formula.

Delegation Behavior with SQL Server

The SQL Server connector supports solid delegation for standard Filter, Sort, and simple comparison operators translated into SQL WHERE and ORDER BY clauses, but delegation breaks down for more complex expressions: functions like Concatenate applied across records, certain nested Filter/LookUp combinations, and any comparison against a client-side collection or variable (rather than a literal or a simple control property) typically cannot be delegated and fall back to retrieving only the first 2,000 rows locally. Because SQL Server tables can be enormous compared to typical SharePoint lists, developers should test delegation warnings carefully against a realistically sized table rather than a small development copy, since a formula that appears to work correctly with 50 test rows can silently return incomplete results once deployed against a table with 500,000 rows.

🏏

Cricket analogy: Testing delegation against a small dev table only is like practicing a run-chase strategy against club bowlers and assuming it'll work identically against genuine international pace in a World Cup final.

Comparing a SQL-backed table against a locally held Collection or a global variable (rather than a control property or literal) is one of the most common silent delegation breaks — always check the formula bar for the blue delegation warning triangle after adding such comparisons, and consider moving the comparison into a stored procedure or view if it must scale.

The SQL Server connector supports both Azure SQL (direct connection) and on-premises SQL Server (via an On-Premises Data Gateway).
Tables, views, and stored procedures all become usable data sources or callable functions in Power Fx.
Stored procedures are called like functions, e.g. usp_CreateOrder.Run(param1, param2), and are ideal for encapsulating multi-step business logic.
Views behave like read-only tables and are useful for pre-filtering or joining data before it reaches Power Apps.
Delegation covers standard Filter/Sort translated to SQL WHERE/ORDER BY, but breaks for complex expressions or comparisons against local collections/variables.
Always test delegation against realistically sized tables, since small development tables can hide silent truncation bugs.