A history of accessing REST APIs and web pages from SQL Server

Problem

If you’ve worked with SQL Server as long as I have, you’ve surely seen this type of request float to your inbox: “Can’t we just call {some API} from SQL Server?“

Setting aside the instinctive “no,” how have we actually handled this over the years? Are we any better off with the new solution offered in SQL Server 2025 (sys.sp_invoke_external_rest_endpoint, already described in this tip by Hristo Hristov: Invoke REST API Endpoint from SQL Server 2025)?

Solution

Before SQL Server 2025, the honest answer was usually, “Yes, but it’s not pretty.” We would push SQL Server outside of its comfort zone: using xp_cmdshell to invoke curl or PowerShell, juggling as-good-as-deprecated OLE Automation objects, deploying SQLCLR assemblies (where we could), or spinning up Python under Machine Learning Services. They all worked, depending on your definition of “worked,” but the whole thing always felt brittle and like SQL Server was just a bystander.

And maybe that’s okay. Even today. In a lot of cases, we arguably shouldn’t be trying to hit even internal APIs from SQL Server, never mind out in the wild wild web. But there are always exceptions, and there are always going to be strong opinions that this is something we should handle within SQL Server. And wouldn’t you know it, since an upgrade of one of our systems in November 2025, I’ve already implemented it in two very different use cases that were much more cumbersome only a month earlier.

What this tip isn’t

If you’re looking for a straightforward walkthrough of the new solution in SQL Server 2005, then Hristo’s tip is a great start. This tip has a different focus. I want to step back and look at some of the oddball solutions we’ve relied on, what we’ve learned using them, and which pain points the new feature addresses. Some of those older approaches:

xp_cmdshell calling curl or PowerShell

This has probably been the most common approach and has been supported by all on-premises versions of SQL Server stretching back over two decades. By enabling xp_cmdshell, you could call curl.exe or PowerShell, capture each row from stdout, and stitch everything back together in T-SQL. Example (given some @url):

DECLARE @cmd nvarchar(4000) = N'curl -s "' + @url + '"';
DECLARE @Result table (OutputLine nvarchar(255));
INSERT @Result (OutputLine)
  EXEC master.sys.xp_cmdshell @cmd;
DECLARE @Json nvarchar(max);
SELECT @Json = STRING_AGG(OutputLine, '') 
  FROM @Result;
SELECT * FROM OPENJSON(@Json);

However, this approach:

  • Requires xp_cmdshell to be enabled through sp_configure
  • Has no built-in timeout or error-handling to speak of
  • Can force you to implement tedious (and often brittle) quoting gymnastics if the URL or any of its parameters require escaping or encoding
  • Makes you manually reassemble response chunks from 255-character response lines (anyone remember sp_helptext?)
  • Is considered a security nightmare in many shops – the surface area is just too big
  • Doesn’t work in Azure SQL Database, where xp_cmdshell is blocked – you’d need a different approach to support a migration, or multiple approaches in a hybrid scenario

This is not an optimal approach in SQL Server 2025, but it is certainly still viable in earlier versions (at least on-premises).

OLE Automation (sp_OA*)

Often considered deprecated, the OLE Automation objects are – surprisingly – still supported. You could use these procedures to ping a web page or REST API by creating an instance of MSXML2.ServerXMLHTTP:

DECLARE @object int, @url nvarchar(2048), @reponse nvarchar(max);
EXEC sp_OACreate  'MSXML2.ServerXMLHTTP', @object OUTPUT;
EXEC sp_OAMethod  @object, 'open', NULL, 'GET', @url, false;
EXEC sp_OAMethod  @object, 'send';
EXEC sp_OAMethod  @object, 'responseText', @response OUTPUT;
EXEC sp_OADestroy @object;

There are some downsides:

  • Plenty of folks forgot to call sp_OADestroy, which led to myths about “SQL Server memory leaks” that were really just uncleaned COM objects
  • Like with xp_cmdshell, you need to enable OLE automation procedures through sp_configure
  • And like with xp_cmdshell, security concerns are real
  • Not supported in Azure SQL Database, so the solution isn’t universal

I wouldn’t recommend that approach today.

SQLCLR

SQL Server 2005 introduced the ability to host .net assemblies inside SQL Server. These assemblies could use HttpClient and/or HttpWebRequest/HttpWebResponse. Using .net and a proper HTTP library provides better troubleshooting, language choice (well, mainly C# or VB.net), and plenty of community examples and advice to lean on.

However, there are some downsides, including that the implementation is complex enough that I’m not going to list any code here:

  • There is noticeable performance overhead on first request
  • Configuration changes are required and there are security concerns
  • Not supported in Azure SQL Database

Several times during my career I’ve faced the latter issue when considering SQLCLR for various problems, like string splitting, regular expressions, and hitting web pages or APIs. Between security hoops, deployment friction, and the fact that none of it works in PaaS solutions like Azure SQL Database or CloudSQL, SQLCLR was usually a non-starter.

sp_execute_external_script

Newer on-premises versions have the ability to install Machine Learning Services, an optional component that supports – among other things – running Python scripts external to the engine. This became a popular use case in recent years, since the processing of external data could be implemented by people familiar with languages like Python or R rather than T-SQL.

I’m not going to list a bunch of verbose Python here – there’s an example here. Some of the other downsides are going to seem familiar:

  • You must install and maintain an additional service/component and runtime, representing more pieces that can break and more work to make function on every replica or target environment
  • Data must be marshaled out instead of running in-process like SQLCLR and even OLE Automation
  • Also not supported in Azure SQL Database

That’s a lot of setup just to call an API. It only makes sense if you’re already using ML Services for other work.

Common Themes

All of the older approaches share a few common problems:

  • Escaping the SQL Server sandbox
  • Wrangling external code or executables
  • Managing quoting and line breaks, especially when nesting calls to curl or PowerShell
  • None of them work in Azure SQL Database

Enter SQL Server 2025

As mentioned, SQL Server 2025 offers a new solution in sys.sp_invoke_external_rest_endpoint. This new procedure finally means you don’t have to escape the SQL Server sandbox, wrestle with external programs, or nest quotes or cater to different delimiters from different languages.

You still need to alter configuration, grant access, and deal with outbound firewall rules that might exist (just as you would with any solution that is going to hit an external URL from your database server).

You start by enabling the server-level configuration:

EXEC sys.sp_configure 'external rest endpoint enabled', 1;
GO
RECONFIGURE WITH OVERRIDE;

Then you can allow any given database principal to execute (note there is no operating system access or executables or runtimes to check for vulnerabilities):

GRANT EXECUTE ANY EXTERNAL ENDPOINT TO {someone};

With those in place, you can hit any HTTPS URI you like, as long as it returns JSON (or you can use TRY/CATCH to ignore the output if it doesn’t).

Use Case

My first use case for this was to find all the image URIs in a table that returned a 404 status code (not found) and then replace them with an alternate path. This was much easier (for me, specifically) than handling this with any of the above solutions, or externally in PowerShell or C# or what have you:

DECLARE @c        cursor, 
        @id       int, 
        @OldURL   nvarchar(2048), 
        @NewUrl   nvarchar(2048),
        @response nvarchar(max),
        @code     int;
SET @c = CURSOR LOCAL FAST_FORWARD FOR
 SELECT EntityID, ImageUrl
   FROM dbo.Entities
  WHERE …;
OPEN @c;
FETCH NEXT FROM @c INTO @id, @OldUrl;
WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @Response = NULL, @Code = NULL;
  BEGIN TRY
    EXEC sys.sp_invoke_external_rest_endpoint
         @method   = 'HEAD',  /* or POST – GET does not return JSON, yields Msg 11558 */
         @url      = @OldUrl,
         @response = @response OUTPUT;
    SET  @Code = TRY_CONVERT(int, JSON_VALUE(@response, '$.response.status.http.code'));
    IF @code IN (403, 404) OR COALESCE(@code, 500) >= 500
    BEGIN
      RAISERROR('Replacing image for %d.', 0, 1, @id) WITH NOWAIT;
      /* 
         SET @NewUrl = {some irrelevant computation};
         UPDATE dbo.Entities SET {…} WHERE EntityID = @id;
      */
    END
  END TRY
  BEGIN CATCH
    RAISERROR('Something went wrong with %d (%d).', 0, 1, @id, @code) WITH NOWAIT;
    PRINT @response;
  END CATCH
  FETCH NEXT FROM @c INTO @id, @OldUrl;
END

This worked quite well, even though it’s not exactly the intention of the feature. Honestly, I spent more time formatting the code than I spent thinking about what it was doing. I could complicate things by setting a different property if I got back a redirect (301 or 302) – this probably means that I can’t trust the current path forever, and I’d want to record the destination URL instead. In our case, these images are all under our control, and the problem definitely wasn’t a redirect, but it might be an implementation detail you’ll care about if you are documenting or fixing someone else’s URLs.

My second use case was to implement sending messages to Slack directly from T-SQL. We currently shell out to PowerShell, and we have a .ps1 file on disk that we pass parameters to depending on the message, the priority, and the destination. This solution is not easily portable between environments, and I’ll talk about our new (and better) implementation in more detail in a forthcoming tip.

(All that said, please don’t take this as an endorsement to open up your SQL Server to the entire Internet. I strongly recommend evaluating each use case individually and only allowing outbound access to endpoints that have been approved.)

Azure SQL Database

One thing I didn’t address with the new solution is support in Azure SQL Database. I saved this until last because it is a little complicated and not quite an automatic win. The procedure has been in Azure SQL Database since 2023 (and in preview since 2022). However, it only works with Azure properties and features out of the box; see the list of allowed endpoints. This means you can’t call arbitrary URLs from Azure SQL Database, like you can in the box version or even Azure Managed Instance. The docs for the new procedure say this regarding Azure SQL Database:

“If you want to invoke a REST service that isn’t within the allowed list, you can use API Management to securely expose the desired service and make it available to sp_invoke_external_rest_endpoint.”

In practice, you set up an API in Azure API Management (APIM) to act as a gateway. You make sys.sp_invoke_external_rest_endpoint call that gateway, and APIM forwards the request to your real API. It’s slightly more complex to set up in Azure SQL Database, but that’s a lot better than “not even a possibility.”

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *