November 16th, 2022

Azure SQL Database External REST Endpoints Integration Public Preview

Davide Mauri
Principal Product Manager

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!

Author

Davide Mauri
Principal Product Manager

I started as a developer, I fell in love with Data and Database, in all their forms. I still have a passion for development (C# and Python). My focus has been databases and performance tuning, focusing both on transactional and analytical workloads. For 5 years I helped developers to get the best out of SQL Server, then I moved to Business Intelligence and Data Warehousing for 10 years. Then I moved to IoT and Big Data for a while. Now back to database space, as Product Manager for Azure SQL ...

More about author

28 comments

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

  • Krishna Kumar Natarajan

    Great to know about the feature. Have a question –
    Is this supported for “Azure Database for PostgreSQL servers”

    • Davide MauriMicrosoft employee Author

      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.

  • Rutz Helge

    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.

    • Davide MauriMicrosoft employee Author

      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)

  • visakh murukesan

    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...

    Read more
    • Davide MauriMicrosoft employee Author

      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...

      Read more
  • Peter de Vos

    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 Author

      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

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

  • Jason Steele

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

    • Davide MauriMicrosoft employee Author · Edited

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

  • shuaib ahmad

    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.

  • Priya Jha

    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 Author

      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...

      Read more
      • Priya Jha

        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...

        Read more
      • Davide MauriMicrosoft employee Author

        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

  • Paul Vaillant

    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 Author

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

  • Dave Hall

    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 Author

      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.

  • Chris Haas

    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 Author

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