| title | sp_invoke_external_rest_endpoint (Transact-SQL) | ||||
|---|---|---|---|---|---|
| description | The sp_invoke_external_rest_endpoint stored procedure invokes an HTTPS REST endpoint. | ||||
| author | jettermctedder | ||||
| ms.author | bspendolini | ||||
| ms.reviewer | randolphwest | ||||
| ms.date | 12/08/2025 | ||||
| ms.service | sql | ||||
| ms.topic | reference | ||||
| ms.custom |
|
||||
| f1_keywords |
|
||||
| helpviewer_keywords |
|
||||
| dev_langs |
|
||||
| monikerRange | =sql-server-ver17 || =sql-server-linux-ver17 || =azuresqldb-current || =azuresqldb-mi-current || =fabric-sqldb |
[!INCLUDE sqlserver2025-asdb-asmi-fabricsqldb]
The sp_invoke_external_rest_endpoint stored procedure invokes an HTTPS REST endpoint provided as an input argument to the procedure.
Caution
Using the sp_invoke_external_rest_endpoint stored procedure allows for the transfer of data to an external entity.
To mitigate the risk of unauthorized access or transfer of data, consider the following security best practices:
- Implement strong access controls: Ensure that only authorized users have access to sensitive data and REST API endpoints. Use the principle of least privilege, as well as database roles and privileges.
- Proper authentication and authorization: Ensure that all REST calls are authenticated and authorized to prevent unauthorized access.
- Monitor and audit access: Regularly monitor and audit access to the database and REST API calls to detect any suspicious activities.
- Regular Security Assessments: Conduct regular security assessments and vulnerability scans to identify and mitigate potential risks.
- Employee training: Educate employees about the risks of data exfiltration and the importance of following security protocols.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
EXECUTE @returnValue = sp_invoke_external_rest_endpoint
[ @url = ] N'url'
[ , [ @payload = ] N'request_payload' ]
[ , [ @headers = ] N'http_headers_as_json_array' ]
[ , [ @method = ] 'GET' | 'POST' | 'PUT' | 'PATCH' | 'DELETE' | 'HEAD' ]
[ , [ @timeout = ] seconds ]
[ , [ @credential = ] credential ]
[ , @response OUTPUT ]
[ , [ @retry_count = ] # of retries if there are errors ]
URL of the HTTPS REST endpoint to be called. @url is nvarchar(4000) with no default.
Unicode string in a JSON, XML, or TEXT format that contains the payload to send to the HTTPS REST endpoint. Payloads must be a valid JSON document, a well formed XML document, or text. @payload is nvarchar(max) with no default.
Headers that must be sent as part of the request to the HTTPS REST endpoint. Headers must be specified using a flat JSON (a JSON document without nested structures) format. Headers defined in the Forbidden headers name list are ignored even if explicitly passed in the @headers parameter; their values are discarded or replaced with system-supplied values when starting the HTTPS request.
The @headers parameter is nvarchar(4000) with no default.
HTTP method for calling the URL. Must be one of the following values: GET, POST, PUT, PATCH, DELETE, HEAD. @method is nvarchar(6) with POST as default value.
Time in seconds allowed for the HTTPS call to run. If the full HTTP request and response can't be sent and received within the defined timeout in seconds, the stored procedure execution is halted, and an exception is raised. Timeout starts when the HTTP connection starts and ends when the response, and payload included if any, has been received. @timeout is a positive smallint with a default value 30. Accepted values: 1 to 230.
If the @retry_count parameter is specified, the @timeout parameter acts as the cumulative timeout for the procedure.
Indicate which DATABASE SCOPED CREDENTIAL object is used to inject authentication info in the HTTPS request. @credential is sysname with no default value.
Allow the response received from the called endpoint to be passed into the specified variable. @response is nvarchar(max).
Specifies how many times the stored procedure retries connecting to the specified endpoint if there's an error. @retry_count is a positive tinyint with a default value of 0. Accepted values: 0 to 10, with 0 bypassing all retry logic. The retry interval is determined using the Retry-After header if it is present. If the header is absent, the system applies an exponential backoff strategy for specific error codes. In all other cases, a default delay of 200 milliseconds is used.
Execution returns 0 if the HTTPS call was done and the HTTP status code received is a 2xx status code (Success). If the HTTP status code received isn't in the 2xx range, the return value is the HTTP status code received. If the HTTPS call can't be done at all, an exception is thrown.
Requires EXECUTE ANY EXTERNAL ENDPOINT database permission.
For example:
GRANT EXECUTE ANY EXTERNAL ENDPOINT TO [<PRINCIPAL>];The sp_invoke_external_rest_endpoint stored procedure is available in [!INCLUDE sssql25-md] and Azure SQL Managed Instance with the SQL Server 2025 or Always-up-to-date update policy and is disabled by default.
To enable the sp_invoke_external_rest_endpoint stored procedure in [!INCLUDE sssql25-md] and Azure SQL Managed Instance, run the following T-SQL code:
EXECUTE sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE WITH OVERRIDE;To execute sp_configure to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.
Note
sp_invoke_external_rest_endpoint is enabled by default in Azure SQL Database and SQL database in Fabric.
Response of the HTTP call and the resulting data sent back by the invoked endpoint is available through the @response output parameter. @response might contain a JSON document with the following schema:
{
"response": {
"status": {
"http": {
"code": "",
"description": ""
}
},
"headers": {}
},
"result": {}
}Specifically:
- response: a JSON object that contains the HTTP result and other response metadata.
- result: the JSON payload returned by the HTTP call. Omitted if the received HTTP result is a 204 (
No Content).
Or the @response might contain an XML document with the following schema:
<output>
<response>
<status>
<http code="" description=" " />
</status>
<headers>
<header key="" value="" />
<header key="" value="" />
</headers>
</response>
<result>
</result>
</output>Specifically:
- response: an XML object that contains the HTTP result and other response metadata.
- result: the XML payload returned by the HTTP call. Omitted if the received HTTP result is a 204 (
No Content).
In the response section, aside from the HTTP status code and description, the entire set of received response headers are provided in the headers object. The following example shows a response section in JSON (also the structure for text responses):
"response": {
"status": {
"http": {
"code": 200,
"description": "OK"
}
},
"headers": {
"Date": "Thu, 08 Sep 2022 21:51:22 GMT",
"Content-Length": "1345",
"Content-Type": "application\/json; charset=utf-8",
"Server": "Kestrel",
"Strict-Transport-Security": "max-age=31536000; includeSubDomains"
}
}And the following example shows a response section in XML:
<response>
<status>
<http code="200" description="OK" />
</status>
<headers>
<header key="Date" value="Tue, 01 Apr 1976 21:12:04 GMT" />
<header key="Content-Length" value="2112" />
<header key="Content-Type" value="application/xml" />
<header key="Server" value="Windows-Azure-Blob/1.0 Microsoft-HTTPAPI/2.0" />
<header key="x-ms-request-id" value="31536000-64bi-64bi-64bi-31536000" />
<header key="x-ms-version" value="2021-10-04" />
<header key="x-ms-creation-time" value="Wed, 19 Apr 2023 22:17:33 GMT" />
<header key="x-ms-server-encrypted" value="true" />
</headers>
</response>Important
This list only applies to Azure SQL Database and Azure SQL Managed Instance.
Only calls to endpoints for the following services are allowed:
| Azure Service | Domain |
|---|---|
| Azure Functions | *.azurewebsites.net |
| Azure Apps Service | *.azurewebsites.net |
| Azure App Service Environment | *.appserviceenvironment.net |
| Azure Static Web Apps | *.azurestaticapps.net |
| Azure Logic Apps | *.logic.azure.com |
| Azure Event Hubs | *.servicebus.windows.net |
| Azure Event Grid | *.eventgrid.azure.net |
| Azure AI Services | *.cognitiveservices.azure.com*.api.cognitive.microsoft.com |
| Azure OpenAI | *.openai.azure.com |
| PowerApps / Dataverse | *.api.crm.dynamics.com |
| Microsoft Dynamics | *.dynamics.com |
| Azure Container Instances | *.azurecontainer.io |
| Azure Container Apps | *.azurecontainerapps.io |
| Power BI | api.powerbi.com |
| Microsoft Graph | graph.microsoft.com |
| Analysis Services | *.asazure.windows.net |
| IoT Central | *.azureiotcentral.com |
| API Management | *.azure-api.net |
| Azure Blob Storage | *.blob.core.windows.net |
| Azure Files | *.file.core.windows.net |
| Azure Queue Storage | *.queue.core.windows.net |
| Azure Table Storage | *.table.core.windows.net |
| Azure Communication Services | *.communications.azure.com |
| Bing Search | api.bing.microsoft.com |
| Azure Key Vault | *.vault.azure.net |
| Azure AI Search | *.search.windows.net |
| Azure Maps | *.atlas.microsoft.com |
| Azure AI Translator | api.cognitive.microsofttranslator.com |
Outbound firewall rules for Azure SQL Database and Azure Synapse Analytics control mechanism can be used to further restrict outbound access to external endpoints.
Note
If you want to invoke a REST service that isn't within the allowed list, you can use API Management to securely expose the desired service and make it available to sp_invoke_external_rest_endpoint.
Payload, both when received and when sent, is UTF-8 encoded when sent over the wire. In that format, its size is limited to 100 MB.
The maximum URL length (generated after using the @url parameter and adding the specified credentials to the query string, if any) is 8 KB; the maximum query string length (query string + credential query string) is 4 KB.
The maximum request and response header size (all header fields: headers passed via @headers parameter + credential header + system supplied headers) is 8 KB.
The number of concurrent connections to external endpoints done via sp_invoke_external_rest_endpoint are capped to 10% of worker threads, with a maximum of 150 workers. On an single database throttling is enforced at the database level, while on an elastic pool throttling is enforced both at database and at pool level.
To check how many concurrent connections a database can sustain, run the following query:
SELECT [database_name],
DATABASEPROPERTYEX(DB_NAME(), 'ServiceObjective') AS service_level_objective,
[slo_name] AS service_level_objective_long,
[primary_group_max_outbound_connection_workers] AS max_database_outbound_connection,
[primary_pool_max_outbound_connection_workers] AS max_pool_outbound_connection
FROM sys.dm_user_db_resource_governance
WHERE database_id = DB_ID();If a new connection to an external endpoint using sp_invoke_external_rest_endpoint is tried when the maximum concurrent connections are already reached, error 10928 (or 10936 if you have reached elastic pools limits) is raised. For example:
Msg 10928, Level 16, State 4, Procedure sys.sp_invoke_external_rest_endpoint_internal, Line 1 [Batch Start Line 0]
Resource ID : 1. The outbound connections limit for the database is 20 and has been reached.
See 'https://docs.microsoft.com/azure/azure-sql/database/resource-limits-logical-server' for assistance.
Some REST endpoints require authentication in order to be properly invoked. Authentication can usually be done by passing some specific key-value pairs in the query string or in the HTTP headers set with the request.
It's possible to use DATABASE SCOPED CREDENTIAL to securely store authentication data (like a Bearer token for example) to be used by sp_invoke_external_rest_endpoint to call a protected endpoint. When creating the DATABASE SCOPED CREDENTIAL, use the IDENTITY parameter to specify what authentication data is passed to the invoked endpoint and how. IDENTITY supports four options:
HTTPEndpointHeaders: send specified authentication data using the Request HeadersHTTPEndpointQueryString: send specified authentication data using the Query StringManaged Identity: send the System Assigned Managed Identity using the request headersShared Access Signature: provide limited delegated access to resources via a signed URL (Also referred to as SAS)
The created DATABASE SCOPED CREDENTIAL can be used via the @credential parameter:
EXECUTE sp_invoke_external_rest_endpoint
@url = N'https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>?key1=value1',
@credential = [https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>];With this IDENTITY value, the DATABASE SCOPED CREDENTIAL is added to the request headers. The key-value pair containing the authentication information must be provided via the SECRET parameter using a flat JSON format. For example:
CREATE DATABASE SCOPED CREDENTIAL [https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>]
WITH IDENTITY = 'HTTPEndpointHeaders', SECRET = '{"x-functions-key":"<your-function-key-here>"}';With this IDENTITY value, the DATABASE SCOPED CREDENTIAL is added to the query string. The key-value pair containing the authentication information must be provided via the SECRET parameter using a flat JSON format. For example:
CREATE DATABASE SCOPED CREDENTIAL [https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>]
WITH IDENTITY = 'HTTPEndpointQueryString', SECRET = '{"code":"<your-function-key-here>"}';[!INCLUDE entra-authentication-options]
With this IDENTITY value, the authentication information for the DATABASE SCOPED CREDENTIAL is taken from the system-assigned managed identity of the logical server in which the database resides, and is passed in the request headers. The SECRET must be set to the APP_ID (or CLIENT_ID) used to configure Microsoft Entra authentication of the called endpoint. For an example, see Configure your App Service or Azure Functions app to use Microsoft Entra sign-in.
CREATE DATABASE SCOPED CREDENTIAL [https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>]
WITH IDENTITY = 'Managed Identity', SECRET = '{"resourceid":"<APP_ID>"}';Both system-assigned and user-assigned managed identities are supported:
-
If there's at least one user-assigned managed identity, the defined primary identity is used for authentication when using a managed identity-based database scoped credential.
-
If there's no user-assigned managed identity assigned, then the system-assigned managed identity is used, if it's enabled, for authentication when using a managed identity-based database scoped credential.
-
If both user-assigned and system-assigned managed identities are defined, the user-assigned managed identity is used.
-
If there's more than one user-assigned managed identity assigned, only the primary identity is used.
To use Managed Identity for authentication on [!INCLUDE sssql25-md], you must enable the option by using sp_configure with a user that is granted the ALTER SETTINGS server-level permission.
EXECUTE sp_configure 'allow server scoped db credentials', 1;
RECONFIGURE WITH OVERRIDE;The created DATABASE SCOPED CREDENTIAL must adhere to specific rules in order to be used with sp_invoke_external_rest_endpoint. The rules are as follows:
- Must be a valid URL
- The URL domain must be one of those domains included in the allowlist
- The URL must not contain a query string
- Protocol + Fully Qualified Domain Name (FQDN) of the called URL must match Protocol + FQDN of the credential name
- Each part of the called URL path must match completely with the respective part of URL path in the credential name
- The credential must point to a path that is more generic than the request URL. For example, a credential created for path
https://northwind.azurewebsite.net/customerscan't be used for the URLhttps://northwind.azurewebsite.net
RFC 3986 Section 6.2.2.1 states that "When a URI uses components of the generic syntax, the component syntax equivalence rules always apply; namely, that the scheme and host are case-insensitive," and RFC 7230 Section 2.7.3 mentions that "all other are compared in a case-sensitive manner."
As there's a collation rule set at the database level, the following logic is applied, to be coherent with the database collation rule and the RFC mentioned above. (The described rule could potentially be more restrictive than the RFC rules, for example if database is set to use a case-sensitive collation.):
- Check if the URL and credential match using the RFC, which means:
- Check the scheme and host using a case-insensitive collation (
Latin1_General_100_CI_AS_KS_WS_SC) - Check all other segments of the URL are compared in a case-sensitive collation (
Latin1_General_100_BIN2)
- Check the scheme and host using a case-insensitive collation (
- Check that the URL and credential match using the database collation rules (and without doing any URL encoding).
Database users who access a DATABASE SCOPED CREDENTIAL must have permission to use that credential.
To use the credential, a database user must have REFERENCES permission on a specific credential:
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[<CREDENTIAL_NAME>] TO [<PRINCIPAL>];When sp_invoke_external_rest_endpoint is waiting for the call to the invoked service to complete, it reports an HTTP_EXTERNAL_CONNECTION wait type.
Only endpoints that are configured to use HTTPS with TLS encryption protocol are supported.
sp_invoke_external_rest_endpoint doesn't automatically follow any HTTP redirect received as a response from the invoked endpoint.
sp_invoke_external_rest_endpoint automatically injects the following headers in the HTTP request:
- content-type: set to
application/json; charset=utf-8 - accept: set to
application/json - user-agent: set
<EDITION>/<PRODUCT VERSION>for example:SQL Azure/12.0.2000.8
While user-agent is always overwritten by the stored procedure, the content-type and accept header values can be user defined via the @headers parameter. Only the media type directive is allowed to be specified in the content-type and specifying the charset or boundary directives isn't possible.
Request and response payload supported media types
The following are accepted values for the header content-type.
- application/json
- application/vnd.microsoft.*.json
- application/xml
- application/vnd.microsoft.*.xml
- application/vnd.microsoft.*+xml
- application/x-www-form-urlencoded
- text/*
For the accept header, the following are the accepted values.
- application/json
- application/xml
- text/*
For more information on text header types, see the text type registry at IANA.
Note
If you're testing invocation of the REST endpoint with other tools, like cURL or any modern REST client like Insomnia, make sure to include the same headers that are automatically injected by sp_invoke_external_rest_endpoint to have the same behavior and results.
If setting the @retry_count parameter, the request will be retried when the following errors are encountered:
| HTTP Status Code | Error | Description |
|---|---|---|
| 408 | Request Timeout | The client didn't produce a request within the server's time limit or the server timed out waiting. |
| 429 | Too Many Requests | The client is being rate-limited. Retry time based on the "Retry-After" header value if provided. |
| 500 | Internal Server Error | Generic server error. |
| 502 | Bad Gateway | The server received an invalid response from a backend. |
| 503 | Service Unavailable | Indicates a temporary overload or downtime. |
| 504 | Gateway Timeout | The server didn't get a timely response. |
If you have to send a set of rows to a REST endpoint, for example to an Azure Function or to an event hub, it's recommended to batch the rows into a single JSON document, to avoid the HTTPS call overhead for each row sent. This can be done using the FOR JSON statement, for example:
-- create the payload
DECLARE @payload AS NVARCHAR (MAX);
SET @payload = (SELECT [object_id],
[name],
[column_id]
FROM sys.columns
FOR JSON AUTO);
-- invoke the REST endpoint
DECLARE @retcode AS INT, @response AS NVARCHAR (MAX);
EXECUTE
@retcode = sp_invoke_external_rest_endpoint
@url = '<REST_endpoint>',
@payload = @payload,
@response = @response OUTPUT;
-- return the result
SELECT @retcode,
@response;Here you can find some examples on how to use sp_invoke_external_rest_endpoint to integrate with common Azure Services like Azure Functions or Azure Event Hubs. More samples to integrate with other services can be found on GitHub.
The following example calls an Azure Function using an HTTP trigger binding allowing anonymous access.
DECLARE @ret AS INT, @response AS NVARCHAR (MAX);
EXECUTE
@ret = sp_invoke_external_rest_endpoint
@url = N'https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>?key1=value1',
@headers = N'{"header1":"value_a", "header2":"value2", "header1":"value_b"}',
@payload = N'{"some":{"data":"here"}}',
@response = @response OUTPUT;
SELECT @ret AS ReturnCode,
@response AS Response;The following example calls an Azure Function using an HTTP trigger binding configured to require an authorization key. The authorization key is passed in the x-function-key header as required by Azure Functions. For more information, see Azure Functions - API key authorization.
CREATE DATABASE SCOPED CREDENTIAL [https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>]
WITH IDENTITY = 'HTTPEndpointHeaders', SECRET = '{"x-functions-key":"<your-function-key-here>"}';
DECLARE @ret AS INT, @response AS NVARCHAR (MAX);
EXECUTE
@ret = sp_invoke_external_rest_endpoint
@url = N'https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>?key1=value1',
@headers = N'{"header1":"value_a", "header2":"value2", "header1":"value_b"}',
@credential = [https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>],
@payload = N'{"some":{"data":"here"}}',
@response = @response OUTPUT;
SELECT @ret AS ReturnCode,
@response AS Response;This example reads a file from Azure Blob Storage using a SAS token for authentication. The results are returned in XML, so you must use the header "Accept":"application/xml".
DECLARE @ret AS INT, @response AS NVARCHAR (MAX);
EXECUTE
@ret = sp_invoke_external_rest_endpoint
@url = N'https://blobby.blob.core.windows.net/datafiles/my_favorite_blobs.txt?sp=r&st=2023-07-28T19:56:07Z&se=2023-07-29T03:56:07Z&spr=https&sv=2022-11-02&sr=b&sig=XXXXXX1234XXXXXX6789XXXXX',
@headers = N'{"Accept":"application/xml"}',
@method = 'GET',
@response = @response OUTPUT;
SELECT @ret AS ReturnCode,
@response AS Response;This sample shows how you can send messages to Event Hubs using the Azure SQL Managed Identity. Make sure you have configured the System Managed Identity for the Azure SQL Database logical server hosting your database, for example:
az sql server update -g <resource-group> -n <azure-sql-server> --identity-type SystemAssigned
After that, configure Event Hubs to allow Azure SQL Server's Managed Identity to be able to send messages ("Azure Event Hubs Data Sender" role) to the desired event hub. For more information, see Use Event Hubs with managed identities.
Once this is done, you can use the Managed Identity identity name when defining the database scoped credential that is used by sp_invoke_external_rest_endpoint. As explained in Authenticate an application with Microsoft Entra ID to access Event Hubs resources, the resource name (or ID) to use when using Microsoft Entra authentication is https://eventhubs.azure.net:
CREATE DATABASE SCOPED CREDENTIAL [https://<EVENT-HUBS-NAME>.servicebus.windows.net]
WITH IDENTITY = 'Managed Identity', SECRET = '{"resourceid": "https://eventhubs.azure.net"}';
GO
DECLARE @Id AS UNIQUEIDENTIFIER = NEWID();
DECLARE @payload AS NVARCHAR (MAX) = (SELECT *
FROM (VALUES (@Id, 'John', 'Doe')) AS UserTable(UserId, FirstName, LastName)
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER);
DECLARE @url AS NVARCHAR (4000) = 'https://<EVENT-HUBS-NAME>.servicebus.windows.net/from-sql/messages';
DECLARE @headers AS NVARCHAR (4000) = N'{"BrokerProperties": "'
+ STRING_ESCAPE('{"PartitionKey": "'
+ CAST (@Id AS NVARCHAR (36)) + '"}', 'json') + '"}';
DECLARE @ret AS INT, @response AS NVARCHAR (MAX);
EXECUTE
@ret = sp_invoke_external_rest_endpoint
@url = @url,
@headers = @headers,
@credential = [https://<EVENT-HUBS-NAME>.servicebus.windows.net],
@payload = @payload,
@response = @response OUTPUT;
SELECT @ret AS ReturnCode,
@response AS Response;This example writes a file to an Azure File Storage using an Azure SQL Database scoped credentials for authentication and then returns the contents. The results are returned in XML, so you must use the header "Accept":"application/xml".
Start by creating a master key for the Azure SQL database. Replace <password> with a strong password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GOThen, create the database scoped credentials using the SAS token provided by the Azure Blob Storage Account. Replace <token> with the provided SAS token.
CREATE DATABASE SCOPED CREDENTIAL [filestore]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '<token>';
GONext, create the file and add text to it with the following two statements. Replace <domain> with the appropriate path.
DECLARE @payload AS NVARCHAR (MAX) = (SELECT *
FROM (VALUES ('Hello from Azure SQL!', sysdatetime())) AS payload([message], [timestamp])
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER);
DECLARE @response AS NVARCHAR (MAX);
DECLARE @url AS NVARCHAR (MAX);
DECLARE @headers AS NVARCHAR (1000);
DECLARE @len AS INT = len(@payload);
-- Create the file
SET @url = 'https://<domain>.file.core.windows.net/myfiles/test-me-from-azure-sql.json';
SET @headers = JSON_OBJECT('x-ms-type':'file', 'x-ms-content-length':CAST (@len AS VARCHAR (9)), 'Accept':'application/xml');
EXECUTE sp_invoke_external_rest_endpoint
@url = @url,
@method = 'PUT',
@headers = @headers,
@credential = [filestore],
@response = @response OUTPUT;
SELECT CAST (@response AS XML);
-- Add text to the file
SET @headers = JSON_OBJECT('x-ms-range':'bytes=0-' + CAST (@len - 1 AS VARCHAR (9)), 'x-ms-write':'update', 'Accept':'application/xml');
SET @url = 'https://<domain>.file.core.windows.net/myfiles/test-me-from-azure-sql.json';
SET @url += '?comp=range';
EXECUTE sp_invoke_external_rest_endpoint
@url = @url,
@method = 'PUT',
@headers = @headers,
@payload = @payload,
@credential = [filestore],
@response = @response OUTPUT;
SELECT CAST (@response AS XML);
GOFinally, use the following statement to read the file. Replace <domain> with the appropriate path.
DECLARE @response AS NVARCHAR (MAX);
DECLARE @url AS NVARCHAR (MAX) = 'https://<domain>.file.core.windows.net/myfiles/test-me-from-azure-sql.json';
EXECUTE sp_invoke_external_rest_endpoint
@url = @url,
@headers = '{"Accept":"application/xml"}',
@credential = [filestore],
@method = 'GET',
@response = @response OUTPUT;
SELECT CAST (@response AS XML);
GOThe following example calls an Azure OpenAI model using Managed identities in Microsoft Entra for Azure SQL. Replace <my-azure-openai-endpoint> and <model-deployment-name> with your Azure OpenAI endpoint and model name respectively, and make sure you have given the Managed Identity the Cognitive Services OpenAI User role in Azure OpenAI service.
CREATE DATABASE SCOPED CREDENTIAL [https://<my-azure-openai-endpoint>.openai.azure.com]
WITH IDENTITY = 'Managed Identity', SECRET = '{"resourceid":"https://cognitiveservices.azure.com"}';
GO
DECLARE @response AS NVARCHAR (MAX);
DECLARE @payload AS NVARCHAR (MAX) = JSON_OBJECT('input':'hello world');
EXECUTE sp_invoke_external_rest_endpoint
@url = 'https://<my-azure-openai-endpoint>.openai.azure.com/openai/deployments/<model-deployment-name>/embeddings?api-version=2024-08-01-preview',
@method = 'POST',
@credential = [https://<my-azure-openai-endpoint>.openai.azure.com],
@payload = @payload,
@response = @response OUTPUT;
SELECT json_query(@response, '$.result.data[0].embedding'); -- Assuming the called model is an embedding model- Resource management in Azure SQL Database
- sys.dm_resource_governor_resource_pools_history_ex
- sys.dm_resource_governor_workload_groups_history_ex
- sys.dm_user_db_resource_governance
- GRANT Database Permissions (Transact-SQL)
- CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
- API Management
- sp_invoke_external_rest_endpoint usage samples