Calling REST endpoints from the database

When the nightly tier recalculation from lesson 4 moves a member up to Kestrel, marketing wants the member to get a congratulations message in the app the next morning. The message service is an Azure Function, fn-finch-notify, that the app team already runs. Hana's first idea was a scheduled job in the API that polls the database for tier changes. Elena showed her two other ways: the database can call the function's REST endpoint directly, or the database's changes can be streamed out to other services as events. In the eleventh session they tried both, and added the third piece DP-800 covers in this area, Data API builder, which turns database objects into REST and GraphQL endpoints without hand-written API code.

sp_invoke_external_rest_endpoint

The system stored procedure sp_invoke_external_rest_endpoint sends an HTTPS request from the database and returns the response. It is enabled by default in Azure SQL Database and SQL database in Fabric. In SQL Server 2025 and in Managed Instance with the SQL Server 2025 or Always-up-to-date update policy it is disabled by default, and an administrator turns it on with sp_configure 'external rest endpoint enabled'. The caller needs the EXECUTE ANY EXTERNAL ENDPOINT database permission, and REFERENCES on any database scoped credential the call uses.

DECLARE @ret int, @response nvarchar(max);
DECLARE @payload nvarchar(max) =
    JSON_OBJECT('memberId': CAST(@PublicId AS nvarchar(36)),
                'newTier': 'K',
                'effective': CONVERT(char(10), SYSUTCDATETIME(), 23));

EXECUTE @ret = sp_invoke_external_rest_endpoint
    @url = N'https://fn-finch-notify-cc.azurewebsites.net/api/tier-changed',
    @method = 'POST',
    @payload = @payload,
    @credential = [https://fn-finch-notify-cc.azurewebsites.net/api/tier-changed],
    @timeout = 10,
    @retry_count = 2,
    @response = @response OUTPUT;

SELECT @ret AS ReturnCode,
       JSON_VALUE(@response, '$.response.status.http.code') AS HttpStatus,
       JSON_VALUE(@response, '$.result.messageId') AS MessageId;

The parameters follow the shape of an HTTP request. @url must be HTTPS; plain HTTP is refused and redirects are not followed. @method defaults to POST and accepts GET, POST, PUT, PATCH, DELETE and HEAD. @headers takes a flat JSON object, @payload the request body, and @timeout a number of seconds from 1 to 230, 30 by default. @retry_count, from 0 to 10, retries on status codes such as 429 and 503, honouring a Retry-After header when the service sends one. The return value is 0 for a 2xx status, otherwise the status code received, and an error is raised if the call cannot be made at all.

The @response output is a JSON document with two parts. The response part holds the HTTP status and the response headers, and the result part holds the body the service returned, omitted for a 204 No Content. Because it is JSON, the functions from lesson 2 read it: JSON_VALUE for single values and OPENJSON for arrays. Payloads can be up to 100 MB in each direction, URLs up to 8 KB and headers up to 8 KB in total.

Credentials and the allowlist

Authentication goes in a database scoped credential, not in the T-SQL that makes the call. The credential's name must be a URL, with no query string, that matches the protocol and host of the call and is the same as, or a prefix of, its path. Its IDENTITY says how the secret is sent. HTTPEndpointHeaders sends key-value pairs as request headers, which suits an Azure Functions key. HTTPEndpointQueryString sends them in the query string. Managed Identity sends a token for the database's managed identity, which is the best choice whenever the target service accepts Entra tokens, because there is no secret to store or rotate. Shared Access Signature suits storage.

-- Preferred: the logical server's managed identity, no secret to store
CREATE DATABASE SCOPED CREDENTIAL [https://fn-finch-notify-cc.azurewebsites.net/api/tier-changed]
WITH IDENTITY = 'Managed Identity',
     SECRET = '{"resourceid": "api://fn-finch-notify"}';

GRANT EXECUTE ANY EXTERNAL ENDPOINT TO [app-finchrewards-jobs];
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[https://fn-finch-notify-cc.azurewebsites.net/api/tier-changed]
    TO [app-finchrewards-jobs];

Database scoped credentials need a database master key to protect their secrets, created once with CREATE MASTER KEY. In Azure SQL Database and Managed Instance, calls are limited to an allowlist of domains: Azure Functions and App Service, Logic Apps, Event Hubs and Service Bus, storage, Azure OpenAI, Key Vault, API Management, Microsoft Graph, Azure AI Search, Dynamics and several more. A service outside the list, such as a shipping company's address API, can be reached by putting Azure API Management in front of it. Outbound firewall rules on the logical server can narrow it further to exactly the hosts a database may call, which Sunil required for production.

When not to call out

Calling a web service from T-SQL is convenient and easy to misuse. The call waits, and while it waits the session holds whatever locks it has. A procedure that updates a member and then calls a slow service before committing keeps that member row locked for the duration, up to the timeout. Elena's rule is that a REST call never happens inside a transaction that holds locks other work needs. The tier procedure commits the tier changes first, writes each change to an outbox table in the same transaction, and a separate step reads the outbox and calls the function, marking each row as sent. If the function is down, the outbox keeps the messages and the next run sends them.

Concurrency is limited too. External calls can use at most 15 in 100 of the database's worker threads, and never more than 175, and beyond that the procedure fails with error 10928, or 10936 for an elastic pool. A per-row loop that calls a service for each of 412,000 members would be slow and would hit that limit. The outbox step sends changes in batches, about 500 per call, because fn-finch-notify accepts a JSON array.

-- Send unsent tier changes in batches of 500 from the outbox
DECLARE @batch nvarchar(max), @ret int, @response nvarchar(max);
SELECT @batch = (SELECT TOP (500) OutboxId, MemberPublicId, NewTier
                 FROM rewards.TierOutbox
                 WHERE SentAtUtc IS NULL
                 ORDER BY OutboxId
                 FOR JSON PATH);
IF @batch IS NOT NULL
BEGIN
    EXECUTE @ret = sp_invoke_external_rest_endpoint
        @url = N'https://fn-finch-notify-cc.azurewebsites.net/api/tier-changed-batch',
        @payload = @batch,
        @credential = [https://fn-finch-notify-cc.azurewebsites.net/api/tier-changed],
        @timeout = 30, @retry_count = 3, @response = @response OUTPUT;
    IF @ret = 0
        UPDATE o SET SentAtUtc = SYSUTCDATETIME()
        FROM rewards.TierOutbox AS o
        JOIN OPENJSON(@batch) WITH (OutboxId bigint) AS b ON b.OutboxId = o.OutboxId;
END;

Streaming changes instead

The outbox works, but it is a pattern the team must build and maintain. Change event streaming, CES, does the same job inside the engine: it publishes inserts, updates and deletes on chosen tables as CloudEvents, in JSON or Avro, to Azure Event Hubs or Fabric Eventstream, close to real time, without triggers or polling. Other services subscribe to the events. At the time of writing CES is a preview feature in SQL Server 2025, Azure SQL Database and Managed Instance, so Elena kept the outbox for production and set up a CES test against the development database to compare.

The DP-800 outline lists the other ways to react to changes, and each fits a different need. Change data capture, CDC, reads the transaction log and writes every change to change tables that consumers query; it keeps the old and new values and suits data integration. Change tracking records only which rows changed and a version number, not the values; it suits synchronizing a cache or a mobile app that will fetch the current row anyway. The Azure Functions SQL trigger binding runs a function when rows in a table change, built on change tracking. Logic Apps can poll for changes and run a workflow with little code.

Data API builder

Data API builder, DAB, is an open-source tool from Microsoft that serves REST and GraphQL endpoints for database objects described in a JSON configuration file. Each entity in the file maps a name to a table, view or stored procedure, and lists which roles may read, create, update, delete or execute it. DAB handles pagination, filtering, sorting and selecting fields from query string parameters for REST and from arguments for GraphQL, can cache responses, and supports relationships between entities so a GraphQL query can fetch a member with their recent transactions. It runs as a container, in Azure Container Apps or App Service, or locally for development, and authenticates callers with Entra ID or another provider. Recent versions can also host an MCP server, which is how lesson 10's Copilot tools reach the development database.

{
  "data-source": { "database-type": "mssql",
                   "connection-string": "@env('FINCHREWARDS_SQL')" },
  "runtime": { "rest": { "path": "/api" }, "graphql": { "path": "/graphql" } },
  "entities": {
    "Offer": {
      "source": { "object": "rewards.vActiveOffers", "type": "view", "key-fields": ["OfferId"] },
      "permissions": [ { "role": "authenticated", "actions": ["read"] } ],
      "cache": { "enabled": true, "ttl-seconds": 60 }
    },
    "PostPurchase": {
      "source": { "object": "rewards.uspPostPurchase", "type": "stored-procedure" },
      "permissions": [ { "role": "store-system", "actions": ["execute"] } ]
    }
  }
}

For Finch Rewards, DAB replaced about a third of the hand-written API: the read-only endpoints for offers, stores and the member card come straight from views, and the write endpoints call the procedures from lessons 4 and 5, so the rules stay in the database. The connection string is read from an environment variable that holds no password, because DAB connects with its own managed identity. The remaining API code handles sign-in, the app's business flows and anything that combines several services.