SQL Server + APIs: a complete guide for DBAs and data engineers

APIs are how modern systems talk to each other. As a DBA or data engineer, you’ll run into two common needs:

Consume an external REST API from SQL Server to enrich or validate data.
Expose your SQL Server data as an API for apps, services, and partners.

This guide shows you both. We’ll cover JSON basics in T-SQL, version-specific options like sp_invoke_external_rest_endpoint, safe fallbacks (CLR, PowerShell, SSIS), and how to front SQL Server with a proper Web API and an API gateway. It ends with a hands-on workshop that takes you from beginner to confident practitioner.

What “API with SQL Server” actually means

Consuming an API from T-SQL: call a REST endpoint and parse the JSON result into tables. In SQL Server 2016+ you can parse and emit JSON natively with OPENJSON, JSON_VALUE, JSON_QUERY, JSON_MODIFY, and FOR JSON Microsoft Learn+2Microsoft Learn+2.
Exposing an API for your data: you don’t make SQL Server itself “host” HTTP endpoints. The standard pattern is to build an ASP.NET Core Web API that talks to SQL Server through parameterized queries or an ORM like EF Core; then put that API behind Azure API Management for authentication, rate limits, and observability Microsoft Learn+2Microsoft Learn+2.

Version check: where `sp_invoke_external_rest_endpoint` fits

Microsoft provides a built-in proc to call HTTPS endpoints:

sp_invoke_external_rest_endpoint is built-in for Azure SQL and Managed Instance and is rolling through releases; by mid-2025 it’s GA on Azure SQL Managed Instance, and it’s in preview for SQL Server 2025 (17.x) on-prem at the time of writing. If you’re on SQL Server 2019/2022 on-prem, you won’t have it and should use the fallbacks below Microsoft Learn+1.

Why this matters: if you have the proc, calling REST from T-SQL is simple, secure, and supported. If not, you’ll rely on SQLCLR or Agent PowerShell with clear guardrails.

JSON in T-SQL: the essentials you’ll actually use

Format rows as JSON for your API responses: SELECT ... FOR JSON [PATH|AUTO] Microsoft Learn
Validate JSON: ISJSON()
Extract scalars: JSON_VALUE()
Extract objects/arrays: JSON_QUERY()
Update JSON text: JSON_MODIFY() Microsoft Learn
Shred JSON to rows: OPENJSON with or without a WITH schema for typed columns Microsoft Learn

These functions ship in SQL Server 2016 and later, with OPENJSON requiring database compatibility level 130+ Microsoft Learn.

Pattern A: consume a REST API from SQL Server

A1) If you have `sp_invoke_external_rest_endpoint` (Azure SQL / MI, or SQL Server 2025 preview)

DECLARE @url    nvarchar(4000) = N'https://api.github.com/repos/dotnet/runtime';
DECLARE @header nvarchar(4000) = N'{"User-Agent":"TSQL-Client"}'; -- GitHub requires it

DECLARE @resp table (
  status   int,
  headers  nvarchar(max),
  content  nvarchar(max)
);

INSERT @resp (status, headers, content)
EXEC sys.sp_invoke_external_rest_endpoint
  @url    = @url,
  @method = N'GET',
  @headers = @header;

SELECT status, headers, content FROM @resp;

Shred the JSON:

-- Extract a few fields into a table
SELECT
  JSON_VALUE(content, '$.full_name')           AS repo_full_name,
  JSON_VALUE(content, '$.stargazers_count')    AS stars,
  JSON_VALUE(content, '$.forks_count')         AS forks,
  JSON_VALUE(content, '$.open_issues_count')   AS open_issues
FROM @resp
WHERE status = 200;

The proc handles TLS and returns status, headers, and body. Use it with POST and JSON payloads by adding @body and @content_type='application/json' Microsoft Learn.

Security notes

Prefer Managed Identity on Azure or store secrets in Azure Key Vault.
Use SCOPE_IDENTITY() or explicit IDs to keep idempotency when your T-SQL logic posts to external systems and might retry.

A2) If you don’t have the proc (SQL Server 2019/2022 on-prem)

Pick one of these:

Option 1: SQLCLR
Create a signed CLR proc that uses HttpClient and register it with CREATE ASSEMBLY. Use CLR strict security, sign assemblies, and avoid UNSAFE unless there’s no alternative. This is powerful and fast, but requires careful security review Microsoft Learn+2Microsoft Learn+2.

Option 2: SQL Agent PowerShell step
Schedule or trigger a PowerShell job that calls Invoke-RestMethod and writes results into a staging table. This keeps HTTP work out of the engine and is easier to audit and patch.

Option 3: SSIS
Use a Script Task or HTTP connector in a package, land JSON to a file or table, then OPENJSON into your schema.

Tip: even if you can call the web from T-SQL, think carefully about blast radius. Keeping HTTP calls at the edge (PowerShell job or app tier) often reduces risk and makes retries and circuit breaking easier to manage.

Pattern B: expose SQL Server data as a REST API

SQL Server is not a web server. The standard architecture:

ASP.NET Core Web API (Minimal APIs or Controllers)
Data access with parameterized queries, Dapper, or EF Core
Put it behind Azure API Management for policy, security, dev portal, and analytics Microsoft Learn+2Microsoft Learn+2

Example minimal API (C#) for a Sales table:

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddSqlServer<DbContext>("Server=tcp:...;Database=AppDb;..."); // or Dapper SqlConnection
var app = builder.Build();

app.MapGet("/api/sales/{id:int}", async (int id, DbContext db) =>
{
    var sale = await db.Set<Sale>()
                       .AsNoTracking()
                       .FirstOrDefaultAsync(s => s.Id == id);
    return sale is null ? Results.NotFound() : Results.Ok(sale);
});

app.MapGet("/api/sales", async (int page = 1, int size = 50, DbContext db) =>
{
    page = Math.Max(1, page); size = Math.Clamp(size, 1, 500);
    var data = await db.Set<Sale>()
                       .AsNoTracking()
                       .OrderBy(s => s.Id)
                       .Skip((page - 1) * size)
                       .Take(size)
                       .ToListAsync();
    return Results.Ok(data);
});

app.Run();

EF Core and ASP.NET Core tutorials walk you through setup, migrations, seeding, and connection resiliency Microsoft Learn. Use API Management to add OAuth2/JWT, per-caller rate limits, request/response validation, and header-based caching without changing code Microsoft Learn+1.

To return JSON shaped by SQL Server, you can keep the business logic in T-SQL and emit JSON using FOR JSON PATH:

CREATE OR ALTER PROCEDURE dbo.Sales_GetPage
  @Page int = 1,
  @Size int = 50
AS
BEGIN
  SET NOCOUNT ON;
  WITH Paged AS (
    SELECT s.Id, s.OrderNo, s.CustomerId, s.Total, s.CreatedUtc
    FROM dbo.Sales AS s
    ORDER BY s.Id
    OFFSET (@Page-1)*@Size ROWS FETCH NEXT @Size ROWS ONLY
  )
  SELECT (SELECT * FROM Paged FOR JSON PATH, INCLUDE_NULL_VALUES) AS ResultJson;
END

Your API action returns that JSON verbatim. This keeps pagination and shape consistent across clients Microsoft Learn.

Best practices you’ll be glad you followed

Security

Never embed secrets in code or T-SQL. Use Managed Identity, Key Vault, or encrypted credentials.
Lock down SQLCLR: enable clr strict security, sign assemblies, and avoid TRUSTWORTHY ON shortcuts Microsoft Learn+1.
In the API, require OAuth2/JWT and validate scopes per route. Use API Management policies to enforce it centrally Microsoft Learn+1.

Reliability

Implement timeouts, retries with backoff, and idempotency keys when posting from SQL to external APIs.
For bulk sync, use queues or staging tables instead of calling the API in a tight loop from triggers.

Performance

Parse only what you need from JSON. Use OPENJSON ... WITH (...) to strongly type columns and avoid over-shredding Microsoft Learn.
Don’t overfetch in your API. Add pagination consistently and consider ETags for caching.
Keep HTTP calls out of user transactions when possible.

Observability

Log request ID, status, latency, and payload size.
In API Management, turn on per-API analytics and alerts for 4xx/5xx spikes Microsoft Learn.

Data shaping

For outward APIs, let SQL Server produce JSON via FOR JSON when it simplifies the app code, or shape in C# if you need complex transformations Microsoft Learn.

Step-by-step workshop: from zero to “API-ready” SQL

Part 0 — Setup

Database compatibility level 130 or higher so OPENJSON works (SQL 2016+) Microsoft Learn.
If you plan to call APIs from T-SQL:
- Azure SQL / MI: verify sp_invoke_external_rest_endpoint is available in your environment. If yes, proceed with Part 2A. If not, choose CLR or Agent PowerShell (Part 2B) Microsoft Learn.
- On-prem 2019/2022: plan for CLR or Agent PowerShell.

Create a playground:

CREATE TABLE dbo.Products(
  ProductId   int IDENTITY PRIMARY KEY,
  Sku         varchar(50) UNIQUE NOT NULL,
  Name        nvarchar(200) NOT NULL,
  Price       decimal(10,2) NOT NULL,
  AttrJson    nvarchar(max) NULL  -- freeform JSON attributes
);

Part 1 — JSON basics you’ll use every day

Validate incoming JSON

SELECT ISJSON(@payload) AS IsValid; -- 1 = valid

2.Extract fields

SELECT
  JSON_VALUE(@payload, '$.name')        AS Name,
  JSON_VALUE(@payload, '$.price')       AS Price,
  JSON_QUERY(@payload,  '$.tags')       AS TagsArray;

JSON_VALUE returns scalars; JSON_QUERY returns objects/arrays Microsoft Learn.

Insert via OPENJSON ... WITH

DECLARE @payload nvarchar(max) = N'[
  {"sku":"A-100","name":"Hat","price":15.99,"attrs":{"color":"black","sizes":[ "S","M" ]}},
  {"sku":"B-200","name":"Shirt","price":24.50,"attrs":{"color":"blue","sizes":[ "M","L" ]}}
]';

INSERT dbo.Products (Sku, Name, Price, AttrJson)
SELECT sku, name, price, attrs
FROM OPENJSON(@payload)
WITH(
  sku   varchar(50)      '$.sku',
  name  nvarchar(200)    '$.name',
  price decimal(10,2)    '$.price',
  attrs nvarchar(max)    '$.attrs'
);

This strongly types columns and stores the nested attributes for later use Microsoft Learn.

Emit API-ready JSON

SELECT ProductId, Sku, Name, Price
FROM dbo.Products
FOR JSON PATH, ROOT('items'), INCLUDE_NULL_VALUES;

This shapes rows as clean JSON for your app layer Microsoft Learn.

Part 2A — Call an external API with `sp_invoke_external_rest_endpoint` (if available)

Goal: fetch exchange rates and store them.

DECLARE @resp table(status int, headers nvarchar(max), content nvarchar(max));

DECLARE @url nvarchar(4000) = N'https://api.exchangerate.host/latest?base=USD';
EXEC sys.sp_invoke_external_rest_endpoint
  @url = @url,
  @method = N'GET',
  @headers = N'{"User-Agent":"SQL-Workshop"}',
  @timeout = 20,
  @result = @resp OUTPUT;

-- Shred some rates
WITH J AS (
  SELECT content FROM @resp WHERE status = 200
)
SELECT
  k.[key]   AS Currency,
  TRY_CONVERT(decimal(18,6), v.[value]) AS Rate
FROM J
CROSS APPLY OPENJSON(JSON_QUERY(content, '$.rates')) AS r
CROSS APPLY OPENJSON(r.value) AS v
CROSS APPLY (SELECT v.[key]) AS k([key]);

You can use the same approach for POSTing data with a JSON body. Read the proc’s parameters and security notes in the docs before enabling in production Microsoft Learn.

Part 2B — Alternative: SQL Agent PowerShell job (works everywhere)

Create a staging table:

CREATE TABLE dbo.Api_ExchangeRates_Staging(
  LoadedUtc datetime2 NOT NULL DEFAULT(sysutcdatetime()),
  Payload   nvarchar(max) NOT NULL
);

2.Agent job step (PowerShell):

$uri = "https://api.exchangerate.host/latest?base=USD"
$resp = Invoke-RestMethod -Method Get -Uri $uri -Headers @{ 'User-Agent'='SQL-Workshop' }
$json = $resp | ConvertTo-Json -Depth 10
$sql  = "INSERT dbo.Api_ExchangeRates_Staging(Payload) VALUES (@p1);"
Invoke-Sqlcmd -ServerInstance "MyServer" -Database "MyDb" -Query $sql -Variable p1=$json

Shred to a proper table with OPENJSON as in Part 2A.

This keeps HTTP off the engine, plays well with scheduling, and is easy to secure and observe.

Part 3 — Build a small Web API for your data

Scaffold an ASP.NET Core Web API and connect it to SQL Server following Microsoft’s tutorial. Use EF Core for quick start or Dapper for lean data access Microsoft Learn.
Add endpoints:

GET /api/products?page=1&size=50 with pagination
GET /api/products/{id}
POST /api/products that validates JSON and uses parameterized SQL or stored procs

Return JSON directly from T-SQL when convenient:

[HttpGet("/api/products-json")]
public async Task<IResult> GetProductsJson([FromServices] SqlConnection conn)
{
    var cmd = new SqlCommand("EXEC dbo.Products_GetPage @Page,@Size", conn);
    cmd.Parameters.AddWithValue("@Page", 1);
    cmd.Parameters.AddWithValue("@Size", 50);
    using var rdr = await cmd.ExecuteReaderAsync();
    if (!await rdr.ReadAsync()) return Results.Ok(new { items = Array.Empty<object>() });
    var json = rdr.GetString(0); // single NVARCHAR(MAX) column
    return Results.Text(json, "application/json");
}

Put it behind Azure API Management. Add policies for JWT validation, per-caller quotas, and response caching. Publish docs for devs in the built-in developer portal Microsoft Learn+1.

Common design questions and straight answers

Should I call external APIs from inside a user transaction?
Usually no. Keep API calls outside the transaction to avoid holding locks and to make retries safe.
SQLCLR or Agent PowerShell?
If you need synchronous, low-latency calls and you accept the operational burden, CLR is fine with strict security. Otherwise, Agent PowerShell is simpler and safer to operate.
OData direct from SQL Server?
There isn’t a native OData host in SQL Server anymore; expose via Web API or EF Core OData and let API Management handle the front door.
JSON performance tips
Use OPENJSON ... WITH(...) so SQL Server does typed extraction in one pass. Avoid parsing the same JSON repeatedly in scalar functions. Consider persisting key attributes to normal columns for indexing and use the JSON only for flexible extras Microsoft Learn.

Production checklist

Secrets in Key Vault or Managed Identity, not in T-SQL text.
Clear retry policy with backoff and idempotency for POST/PUT.
Request timeouts set at every layer.
Pagination on every list endpoint.
FOR JSON PATH or DTOs that match your public contract; never leak internal column names Microsoft Learn.
Monitoring: status code distribution, P95 latency, and payload sizes in API Management Microsoft Learn.
For CLR: clr strict security enabled; assemblies signed; no TRUSTWORTHY ON shortcuts Microsoft Learn.

Final thoughts

SQL Server works well with APIs when you use the right pattern for the job. For consuming APIs, prefer sp_invoke_external_rest_endpoint when available; otherwise keep calls at the edge with Agent PowerShell or a service. For exposing APIs, put a thin, well-secured Web API in front of SQL Server and let API Management own the gateway concerns. Combine that with solid JSON handling in T-SQL and you’ll have an API stack that’s secure, observable, and easy to evolve.

References

sp_invoke_external_rest_endpoint documentation and scope notes (Azure SQL / MI GA; SQL Server 2025 preview): Microsoft Learn; Azure SQL MI “What’s new” release notes; community write-ups Microsoft Learn+2Microsoft Learn+2
JSON in SQL Server: OPENJSON, FOR JSON, ISJSON, JSON_VALUE, JSON_QUERY, JSON_MODIFY and compatibility requirements Microsoft Learn+3Microsoft Learn+3Microsoft Learn+3
CLR integration and security hardening (clr strict security, permission sets, assembly deployment) Microsoft Learn+2Microsoft Learn+2
ASP.NET Core + EF Core tutorials for building a Web API, connection practices, and data access patterns Microsoft Learn
Azure API Management concepts, policies, and lifecycle management for your APIs Microsoft Learn+1

If you want, I can package the workshop code into a single SQL script plus a minimal C# API project structure you can drop into your repo.

Discover more from SQLYARD

Subscribe to get the latest posts sent to your email.

SQL Server + APIs: a complete guide for DBAs and data engineers

What “API with SQL Server” actually means

Version check: where `sp_invoke_external_rest_endpoint` fits

JSON in T-SQL: the essentials you’ll actually use

Pattern A: consume a REST API from SQL Server

A1) If you have `sp_invoke_external_rest_endpoint` (Azure SQL / MI, or SQL Server 2025 preview)

A2) If you don’t have the proc (SQL Server 2019/2022 on-prem)

Pattern B: expose SQL Server data as a REST API

Best practices you’ll be glad you followed

Step-by-step workshop: from zero to “API-ready” SQL

Part 0 — Setup

Part 1 — JSON basics you’ll use every day

Part 2A — Call an external API with `sp_invoke_external_rest_endpoint` (if available)

Part 2B — Alternative: SQL Agent PowerShell job (works everywhere)

Part 3 — Build a small Web API for your data

Common design questions and straight answers

Production checklist

Final thoughts

References

Like this:

Related

Discover more from SQLYARD

Leave a ReplyCancel reply

Sign up to receive email updates, fresh news and more!

What “API with SQL Server” actually means

Version check: where sp_invoke_external_rest_endpoint fits

JSON in T-SQL: the essentials you’ll actually use

Pattern A: consume a REST API from SQL Server

A1) If you have sp_invoke_external_rest_endpoint (Azure SQL / MI, or SQL Server 2025 preview)

A2) If you don’t have the proc (SQL Server 2019/2022 on-prem)

Pattern B: expose SQL Server data as a REST API

Best practices you’ll be glad you followed

Step-by-step workshop: from zero to “API-ready” SQL

Part 0 — Setup

Part 1 — JSON basics you’ll use every day

Part 2A — Call an external API with sp_invoke_external_rest_endpoint (if available)

Part 2B — Alternative: SQL Agent PowerShell job (works everywhere)

Part 3 — Build a small Web API for your data

Common design questions and straight answers

Production checklist

Final thoughts

References

Share this:

Like this:

Related

Discover more from SQLYARD

Related Posts

Leave a ReplyCancel reply

Discover more from SQLYARD

Version check: where `sp_invoke_external_rest_endpoint` fits

A1) If you have `sp_invoke_external_rest_endpoint` (Azure SQL / MI, or SQL Server 2025 preview)

Part 2A — Call an external API with `sp_invoke_external_rest_endpoint` (if available)