// Call REST API Directly From SQL Server

When I attended the PASS Summit in Seattle in November 2022, we got a list of new releases from Microsoft. Among those releases we got the ability to call an external REST API directly from the SQL server in Azure.

The feature is, at this post is written, in public preview, so you might see or experience some minor problems.

The approach

The SQL server in Azure (all versions in Azure - besides the one on a VM) has now gotten a new stored procedure named:

sp_invoke_external_rest_endpoint

The stored procedure takes a list of arguments:

1. url » URL of the HTTPS REST endpoint to be called. url is nvarchar(4000) with no default.
2. payload » Unicode string in a JSON format that contains the payload to send to the HTTPS REST endpoint. Payload must be a valid JSON document. payload is nvarchar(max) with no default.
3. headers » Headers that must be sent as part of the request to the HTTPS REST endpoint. Headers must be specified using a flat JSON (a JSON document without nested structures) format.
4. method » HTTP method for calling the URL. Must be one of the following values: GET, POST, PUT, PATCH, DELETE. method is nvarchar(6) with POST as default value.
5. timeout » Time in seconds allowed for the HTTPS call to run.
6. credential » Indicate which DATABASE SCOPED CREDENTIAL object will be used to inject authentication info in the HTTPS request. credential is a sysname with no default value.
7. response (this is output) » Allow the response received from the called endpoint to be passed into the specified variable. response is a nvarchar(max).

Usage scenario

The usage scenarios are plenty, you can surely think of more than I can below.

One of the things I think is very good wtih this new method, is taht we can now ingest data directly to a datawarehouse without using other tools. This is not a usage scenario to gather huge amounts of data, as I would surely use other tools for that process. But a small payload to load data to the SQL server would be a good starting point to try this new feature out.

Another usage scenario is to get the SQL server to send data to the REST API directly as the API methods to apply are fully supported in this feature. Think of a situation where you need a small process to send or update data to an API - then you can ndo this now directly from the SQL server and in T-SQL code.

All of this is naturally a process that needs a good governance and a selection between the method of SQL directly or Data Factory/Synapse Pipelines.

Example usage

For every usage scenario, you need to configure access to the stored procedure by running below script:

GRANT EXECUTE ANY EXTERNAL ENDPOINT TO [<PRINCIPAL>];

A note on throttling: stolen from Microsoft documentation:
The number of concurrent connections to external endpoints done via sp_invoke_external_rest_endpoint are capped to 10% of worker threads, with a maximum of 150 workers. On an single database throttling is enforced at the database level, while on an elastic pool throttling is enforced both at database and at pool level.

So you can only use 10% of your worker threads in conrrent connections to an external endpoint. If you need to know how many worker threads you have you can run below script (also stolen from Microsoft documentation):

SELECT
  [database_name],
  DATABASEPROPERTYEX(DB_NAME(), 'ServiceObjective') AS service_level_objective,
  [slo_name] as service_level_objective_long,
  [primary_group_max_outbound_connection_workers] AS max_database_outbound_connection,
  [primary_pool_max_outbound_connection_workers] AS max_pool_outbound_connection
FROM
  sys.dm_user_db_resource_governance
WHERE
  database_id = DB_ID();

Situation 1 - download data from API

Given a situation where I need to download data from an API endpoint from my SQL server. I then need to configure and execute my stored procedure as follows:

CREATE DATABASE SCOPED CREDENTIAL [https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>]
WITH IDENTITY = 'HTTPEndpointHeaders', SECRET = '{"x-functions-key":"<your-function-key-here>"}';

DECLARE @ret INT, @response NVARCHAR(MAX);

EXEC @ret = sp_invoke_external_rest_endpoint
  @url = N'https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>?key1=value1',
  @headers = N'{"header1":"value_a", "header2":"value2", "header1":"value_b"}',
  @credential = [https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>],
  @payload = N'{"some":{"data":"here"}}',
  @response = @response OUTPUT;

SELECT @ret AS ReturnCode, @response AS Response;

Above dummy approach gives me data from an Azure Function using and authorization key.

Situation 2 - send/update data to API

Given a situation I want to send data to an Event Hub - in this case my credential then needs to be a Managed Idendity. The setup and stored procedure exection should look something like this:

CREATE DATABASE SCOPED CREDENTIAL [https://<EVENT-HUBS-NAME>.servicebus.windows.net]
    WITH IDENTITY = 'Managed Identity',
        SECRET = '{"resourceid": "https://eventhubs.azure.net"}';
GO

DECLARE @Id UNIQUEIDENTIFIER = NEWID();
DECLARE @payload NVARCHAR(MAX) = (
        SELECT *
        FROM (
            VALUES (@Id, 'John', 'Doe')
            ) AS UserTable(UserId, FirstName, LastName)
        FOR JSON AUTO,
            WITHOUT_ARRAY_WRAPPER
        )
DECLARE @url NVARCHAR(4000) = 'https://<EVENT-HUBS-NAME>.servicebus.windows.net/from-sql/messages';
DECLARE @headers NVARCHAR(4000) = N'{"BrokerProperties": "' + STRING_ESCAPE('{"PartitionKey": "' + CAST(@Id AS NVARCHAR(36)) + '"}', 'json') + '"}'
DECLARE @ret INT, @response NVARCHAR(MAX);

EXEC @ret = sp_invoke_external_rest_endpoint @url = @url,
    @headers = @headers,
    @credential = [https://<EVENT-HUBS-NAME>.servicebus.windows.net],
    @payload = @payload,
    @response = @response OUTPUT;

SELECT @ret AS ReturnCode, @response AS Response;

Notes on usage

The stored procedure “only” handles a complete string of URL (and credentials if needed in the url string) with a maximum length of nvarchar(4000)

Take note on access to credentials in the database, as the credentials to be used in this stored procedure only handles the database stored credentials.

There is, for now, only a limited list of allowed endpoints for this stored procedure:

Happy coding and now it is time for ☕