A database can call an approved HTTPS endpoint without sending the request through a separate client. SQL Server 2025 adds sp_invoke_external_rest_endpoint, but that convenience needs clear limits on access, timing, and data transfer.
Start With the Version and Operating Boundary
These examples target SQL Server 2025. The procedure is disabled by default on that platform and requires an administrator to enable the server capability. Inspect the engine build and current setting before proposing the change. Hosted database offerings have their own enablement and endpoint restrictions; do not assume their rules are identical to this server deployment.
SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion;
SELECT name,value,value_in_use
FROM sys.configurations
WHERE name=N'external rest endpoint enabled';All URLs below are deliberate placeholders using an invalid domain. Replace them only with an approved endpoint in an isolated test. Review outbound network access, certificate trust, destination authentication, and the fields permitted to leave the database. I settle the payload contract before granting endpoint execution, because authorization to read a table is not automatically authorization to export its contents.
Keep request volume bounded. The destination can be unavailable, slow, or subject to rate limits. A database worker waiting for an external response is still part of the database workload. Decide whether a synchronous database call is appropriate or whether an application-owned queue provides a better failure boundary.
Enable sp_invoke_external_rest_endpoint and Limit Execution
The following administrative change requires approved server-setting permissions. The database grant requires an existing reviewed principal named EndpointCaller. Grant the capability to the specific workload identity rather than every database user. Until the server setting is on, every call below stops with error 31643.
EXEC sys.sp_configure N'external rest endpoint enabled',1;
RECONFIGURE;
GRANT EXECUTE ANY EXTERNAL ENDPOINT TO EndpointCaller;Endpoint execution is a broad capability, so pair it with controlled module access and a reviewed deployment process. Avoid accepting an arbitrary destination URL directly from untrusted application input. A wrapper can restrict the chosen destination and payload shape, but its permissions and signing context must be reviewed too. The grant should not become a general-purpose data delivery service by accident.
Record the prior configuration and how to revoke the workload's access if the integration is retired. Confirm the effective behavior under the actual caller rather than an administrator's session. A successful privileged test cannot establish that a least-privilege scheduled call will work.
Make a Bounded GET Request With sp_invoke_external_rest_endpoint
Supply an explicit method, timeout, and JSON acceptance header. Inspect both the procedure's return value and response. A transport failure throws an exception; a completed HTTP request can still report a non-success status. The example displays its diagnostic response only in a test session.
DECLARE @ReturnCode int,@Response nvarchar(max);
EXEC @ReturnCode=sys.sp_invoke_external_rest_endpoint
@url=N'https://api.example.invalid/v1/status',
@method=N'GET',@headers=N'{"Accept":"application/json"}',
@timeout=10,@retry_count=0,@response=@Response OUTPUT;
SELECT @ReturnCode AS ReturnCode,
JSON_VALUE(@Response,'$.response.status.http.code') AS HttpStatus,
@Response AS ResponseDocument;A zero return code corresponds to an HTTP success response. It does not prove that the returned business result is acceptable. An endpoint can return a successful HTTP status with an application-level rejection or an unexpected schema. Validate the contract separately and preserve the correlation identifier needed to investigate the remote operation.
Send a POST and Read the JSON Result
Construct a payload from explicitly selected fields. The sample uses synthetic identifiers and an operation key so the endpoint contract can recognize a repeated request. It assumes the approved endpoint returns a JSON result containing operationId and state.
DECLARE @Payload nvarchar(max),@Response nvarchar(max),@ReturnCode int;
SET @Payload=(SELECT N'Operation-001' AS operationId,
101 AS customerId,N'Preview' AS action
FOR JSON PATH,WITHOUT_ARRAY_WRAPPER);
EXEC @ReturnCode=sys.sp_invoke_external_rest_endpoint
@url=N'https://api.example.invalid/v1/operations',
@method=N'POST',@payload=@Payload,
@headers=N'{"Content-Type":"application/json","Accept":"application/json"}',
@timeout=10,@retry_count=0,@response=@Response OUTPUT;
IF @ReturnCode<>0
THROW 50000,'The endpoint returned a non-success HTTP status.',1;
IF ISJSON(@Response)<>1
THROW 50000,'The endpoint did not return the expected JSON envelope.',1;
SELECT operationId,state
FROM OPENJSON(@Response,'$.result')
WITH(operationId nvarchar(100) '$.operationId',state nvarchar(30) '$.state');Validate required fields and allowed states before changing local data. The response envelope separates HTTP metadata from the endpoint result. A no-content success can have no result to parse, which must be an accepted contract case or a rejected response. Do not substitute an empty object and pretend the operation returned the expected values.
For sp_invoke_external_rest_endpoint, successful parsing is only a structural check. Compare the operation identifier with the submitted request and apply the endpoint's documented business rules. I keep those checks visible in the calling module so a future schema change fails clearly rather than quietly producing NULL values.
Store Authentication in a Scoped Credential
Use a database-scoped credential for the approved authentication header. Its name must match the destination according to the credential's endpoint-matching rules. The following secret is an obvious placeholder; deploy the real value through the protected secret-handling process. Ensure the database has its required encryption-key setup beforehand.
CREATE DATABASE SCOPED CREDENTIAL [https://api.example.invalid/v1/]
WITH IDENTITY=N'HTTPEndpointHeaders',
SECRET=N'{"Authorization":"Bearer REPLACE_WITH_PROTECTED_TOKEN"}';
DECLARE @Response nvarchar(max),@ReturnCode int;
EXEC @ReturnCode=sys.sp_invoke_external_rest_endpoint
@url=N'https://api.example.invalid/v1/status',@method=N'GET',
@credential=N'https://api.example.invalid/v1/',
@timeout=10,@response=@Response OUTPUT;
SELECT @ReturnCode AS ReturnCode;Restrict credential administration and grant only the required access for the chosen execution design. Rotate the secret and test the rotated credential before its predecessor expires. Avoid logging request headers, complete authentication material, or sensitive response bodies in an ordinary job log. A diagnostic record should explain the failure without becoming another place that stores the secret.
Design sp_invoke_external_rest_endpoint Retries Around Side Effects
A timeout can occur after the destination accepted a POST. Repeating the request without a remote idempotency contract can create a duplicate action. Use a stable operation key, bounded retry policy, and a way to query the remote outcome. A local transaction rollback does not reverse a completed external action.
Which system owns reconciliation when the response never arrives? Define that responsibility before enabling retries. Avoid holding a long local transaction open while waiting for an external service. An outbox-style handoff can commit local intent first and perform the external action through a separately monitored worker when that better matches the workload.
Observe the Integration as a Service
Track request duration, status, accepted business outcome, timeout, retry count, and correlation identifier with appropriate redaction. Test authorization failure, malformed JSON, missing fields, endpoint downtime, and duplicate submissions. Keep workload concurrency within an agreed limit and confirm that application latency remains acceptable.
Separate deployment readiness from ongoing service health. A working initial request does not verify future token rotation, changed certificates, altered response schemas, or destination maintenance. Assign an owner for each of those changes and keep a small synthetic health check that contains no customer information. Set an escalation rule for repeated failures so callers do not continue building an unexplained backlog while the database job remains enabled.
The useful role of sp_invoke_external_rest_endpoint is a controlled integration point with a documented contract. Enable it for a justified operation, then verify access, failure behavior, and reconciliation. The endpoint does not become local merely because the request started in a query window.
Related reading on this blog: Loading Data From an API Into SQL Server and Validating JSON Parameters Before a Procedure Uses Them.
An external endpoint call is not a local database operation, it is a distributed action with its own failure and recovery contract.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
