[SOLVED] Accessing External API Endpoint from Azure SQL Server

Accessing External API Endpoint from Azure SQL Server

I have an external endpoint that I am trying to access using 'sp_invoke_external_rest_endpoint'in an Azure SQL Database. I am getting the error:

Msg 31612, Level 16, State 1, Procedure sys.sp_invoke_external_rest_endpoint_internal, Line 1 Connections to the domain date.nager.at are not allowed.

any suggestions?

DECLARE @URL NVARCHAR(255) = 'https://date.nager.at/api/v3/PublicHolidays/2024/ZA'

--------------------------------------------------------------------------------
-- HTTP Request Parameters
DECLARE @Object AS INT;
DECLARE @HR INT;
DECLARE @JSONResponse as NVARCHAR(max);

--------------------------------------------------------------------------------
-- Perform HTTP GET Request to endpoint
EXEC @HR = sp_invoke_external_rest_endpoint
     @url = @URL,
     @method = 'GET',
     @response = @JSONResponse output;

SELECT @HR AS ReturnCode, @JSONResponse AS Response

Ive tried adding the site to the Azure SQL Server 'Restrict Outbound Networking' section however this is not enabled to begin with to does not really make a difference.

Solution

The reason you are unable to connect to the external domain is because Microsoft have their own whitelist of domains this feature works with. For example, *.azurwebsites.net; *.logic.azure.com. This link to learn.microsoft.com has a much more comprehensive list.

I believe your best alternative is to create an Azure Logic or Function App to make this connection on your behalf.