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.
With 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 Service | |
|---|---|
| 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 |
| API Management | |
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!
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.
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)
Hi Davide
This is a great feature. I was trying to invoke Graph API using this. It worked well while passing authentication token explicitly. But when trying to fetch the token using a prior invocation request using login.microsoftonline.com, it gives an error like below
Msg 31612, Level 16, State 1, Procedure sys.sp_invoke_external_rest_endpoint_internal, Line 1 [Batch Start Line 36]
Connections to the domain login.microsoftonline.com are not allowed.
How do we fetch the auth token in such cases? Even...
Hi Visakh
Great to hear that this feature can be helpful to you. For what concern using Managed Identity, please take a look at this sample here: https://github.com/Azure-Samples/azure-sql-db-invoke-external-rest-endpoints/blob/main/azure-functions.ipynb, and specifically the paragraph "Call an Azure Function protected by Azure AD".
Otherwise, the other option is to create an Azure Function that will retrieve the token for you. Retrieve the token by calling that function, store the token locally along with expiration date and then use it...
Great feature!
I wanted to test it with Dataverse, but I am getting an error that the domain is not allowed. We have an environment on crm4.dynamics.com (EMEA), the official documentation says that only crm.dynamics.com domains are allowed. Is that by design, or because it’s still in preview?
Hi Peter
thanks a lot for your feedback. I’ll take a look into it ASAP. In the meantime, a possible workaround is to use API Management to wrap the call to the desired endpoint.
Excellent, thank you very much!
Are there any plans to allow *.operations.dynamics.com (Dynamics365 for Finance & Operations) as well? That would be really helpful too. (Directly is still easier than through API Management…)
Sure, let me look into it.
Are there plans to make CDC event driven? For example, hook CDC up to EventGrid?
I can’t share anything at the moment, but we know that ability is super important in today’s modern architecture. So….stay tuned 🙂
This is really a great feature !!! I have been waiting for such feature for long to have event driven architecture for Azure Sql. Can you provide the way to publish message to event hub? I am thinking to use it for Audit logs.
Hi Shuaib! That’s great to hear 🙂 You can find many samples here: https://github.com/Azure-Samples/azure-sql-db-invoke-external-rest-endpoints/blob/main/azure-event-hubs.ipynb. Enjoy 🙂
Hey @Davide, Thanks for this update.
I went through the PowerBI TSQL integration wherein you have created a database scoped credential for triggering the API but the database scoped credential is containing the access token which has a lifecycle of 2 hours.
Then what is the use of creating a database scoped credential.
Is it possible to increase the lifetime of that token leveraged in the database scoped credential for 6 months?
Hi Priya, AFAIK it is not possible to change the lifetime of an authentication token as it is set by the server emitting that token. My recommendation is the following: create an Azure Function that returns the token and call that endpoint before calling the Power BI endpoint. Store the token with the expiration date. When calling the Power BI endpoint, check the token's expiration date. If it is expired, renew the token, otherwise use...
Hi @Davide,
Below is my code in Azure Function Powershell,
<code>
I am getting proper result after execution of Azure Function via Portal but when i am triggering it via Azure SQL DB based on this preview approach , i am getting below error:
Failed to execute query. Error: The @result JSON string could not be parsed. Please check the formatting of the JSON.
Can you please help me with this issue as i am unable to find any...
Try to call the function using cUrl or Postman or Insomnia, passing the same headers that Azure SQL DB will pass (see https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-invoke-external-rest-endpoint-transact-sql?view=azuresqldb-current&tabs=request-headers#http-headers). If the response is not JSON (for example, XML), the payload cannot be parsed into JSON and thus you’ll get that error
This is awesome! Is there a way to in Azure SQL to serialize a JSON payload automatically or do payloads always need to be constructed manually (as in your example)?
You can just use FOR JSON to turn any resultset into a JSON document 🙂
This looks really great! Will it be possible to fire in a non blocking manner? Or will it always need to wait for the response before continuing with the script?
For this first release the call is synchronous. You can use Azure Durable Functions, Event Hubs or Event Grid to make a close-to-async call, which will be executed in a few milliseconds.
Will this work with Synapse serverless SQL pools? I’m intrigued by the possibility of writing a DAX query against a data model using the executeQueries REST endpoint, getting the payload, potentially processing with SQL, and then write the data as an External Table using CETAS.
Not at the moment….Right now the support for this feature is only available on Azure SQL DB.