Azure SQL Database External REST Endpoints Integration Public Preview
The ability to call a REST endpoint natively from Azure SQL Database, which was made available via an Early Adopter Preview in May, is now moving to Public Preview. I’m extremely happy that as of today the new system stored procedure
sp_invoke_external_rest_endpoint is available to everyone, automatically and transparently: you can use it right away, from any Azure SQL Database that you may already have or that you’ll create in future.
sp_invoke_external_rest_endpoint you can call a REST endpoint, like an Azure Function, for example, with just a line of code:
declare @ret as int, @response as nvarchar(max); exec @ret = sp_invoke_external_rest_endpoint @method = 'GET', @url = 'https://my-function.azurewebsites.net/api/say?message=hello world', @response = @response output; select @ret as ReturnCode, @response as Response;
Here’s a screenshot of the result of calling an Azure Function that provides information about local restaurants. The screenshot also shows that you can also query using GraphQL, if that is supported by the called endpoint.
Azure Function is just one of the services you can use with
sp_invoke_external_rest_endpoint. In fact, you can call any of the following Azure services:
|Azure Functions||Azure Apps Service|
|Azure App Service Environment||Azure Static Web Apps|
|Azure Logic Apps||Azure Container Instances|
|Azure Event Grid||Azure Event Hubs|
|PowerApps / Dataverse||Azure Cognitive Services|
|Power BI||Microsoft Graph|
|Analysis Services||IoT Central|
Aside from calling an Azure Function, as you can see, you really have a lot of options which will enable you to create more user-friendly and responsive solutions. Just a few ideas, for example:
- integrate with Azure Event Hubs and Azure Event Grid is extremely easy now to push data out of Azure SQL to make it available to event-driven solutions.
- integrate with Power BI so that you can execute a DAX query using the executeQueries REST endpoint and get the result right into Azure SQL DB
- invoke any REST endpoint anywhere in the world, thanks to the integration with API Management
- send a notification to a SignalR website right from Azure SQL DB thanks to already mentioned Azure Function integration
- enrich your data using AI and ML models – for example to detect anomalies – via the integration with Azure Cognitive Services
To learn everything about
sp_invoke_external_rest_endpoint, the newly added security permission needed to use it, the support for Managed Service Identities and much more, take a look at the official documentation: sp_invoke_external_rest_endpoint (Transact-SQL) (Preview).
To make it easy to start and use
sp_invoke_external_rest_endpoint right away, you can also use this GitHub Repo full of samples: azure-sql-db-invoke-external-rest-endpoints.
Enjoy and let us know if you like this feature and how you are using it, using the comment section below to give us your feedback and to tell us how you’d like to see this feature evolving, in the future. 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. Looking forward to reading your ideas!
This is great and long waited 🙂
But I have a question about the API/URL limitation.
Is the list of known URLs allowed for external calls checked against the given string, or is it possible to resolve DNS alias?
We typically use APIM to expose endpoints, and use custom domains there.
Great to hear that you like the feature! 🙂 The allow-list is checked both using string matching *and* resolved IPs. If you have an endpoint hosted in Azure that is using a custom domain name and you want to reach that endpoint using the custom domain name, then you have to use API Management to call it (and use the azure-api.net domain)
Great to know about the feature. Have a question –
Is this supported for “Azure Database for PostgreSQL servers”
Hi Krishna. Great to hear that like the feature! No, this feature is not available on PostgreSQL (or MySQL or MariaDB) databases: it is available only on Azure SQL databases.