External REST endpoint invocation in Azure SQL DB went GA in August 2023. Whereas before, we might have needed an intermediate technology to make a REST call to an Azure service, we can now use an Azure SQL Database to call a REST endpoint directly.
One use case for this would be to retrieve a file from blob storage. I explain how to set this up below.
Azure Preparations
Before we can do the SQL parts, we need to do a bit of setup. First, we need to enable the system-assigned managed identity for the Azure SQL server. This is currently located under Security -> Identity in the Azure portal. Once this is on, go to your storage account in which you want to read and/or write data and add read permissions. This could be done by adding the managed identity to the Storage Blob Data Reader role on the container.
You’ll also need to make sure that your storage account firewall will allow traffic from the Azure SQL server to the storage account. The way to accomplish this will differ depending on your network configuration. Azure SQL servers don’t have a dedicated static IP address by default. You may only be allowed to use private endpoints instead of public. While the fastest way to do this would be to allow public network access from all networks, or to allow Azure services on the trusted services list to access the storage account, that presents some security risks and is not recommended for actual solutions containing organizational data. Whatever your setup, your database needs to talk to your storage account.
SQL Preparations
The remaining steps can be done in T-SQL. But before we can query the data, we need to create a master key (if one is not already created) and database-scoped credential.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxxxxxxxxxxxxxxxxxxxxxxx'; --change this value to a real value
GO
CREATE DATABASE SCOPED CREDENTIAL [https://myaccountname.blob.core.windows.net]
with identity = 'Managed Identity',
secret = '{"resourceid": "https://storage.azure.com" }'; SQL Call to REST Endpoint
The actual SQL call to the rest endpoint uses the sp_invoke_external_rest_endpoint stored procedure. Let’s say I have a single JSON file I want to read into the database. Here’s the SQL I might use.
declare @response nvarchar(max);
declare @url nvarchar(4000) = 'https://myaccountname.blob.core.windows.net/container1/MyFile.json';
Exec sp_invoke_external_rest_endpoint
@url = @url,
@headers = '{"Accept":"application/json","x-ms-version" : "2022-11-02"}',
@method = 'GET',
@credential = [https://myaccountname.blob.core.windows.net],
@response = @response output
Select @response;Explanation
We are using SQL to directly call the Get Blob REST operation. The Azure SQL server managed identity is the actual user calling the REST endpoint in my code above. Alternatively, you could use a Shared Access Signature instead of the managed identity. Your arguments in sp_invoke_external_rest_endpoint will look a little different in that case.
You must create a database scoped credential to be used in this invocation if the API requires authentication credentials to be passed. There is no default value for @credential.
For for this API call specifically, I included the Accept header. I specified “application/json”, which is the default value, just so it is more explicit what I will receive in my response (JSON). This could also be “application/xml” or “text/*”. The x-ms-version header is required. There are several other optional headers that you could include as needed.
We are doing a GET operation to read a file in the example code. But we could just as easily do a PUT or another operation.
Don’t forget to grant access to the appropriate user(s) to use the database scoped credential. Otherwise, those users cannot sucessfully execute the stored procedure.
Limitations
There will be times when sp_invoke_external_rest_endpoint is not appropriate. These include:
- When your request or response is larger 100 MB (UTF-8) format
- The request URL length is larger than 8 KB or the query string is larger than 4 KB
- The request or response headers size is larger than 8 KB.
- Your endpoint uses TLS 1.1 or earlier.
More Possibilities
In addition to calling Azure blob storage APIs, there are several other Azure services we can call using sp_invoke_external_rest_endpoint. We could write to Azure Files, call an Azure Function or a Logic App, send data to Event Hubs or Stream Analytics, or execute DAX queries in the Power BI service.
Are you using sp_invoke_external_rest_endpoint? If so, let me know what you are doing with it in the comments.