Azure SQL Database External REST Endpoints Integration Public Preview

Davide Mauri

November 16th, 202228 7

Image azure sql integrate with everything

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.

sp_invoke_external_rest_endpoint invoking an Azure Funcrtion passing a GraphQL query

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!

Davide Mauri Principal Product Manager, Azure SQL

Read next

28 comments

Discussion is closed. Login to edit/delete existing comments.

  • MgSam 1

    This is great! Shame this feature did not make it into SQL Server 2022. 🙁

    • Davide MauriMicrosoft employee 0

      We had to decide what to prioritize, and Azure SQL database, needed this feature more than the others, as with Azure SQL MI and SQL Server there are ways – not so simple, I agree – to call a REST endpoint. But we got a lot of feedback around the need to support this feature also in SQL Server, and while I cannot make any promise right now, I can tell you that having feature parity is definitely on my priority list list.

  • Olivier Travers 0

    Nice! Will this support webhooks?

    • Davide MauriMicrosoft employee 0

      As long as it is something that can be called via REST, accepting JSON as payload (if a payload is needed), yes.

  • Chris Haas 0

    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.

    • Davide MauriMicrosoft employee 0

      Not at the moment….Right now the support for this feature is only available on Azure SQL DB.

  • Dave Hall 1

    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?

    • Davide MauriMicrosoft employee 1

      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.

  • Paul Vaillant 0

    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)?

    • Davide MauriMicrosoft employee 0

      You can just use FOR JSON to turn any resultset into a JSON document 🙂

  • Priya Jha 0

    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?

    • Davide MauriMicrosoft employee 0

      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 the one you already have.

      • Priya Jha 1

        Hi @Davide,

        Below is my code in Azure Function Powershell,

        using namespace System.Net

# Input bindings are passed in via param block.
param($Request, $TriggerMetadata)

Connect-AzAccount -Identity
$Test = (Get-AzStorageAccountKey -ResourceGroupName "RGName" -AccountName "AccountName").Value[0]

$AT= $Test | ConvertTo-Json
Write-Host $AT

# Associate values to output bindings by calling 'Push-OutputBinding'.
Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
    StatusCode = [HttpStatusCode]::OK
    Body = $AT
})

        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 document pertaining to this.

  • shuaib ahmad 0

    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.

  • Jason Steele 1

    Are there plans to make CDC event driven? For example, hook CDC up to EventGrid?

    • Davide MauriMicrosoft employee 0

      I can’t share anything at the moment, but we know that ability is super important in today’s modern architecture. So….stay tuned 🙂

  • Peter de Vos 0

    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?

    • Davide MauriMicrosoft employee 1

      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.

      • Peter de Vos 1

        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…)

        • Davide MauriMicrosoft employee 1

          Sure, let me look into it.

  • visakh murukesan 0

    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 tried using an app registration and trying through managed identity but that also failed

    Any help on this would be really appreciated

    Thanks in advance
    Visakh

    • Davide MauriMicrosoft employee 0

      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 – if not expired – anytime you need to call a service using that token as authentication mechanism.

Feedback usabilla icon