SQL Server 2025 is introducing a new feature that might be familiar if you’ve used Azure SQL: the ability to call REST endpoints directly from T-SQL.  

On the surface, it’s a great way to handle tasks like sending notifications or interacting with external APIs from within your database. But it’s also a tool that comes with some serious risks.  

A slow or poorly designed endpoint can hurt performance, cause delays, block operations, and even expose data. So, make sure to use this feature with care and read Microsoft’s official guidance on mitigating risks. With that in mind, let’s look at a simple example of how to use it correctly. 

Enabling the feature 

Before we can use the sp_invoke_external_rest_endpoint stored procedure, we need to enable it with the following command:   

EXEC sp_configure ‘external rest endpoint enabled’, 1; 

RECONFIGURE; 

If you try to use the procedure without enabling it first, you’ll encounter the following error: 

Msg 31643, Level 16, State 1, Procedure sys.sp_invoke_external_rest_endpoint_internal 

‘sp_invoke_external_rest_endpoint’ is disabled on this instance of SQL Server. Use sp_configure ‘external rest endpoint enabled’ to enable it. 

Setting up security 

Next, we’ll create a database scoped credential. In our example, we’re using the OpenAI API. For obvious reasons, we’d prefer not to share our API keys, and by extension, the linked credit card. Using a credential avoids the need to hardcode tokens directly in the query, which is both cleaner and much more secure.
 

CREATE DATABASE SCOPED CREDENTIAL [https://api.openai.com] 

WITH IDENTITY = ‘HTTPEndpointHeaders’, 

SECRET = ‘{“Authorization”:”Bearer sk-MyBearerCode”}’; 

Calling the REST endpoint 

With the credential in place and the feature enabled, we can now call the stored procedure. In this example, we’ll ask the gpt-4o-mini model a simple question: “What are fun things to do in Belgium?”.
 

DECLARE @body nvarchar(max) = N’ 

{ 

  “model”: “gpt-4o-mini”, 

  “messages”: [ 

    { “role”: “user”, “content”: “What are fun things to do in Belgium?” } 

  ] 

}’; 

DECLARE @ret int, 

        @rawResp nvarchar(max); 

EXEC @ret = sys.sp_invoke_external_rest_endpoint 

  @url = N’https://api.openai.com/v1/chat/completions’, 

  @method = ‘POST’, 

  @payload = @body, 

  @credential = [https://api.openai.com], 

  @response = @rawResp OUTPUT; 

— Extracting the assistant’s reply and HTTP status code from the JSON response 

SELECT JSON_VALUE(@rawResp,’$.result.choices[0].message.content’) AS assistant_reply, 

       JSON_VALUE(@rawResp,’$.response.status.http.code’) AS http_status; 

Let’s break down what’s happening here: 

1. First, we declare a @body variable containing the JSON payload for the API, including the model’s name and prompt. 

1. Next, we execute the stored procedure, passing the URL, method, payload, and the credential we created earlier. 

1. Finally, we use the JSON_VALUE function to extract the assistant’s reply and the HTTP status code from the raw JSON response. 

Once the procedure has been executed, we’ll receive the response: 

Conclusion 

Of course, this is just a sample, but the possibilities are genuinely interesting. You could store API responses using the new native JSON data type, which we’ll explain in our next blog. You could also take data directly from your database and pass it to an API for enrichment, classification, or translation. 

The main point is to remember the risks we mentioned at the start. Using external REST endpoints is a powerful feature. You just need to handle it with care, especially with sensitive data or performance-critical processes. 

If you’re thinking about how this new feature could fit into your environment, or if you want to talk through the security implications, get in touch. We’re happy to clear things up for you!