sp_invoke_external_rest_endpoint :: Connections to the domain are not allowed

Question

Lately Microsoft has released sp_invoke_external_rest_endpoint which seems very straight forward:

declare @ret as int, @response as nvarchar(max);

exec @ret = sp_invoke_external_rest_endpoint 
    @method = 'GET',
    @url = 'https://api.publicapis.org/entries',
    @response = @response output;
    
select @ret as ReturnCode, @response as Response;

I've been trying to query test REST endpoints and they all return the error:

Msg 31612, Level 16, State 1, Procedure sys.sp_invoke_external_rest_endpoint_internal, Line 1 [Batch Start Line 0]
Connections to the domain api.publicapis.org are not allowed.

As anyone been able to use this stored procedure?

Answer 1

Agreed with @AlwaysLearning and @siggemannen there are only specific endpoints are allowed to call the Rest API.

As per Microsoft document below are some specific endpoints that are allowed.

The workaround can be to use the azure function to call the rest Api and then get call this azure function from SQL and get response code and response.

• I create a sample python function to call the API

My sample init.py file:

import logging
import requests
import azure.functions as func

def main(req: func.HttpRequest) -> func.HttpResponse:
output = requests.get('https://reqres.in/api/users/2')
return func.HttpResponse(output.content)

Output of Function:

• Calling this function from Azure SQL

Query:

declare @ret as  int, @response as  nvarchar(max);
exec @ret = sp_invoke_external_rest_endpoint
@method =  'GET',
@url =  '<function-url>',
@response = @response output;
select @ret as ReturnCode, @response as Response;

Output: