Summer 2023 REST Endpoint Invocation Update

Welcome to the Summer 2023 REST Endpoint Invocation update post! This summer, we updated the External REST Endpoints Invocation feature with a few key fixes and additions to help you use more Azure Services with your Azure SQL Database. The feature was released to public preview last year and this latest set of new features are preparing it for General Availability in the near future.

As a refresher, this feature makes it possible to call REST endpoints from other Azure Services from right in the Azure SQL Database. With a quick call to the system stored procedure sp_invoke_external_rest_endpoint, you can have data processed via an Azure Function, update a PowerBI dashboard, or even talk to OpenAI. Here is a quick example of sending a REST request to an Azure Function:

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

EXEC @ret = sp_invoke_external_rest_endpoint
     @url = N'https://mydemofunction.azurewebsites.net/api/HttpTrigger',
     @payload = N'{"name":"test"}',
     @method = N'POST',
     @response = @response OUTPUT;

SELECT @ret AS ReturnCode, @response AS Response;

Very straightforward and easy to use.

What’s New for Summer 2023

More Azure Services

To start, we have expanded on the Azure Services you can call, adding OpenAI, Azure Blob Storage, Azure Files, Azure Queue Services and Azure Table Services.

The full list is as follows:

XML and Text Payloads

Expanding on core functionality, we have added the ability to have request and response payloads be text or XML. You can now use the following media types for the header content-type:

• application/json
• application/vnd.microsoft.*.json
• application/xml
• application/vnd.microsoft.*.xml
• application/vnd.microsoft.*+xml
• application/x-www-form-urlencoded
• text/*

and the for the accept header, the following are now the accepted values:

• application/json
• application/xml
• text/*

To illustrate this, if we take the example from above where we called an Azure Function, we can alter the parameters passed to the stored procedure by adding a header:

"Accept":"text/*"

This tells the procedure to expect a text response back from the Azure Function. The full call would look like the following:

declare @url nvarchar(4000) = N'https://texttest1.azurewebsites.net/api/HttpTrigger1?';
DECLARE @headers nvarchar(102) = N'{"Accept":"text/*"}'
declare @payload nvarchar(max) = N'{"name":"Frank"}'
declare @ret int, @response nvarchar(max);
exec @ret = sp_invoke_external_rest_endpoint
    @url = @url,
    @method = 'POST',
    @headers = @headers,
    @payload = @payload,
    @response = @response output;
   
select @ret as ReturnCode, @response as Response;

And text returning in a payload will look similar to the following example:

    "response": {
        "status": {
            "http": {
                "code": 200,
                "description": "OK"
            }
        },
        "headers": {
            "Date": "Fri, 04 Aug 2023 16:54:11 GMT",
            "Transfer-Encoding": "chunked",
            "Content-Type": "text\/plain; charset=utf-8",
            "Request-Context": "appId=cid-v1:2112-3d3c-141976-8a1b-goatch33s3"
        }
    },
    "result": "Hello, Frank. This HTTP triggered function executed successfully. Oh, this is text as well!"
}

Moving to XML, return responses using XML will look like the following:

<output>
 <response>
  <status>
   <http code="" description=" " />
  </status>
  <headers>
   <header key="" value="" />
   <header key="" value="" />
  </headers>
 </response>
 <result>
 </result>
</output>

Like the text accept header, XML is done in the same manner:

'Accept': 'application/xml'

with the following code example highlighting this:

DECLARE @ret INT, @response NVARCHAR(MAX);
EXEC @ret = sp_invoke_external_rest_endpoint
  @url = N'https://blobby.blob.core.windows.net/datafiles/my_favorite_blobs.txt?sp=r&st=2023&se=2023&spr=https&sv=2022&sr=b&sig=XXXXXX',
  @headers = N'{"Accept":"application/xml"}',
  @method = 'GET',
  @response = @response OUTPUT;
SELECT cast(@response as xml);
go

<output>
  <response>
    <status>
      <http code="200" description="OK" />
    </status>
    <headers>
      <header key="Date" value="Fri, 04 Aug 2023 18:46:11 GMT" />
      <header key="Content-Length" value="2297" />
      <header key="Content-Type" value="application/xml" />
      <header key="Content-MD5" value="l+LknBXHtvd1gwMsBKFWXw==" />
      <header key="Last-Modified" value="Wed, 19 Apr 2023 22:17:33 GMT" />
      <header key="Accept-Ranges" value="bytes" />
      <header key="Server" value="Windows-Azure-Blob/1.0 Microsoft-HTTPAPI/2.0" />
      <header key="x-ms-version" value="2021-10-04" />
      <header key="x-ms-creation-time" value="Wed, 19 Apr 2023 22:17:33 GMT" />
      <header key="x-ms-lease-status" value="unlocked" />
      <header key="x-ms-lease-state" value="available" />
      <header key="x-ms-blob-type" value="BlockBlob" />
      <header key="Content-Disposition" value="" />
      <header key="x-ms-server-encrypted" value="true" />
    </headers>
  </response>
  <result>Lorem ipsum dolor sit amet, consectetur adipiscing elit</result>
</output>

More Examples!

OpenAI

The first example is calling OpenAI in Azure and asking it a deep philosophical question that only AI could answer:

declare @url nvarchar(4000) = N'https://demo.openai.azure.com/openai/deployments/chat/completions';
declare @headers nvarchar(102) = N'{"api-key":“XXXXXXXXXXXXXX"}'
declare @payload nvarchar(max) = N'{"messages":[{"role":"system","content":"Why is the sky blue?"}]}'
declare @ret int, @response nvarchar(max); 

exec @ret = sp_invoke_external_rest_endpoint
     @url = @url,
     @method = 'POST',
     @headers = @headers,
     @payload = @payload,
     @timeout = 230,
     @response = @response output;      

select @ret as ReturnCode, @response as Response;

With our answer being:

"The blue color of the sky is due to the scattering of sunlight by the gas molecules and
tiny particles in the Earth's atmosphere, primarily nitrogen and oxygen. These molecules
absorb and scatter light in different ways, with blue light being scattered more than
other colors because it has a shorter wavelength. This scattering of blue light in all
directions makes the sky appear blue to our eyes. At sunrise and sunset, when the light
must travel through more of the Earth's atmosphere, the sky can 
appear red or orange due to the scattering of longer wavelengths of light."

Very deep indeed!

Azure Files and more XML

Next, let’s look at an example of calling Azure Files and the new XML functionality. In this example, we will create some database scoped credentials using a SAS token from Azure Blob Storage, create a file, put some text into it, and finally read the file back. (this example is in the docs as well)

Start by creating a master key for the Azure SQL Database if one has not already been created:

create master key encryption by password = '2112templesmlm2BTS21.qwqw!@0dvd'
go

Then, create the database scoped credentials using the SAS token provided by the Azure Blob Storage Account.

create database scoped credential [filestore]
with identity='SHARED ACCESS SIGNATURE', 
secret='sv=2022-11-02&ss=bfqt&srt=sco&sp=seespotrun&se=2023-08-03T02:21:25Z&st=2023-08-02T18:21:25Z&spr=https&sig=WWwwWWwwWWYaKCheeseNXCCCCCCDDDDDSSSSSU%3D'
go

Next, create the file and add text to it with the following two statements:

declare @payload nvarchar(max) = (select * from (values('Hello from Azure SQL!', sysdatetime())) payload([message], [timestamp])for json auto, without_array_wrapper)
declare @response nvarchar(max), @url nvarchar(max), @headers nvarchar(1000);
declare @len int = len(@payload)

-- Create the File
set @url = 'https://myfiles.file.core.windows.net/myfiles/test-me-from-azure-sql.json'
set @headers = json_object(
        'x-ms-type': 'file',
        'x-ms-content-length': cast(@len as varchar(9)),
        'Accept': 'application/xml')
exec sp_invoke_external_rest_endpoint
    @url = @url,
    @method = 'PUT',
    @headers = @headers,
    @credential = [filestore],
    @response = @response output
select cast(@response as xml);

-- Add text to the File
set @headers = json_object(
        'x-ms-range': 'bytes=0-' + cast(@len-1 as varchar(9)),
        'x-ms-write': 'update',
        'Accept': 'application/xml');
set @url = 'https://myfiles.file.core.windows.net/myfiles/test-me-from-azure-sql.json'
set @url += '?comp=range'
exec sp_invoke_external_rest_endpoint
    @url = @url,
    @method = 'PUT',
    @headers = @headers,
    @payload = @payload,
    @credential = [filestore],
    @response = @response output
select cast(@response as xml)
go

Finally, use the following statement to read the file

declare @response nvarchar(max);
declare @url nvarchar(max) = 'https://myfiles.file.core.windows.net/myfiles/test-me-from-azure-sql.json'
exec sp_invoke_external_rest_endpoint
    @url = @url,
    @headers = '{"Accept":"application/xml"}',
    @credential = [filestore],
    @method = 'GET',
    @response = @response output
select cast(@response as xml)
go

What’s Next?

In the coming months, we hope to add some more Azure Services to the Allow list as well as move out of public preview and into General Availability. Stay tuned!

For More Information

To learn more about sp_invoke_external_rest_endpoint, look at the documentation: sp_invoke_external_rest_endpoint (Transact-SQL) (Preview).

Want more examples? You can use this GitHub Repository here: azure-sql-db-invoke-external-rest-endpoints.

Finally, and very importantly, let us know if you like this feature and how you are using it.  Use the comment section below to give us your feedback and to tell us how you’d like to see this feature evolving or drop us a line and contact us directly! If you have some specific request, make sure to add it to the Azure SQL and SQL Server feedback portal, so that other users can contribute and help us prioritize future development.