Calling REST APIs from SQL Server 2025: A Guide to sp_invoke_external_rest_endpoint

sp_invoke_external_rest_endpoint, introduced in SQL Server 2025, lets you call REST APIs over HTTPS directly from T-SQL. Pulling exchange rates, sending an alert to a webhook or pushing data to an Azure service no longer needs PowerShell, SSIS or a separate application. The feature is off by default and needs both a server setting and a database permission. For production use, plan permissions, secret storage and error handling from day one.

This guide walks through the feature step by step: enabling it, granting permissions, GET and POST examples, storing secrets safely, error behavior and monitoring. Our team tested the feature on SQL Server 2025 with exchange rates from the Central Bank of the Republic of Türkiye (CBRT) and weather data. Every script in this post was run on our test server.

Summary

What Is sp_invoke_external_rest_endpoint?

sys.sp_invoke_external_rest_endpoint is a system stored procedure that sends an HTTPS request to the URL you give it. The payload, HTTP headers and method are parameters. The service’s response comes back in an OUTPUT variable, together with the HTTP status code and response headers.

Typical uses:

• Pulling exchange rates or other reference data
• Sending failed Agent jobs or disk space alerts to a webhook or incident management system
• Pushing data to Azure Functions, Event Hubs or Logic Apps
• Generating embeddings with services such as Azure OpenAI
• Checking the state of an external system and writing the result to a table

In Azure SQL Database and Managed Instance, only domains on Microsoft’s allowlist can be called: Azure Functions, Logic Apps, Event Hubs, Azure OpenAI, Microsoft Graph, Blob Storage and others. For a service that isn’t on the list, Microsoft suggests exposing it through API Management. That list doesn’t apply to SQL Server 2025 on your own servers. There, the limits come from the server’s network access, DNS, proxy and firewall rules. Our test server reached the CBRT and a public weather service directly.

1. Enabling the Feature

The feature ships disabled in SQL Server 2025. A user with ALTER SETTINGS permission (sysadmin or serveradmin) turns it on:

EXEC sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE WITH OVERRIDE;

SELECT name, value_in_use
FROM sys.configurations
WHERE name = N'external rest endpoint enabled';

A value_in_use of 1 means the feature is on. This isn’t an advanced option, so you don’t need to enable show advanced options first, and no restart is needed.

2. Granting Permissions

Enabling the server setting doesn’t let everyone call APIs. The calling user needs the EXECUTE ANY EXTERNAL ENDPOINT permission in the database. Granting it to a role rather than to individual users keeps it manageable:

CREATE ROLE rest_caller;
GRANT EXECUTE ANY EXTERNAL ENDPOINT TO rest_caller;
ALTER ROLE rest_caller ADD MEMBER [ApiCallerUser];

A user without the permission gets this error:

This permission opens a channel through which data can leave the database. Don’t grant it to application users in bulk. Where possible, give it only to a dedicated account that runs the integration procedures, or to the Agent job owner, and record who called what and when with SQL Server Audit.

3. Syntax and Parameters

Note that the default method is POST. If you want to read data, write @method = N'GET' explicitly. The procedure adds content-type: application/json and accept: application/json headers itself. For a service that returns XML, change the Accept header to application/xml.

4. Reading Data with GET and Parsing the JSON Response

The example below pulls a three-day forecast for Istanbul from a weather service that needs no key. The arrays in the response are opened into rows with OPENJSON and joined on their position ([key]):

DECLARE @ret int, @response nvarchar(max);

EXEC @ret = sys.sp_invoke_external_rest_endpoint
     @url = N'https://api.open-meteo.com/v1/forecast?latitude=41.01&longitude=28.98&daily=temperature_2m_min,temperature_2m_max,precipitation_sum&forecast_days=3&timezone=Europe%2FIstanbul',
     @method = N'GET',
     @timeout = 30,
     @response = @response OUTPUT;

IF @ret <> 0
    THROW 50001, N'Weather service call failed.', 1;

SELECT JSON_VALUE(@response, '$.response.status.http.code') AS http_status,
       d.value  AS forecast_date,
       mn.value AS temp_min_c,
       mx.value AS temp_max_c,
       p.value  AS precipitation_mm
FROM OPENJSON(@response, '$.result.daily.time') AS d
JOIN OPENJSON(@response, '$.result.daily.temperature_2m_min') AS mn ON mn.[key] = d.[key]
JOIN OPENJSON(@response, '$.result.daily.temperature_2m_max') AS mx ON mx.[key] = d.[key]
JOIN OPENJSON(@response, '$.result.daily.precipitation_sum')  AS p  ON p.[key]  = d.[key]
ORDER BY CAST(d.[key] AS int);

The response has two parts. $.response carries the HTTP status code and response headers. The data the service returned is under $.result:

{
  "response": {
    "status": { "http": { "code": 200, "description": "OK" } },
    "headers": { "Content-Type": "application/json; charset=utf-8", "...": "..." }
  },
  "result": {
    "daily": {
      "time": ["2026-11-06", "2026-11-07", "2026-11-08"],
      "temperature_2m_min": [7.9, 6.9, 6.1],
      "temperature_2m_max": [12.4, 11.4, 11.2]
    }
  }
}

5. A Real Scenario: Loading Central Bank Exchange Rates into a Table

Daily exchange rates are one of the most common integration needs in ERP and reporting systems. The CBRT publishes its rates as XML. With the Accept header set to application/xml, the procedure returns the response as <output><response>…</response><result>…</result></output>, and the bank’s XML sits inside the result node.

First, the table that holds the rates:

CREATE TABLE dbo.ExchangeRate (
    RateDate        date          NOT NULL,
    CurrencyCode    char(3)       NOT NULL,
    Unit            int           NOT NULL,
    CurrencyName    nvarchar(100) NOT NULL,
    ForexBuying     decimal(18,6) NULL,
    ForexSelling    decimal(18,6) NULL,
    BanknoteBuying  decimal(18,6) NULL,
    BanknoteSelling decimal(18,6) NULL,
    LoadedAt        datetime2(0)  NOT NULL CONSTRAINT DF_ExchangeRate_LoadedAt DEFAULT SYSDATETIME(),
    CONSTRAINT PK_ExchangeRate PRIMARY KEY (RateDate, CurrencyCode)
);

The procedure fetches the rates, parses the XML and loads it with MERGE. If it runs more than once a day, it updates rows instead of duplicating them:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER PROCEDURE dbo.usp_LoadExchangeRates
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @ret int, @response nvarchar(max), @xml xml;

    EXEC @ret = sys.sp_invoke_external_rest_endpoint
         @url         = N'https://www.tcmb.gov.tr/kurlar/today.xml',
         @method      = N'GET',
         @headers     = N'{"Accept":"application/xml"}',
         @timeout     = 60,
         @retry_count = 3,
         @response    = @response OUTPUT;

    SET @xml = TRY_CAST(@response AS xml);

    IF @ret <> 0 OR @xml IS NULL
        THROW 50001, N'CBRT call failed or the response is not valid XML.', 1;

    WITH src AS (
        SELECT CONVERT(date, t.n.value('@Date', 'char(10)'), 101)         AS RateDate,
               c.n.value('@Kod', 'char(3)')                                AS CurrencyCode,
               c.n.value('(Unit/text())[1]', 'int')                        AS Unit,
               TRIM(c.n.value('(CurrencyName/text())[1]', 'nvarchar(100)')) AS CurrencyName,
               TRY_CONVERT(decimal(18,6), c.n.value('(ForexBuying/text())[1]', 'varchar(20)'))     AS ForexBuying,
               TRY_CONVERT(decimal(18,6), c.n.value('(ForexSelling/text())[1]', 'varchar(20)'))    AS ForexSelling,
               TRY_CONVERT(decimal(18,6), c.n.value('(BanknoteBuying/text())[1]', 'varchar(20)'))  AS BanknoteBuying,
               TRY_CONVERT(decimal(18,6), c.n.value('(BanknoteSelling/text())[1]', 'varchar(20)')) AS BanknoteSelling
        FROM @xml.nodes('/output/result/Tarih_Date') AS t(n)
        CROSS APPLY t.n.nodes('Currency') AS c(n)
    )
    MERGE dbo.ExchangeRate AS tgt
    USING src ON tgt.RateDate = src.RateDate AND tgt.CurrencyCode = src.CurrencyCode
    WHEN MATCHED THEN
        UPDATE SET Unit = src.Unit, CurrencyName = src.CurrencyName,
                   ForexBuying = src.ForexBuying, ForexSelling = src.ForexSelling,
                   BanknoteBuying = src.BanknoteBuying, BanknoteSelling = src.BanknoteSelling,
                   LoadedAt = SYSDATETIME()
    WHEN NOT MATCHED THEN
        INSERT (RateDate, CurrencyCode, Unit, CurrencyName, ForexBuying, ForexSelling, BanknoteBuying, BanknoteSelling)
        VALUES (src.RateDate, src.CurrencyCode, src.Unit, src.CurrencyName,
                src.ForexBuying, src.ForexSelling, src.BanknoteBuying, src.BanknoteSelling);
END;
GO

EXEC dbo.usp_LoadExchangeRates;

On our test server the procedure loaded 22 currencies, and a second run left the row count unchanged. A few details matter:

• The date comes from the XML’s Date attribute (MM/DD/YYYY). On weekends and holidays, today.xml returns the last business day’s bulletin; the primary key stops the same bulletin from being written twice.
• Some items, such as the SDR (XDR), have no banknote buying and selling values. That’s why values go through TRY_CONVERT and the columns allow NULL.
• Some currency names have trailing spaces; TRIM removes them.

Scheduling with an Agent job

The CBRT publishes rates at 15:30 Türkiye time on business days; the Last-Modified header in our test call confirmed it. The job below runs the procedure at 15:45 on weekdays and retries the step twice on a transient error:

USE msdb;
GO
EXEC dbo.sp_add_job @job_name = N'Load CBRT Exchange Rates', @owner_login_name = N'sa';

EXEC dbo.sp_add_jobstep @job_name = N'Load CBRT Exchange Rates', @step_name = N'Load rates',
     @subsystem = N'TSQL', @database_name = N'YourDatabase',
     @command = N'EXEC dbo.usp_LoadExchangeRates;',
     @retry_attempts = 2, @retry_interval = 10;

EXEC dbo.sp_add_jobschedule @job_name = N'Load CBRT Exchange Rates', @name = N'Weekdays 15:45',
     @freq_type = 8, @freq_interval = 62, @freq_recurrence_factor = 1,
     @active_start_time = 154500;

EXEC dbo.sp_add_jobserver @job_name = N'Load CBRT Exchange Rates';

@freq_interval = 62 is the bit sum of Monday through Friday (2+4+8+16+32). For job owners, notifications and history settings, see our SQL Server Agent best practices.

6. Storing API Keys Safely: DATABASE SCOPED CREDENTIAL

Writing an API key or token as plain text in @headers exposes it in the procedure definition, the plan cache and source control. The right way is to put the key in a DATABASE SCOPED CREDENTIAL, which is stored encrypted. Creating a credential needs a database master key; without one you get:

-- Create the database master key once, if it doesn't exist
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [https://alerts.example.com/api]
WITH IDENTITY = 'HTTPEndpointHeaders',
     SECRET   = '{"x-api-key":"<API_KEY>"}';

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[https://alerts.example.com/api] TO rest_caller;

The call passes only the credential name, and the procedure adds the key to the request itself. In our test, a service that echoes request headers confirmed that the x-api-key header was added.

IDENTITY takes four values:

The credential name has rules too: it must be a valid HTTPS URL, contain no query string and match the protocol and domain of the called URL. It must also point to a path that is more generic than the called URL. A credential created for https://alerts.example.com/api works for a call to https://alerts.example.com/api/sql-events. The reverse, a credential for a more specific path used on a more generic URL, fails:

7. Sending Data with POST

One of the most useful DBA scenarios is sending alert data to a webhook or incident system. The example below gathers the Agent jobs that failed in the last 24 hours into one JSON document and sends it with a credential:

DECLARE @payload nvarchar(max) = (
    SELECT @@SERVERNAME AS server_name,
           j.name       AS job_name,
           msdb.dbo.agent_datetime(h.run_date, h.run_time) AS run_at,
           h.message
    FROM msdb.dbo.sysjobhistory AS h
    JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
    WHERE h.step_id = 0
      AND h.run_status = 0
      AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= DATEADD(hour, -24, GETDATE())
    FOR JSON PATH, ROOT('failed_jobs')
);

IF @payload IS NULL
    RETURN;  -- no failed jobs, no call

DECLARE @ret int, @response nvarchar(max);

EXEC @ret = sys.sp_invoke_external_rest_endpoint
     @url        = N'https://alerts.example.com/api/sql-events',
     @method     = N'POST',
     @payload    = @payload,
     @credential = [https://alerts.example.com/api],
     @timeout    = 30,
     @retry_count = 2,
     @response   = @response OUTPUT;

SELECT @ret AS return_code,
       JSON_VALUE(@response, '$.response.status.http.code') AS http_status;

Gathering rows into one document with FOR JSON, instead of sending them one by one, avoids opening an HTTPS connection per row; Microsoft recommends batching too. FOR JSON returns NULL for an empty result set, so the check stops an empty request.

8. Return Codes and Error Behavior

The procedure’s return value isn’t the same as the HTTP status code. For 2xx responses the return value is 0. For any other HTTP response, the return value is the HTTP status code itself. If the service can’t be reached at all, the procedure raises an error. We tried each case on our test server:

So production code needs two separate checks: whether the return value is 0, and a TRY...CATCH for connection and timeout errors:

DECLARE @ret int, @response nvarchar(max);

BEGIN TRY
    EXEC @ret = sys.sp_invoke_external_rest_endpoint
         @url         = N'https://alerts.example.com/api/health',
         @method      = N'GET',
         @timeout     = 60,
         @retry_count = 3,
         @response    = @response OUTPUT;

    IF @ret <> 0
        -- The service answered, but with a non-2xx code
        PRINT CONCAT(N'HTTP error: ', @ret, N' ', LEFT(@response, 500));
END TRY
BEGIN CATCH
    -- The service couldn't be reached: DNS, TLS, timeout, permission
    PRINT CONCAT(N'Call failed. Msg ', ERROR_NUMBER(), N': ', ERROR_MESSAGE());
END CATCH;

In a real integration, write the result to a log table instead of using PRINT, and end with THROW on failure so the job fails and Agent notifications fire.

Timeouts and retries

With @retry_count set, the procedure retries on 408, 429, 500, 502, 503 and 504 responses. If the service sends a Retry-After header, the wait follows it. If not, exponential backoff applies to those codes; in all other cases the default delay is 200 milliseconds. One important detail: with retries on, @timeout is the total for the whole call, not per attempt. If you plan three attempts, raise the timeout accordingly.

9. Limits

10. Monitoring and Performance

While it waits for the external service, the procedure reports the HTTP_EXTERNAL_CONNECTION wait type, and the session running it holds a worker thread. A slow API combined with many parallel calls can strain the worker pool and the concurrent connection limit. Watch the current and cumulative picture with these queries:

-- Requests waiting for an external service right now
SELECT r.session_id, r.wait_type, r.wait_time AS wait_time_ms, t.text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.wait_type = N'HTTP_EXTERNAL_CONNECTION';

-- Cumulative waits since the service started
SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = N'HTTP_EXTERNAL_CONNECTION';

We explain how we read wait statistics in general in our wait statistics workflow.

When to Use It, and When Not To

This feature doesn’t replace the application layer or an integration platform. For simple, controlled integrations, it removes the need for a middle tier and cuts the maintenance load.

Production Security Checklist

• Enable the feature only on servers that will actually use it.
• Grant EXECUTE ANY EXTERNAL ENDPOINT to a role and assign the role only to integration accounts.
• Keep API keys in a DATABASE SCOPED CREDENTIAL, never as plain text in procedure code or @headers.
• Limit the server’s outbound access in the firewall to the domains it needs.
• Minimize the data you send out, and assess personal data under the applicable data protection law.
• Don’t write responses from external services into critical tables without validating them.
• Record calls with audit and a log table, and watch the HTTP_EXTERNAL_CONNECTION wait.

For general server hardening, see our SQL Server security hardening guide. We show how this procedure generates embeddings with Azure OpenAI in our vector search in Azure SQL post.

SQL Server 2025 with Aryasoft

A move to SQL Server 2025 delivers real value when it’s planned together with the safe use of its new features. Aryasoft handles the upgrade plan, the permission and network design for new features such as sp_invoke_external_rest_endpoint, and monitoring after go-live. We cover the upgrade side in detail in our SQL Server 2025 upgrade guide.

Frequently Asked Questions

Sources

The technical details in this guide were checked against the following official documentation in November 2026. The scripts were run on our SQL Server 2025 (17.0.4055.5) test server.

1. Microsoft Learn: sys.sp_invoke_external_rest_endpoint
2. Microsoft Learn: CREATE DATABASE SCOPED CREDENTIAL
3. Microsoft Learn: OPENJSON
4. Central Bank of the Republic of Türkiye: indicative exchange rates (XML)