sp_invoke_external_rest_endpoint (Transact-SQL)

适用于:Azure SQL 数据库

sp_invoke_external_rest_endpoint存储过程调用作为过程的输入参数提供的 HTTPS REST 终结点。

语法

Transact-SQL 语法约定

EXEC @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 ]

参数

[ @url = ] N'url'

要调用的 HTTPS REST 终结点的 URL。 @url为 nvarchar(4000),没有默认值。

[ @payload = ] N'request_payload'

JSON、XML 或 TEXT 格式的 Unicode 字符串,其中包含要发送到 HTTPS REST 终结点的有效负载。 有效负载必须是有效的 JSON 文档、格式良好的 XML 文档或文本。 @payload为 nvarchar(max),没有默认值。

[ @headers = ] N'headers'

必须作为请求的一部分发送到 HTTPS REST 终结点的标头。 必须使用平面 JSON(不带嵌套结构的 JSON 文档)格式指定标头。 即使显式传入了 @headers 参数,在禁止标头名称列表中定义的标头也会被忽略;在启动 HTTPS 请求时,这些标头的值将被取消卡或替换为系统提供的值。

@headers参数为 nvarchar(4000),没有默认值。

[ @method = ] N'method'

用于调用 URL 的 HTTP 方法。 必须是以下值之一:GET、、POSTPUTPATCHDELETEHEAD@method为 nvarchar(6),默认值POST为 nvarchar。

[ @timeout = ]

允许 HTTPS 调用运行的时间(以秒为单位)。 如果无法在定义的超时时间内发送和接收完整的 HTTP 请求和响应,则存储过程执行将停止,并引发异常。 HTTP 连接在响应开始和结束时以及已收到有效负载(如果有)时,超时将启动。 @timeout是默认值为 30 的正小数。 接受的值:1 到 230。

[ @credential = ] credential

指示哪个 DATABA标准版 SCOPED CREDENTIAL 对象用于在 HTTPS 请求中注入身份验证信息。 @credential没有默认值的 sysname

@response 输出

允许从调用的终结点接收的响应传递到指定的变量中。 @response为 nvarchar(max)。

返回值

如果 HTTPS 调用完成并且收到的 HTTP 状态代码为 2xx 状态代码(Success),则执行将返回0。 如果收到的 HTTP 状态代码不在 2xx 范围内,则返回值将是收到的 HTTP 状态代码。 如果根本无法执行 HTTPS 调用,将引发异常。

权限

需要 EXECUTE ANY EXTERNAL ENDPOINT 数据库权限。

例如:

GRANT EXECUTE ANY EXTERNAL ENDPOINT TO [<PRINCIPAL>];

响应格式

可以通过 @response 输出参数响应 HTTP 调用和调用终结点发送的生成的数据。 @response可能包含具有以下架构的 JSON 文档:

{
  "response": {
    "status": {
      "http": {
        "code": "",
        "description": ""
      }
    },
    "headers": {}
  },
  "result": {}
}

具体而言:

  • 响应:包含 HTTP 结果和其他响应元数据的 JSON 对象。
  • 结果:HTTP 调用返回的 JSON 有效负载。 如果收到的 HTTP 结果为 204(No Content),则省略。

或者@response可能包含具有以下架构的 XML 文档:

<output>
    <response>
        <status>
            <http code="" description=" " />
        </status>
        <headers>
            <header key="" value="" />
            <header key="" value="" />
        </headers>
    </response>
    <result>
    </result>
</output>

具体而言:

  • 响应:包含 HTTP 结果和其他响应元数据的 XML 对象。
  • 结果:HTTP 调用返回的 XML 有效负载。 如果收到的 HTTP 结果为 204(No Content),则省略。

在本节中 response ,除了 HTTP 状态代码和说明之外,将在对象中 headers 提供整个接收的响应标头集。 以下示例显示了 JSON 中的一个 response 部分(也是文本响应的结构):

"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"
    }
  }

以下示例演示 XML 中的一个 response 部分:

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

允许的终结点

仅允许调用以下服务中的终结点:

Azure 服务
Azure Functions *.azurewebsites.net
Azure 应用服务 *.azurewebsites.net
Azure 应用服务环境 *.appserviceenvironment.net
Azure 静态 Web 应用 *.azurestaticapps.net
Azure 逻辑应用 *.logic.azure.com
Azure 事件中心 *.servicebus.windows.net
Azure 事件网格 *.eventgrid.azure.net
Azure 认知服务 *.cognitiveservices.azure.com
Azure OpenAI *.openai.azure.com
PowerApps /Dataverse *.api.crm.dynamics.com
Microsoft Dynamics *.dynamics.com
Azure 容器实例 *.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 管理 *.azure-api.net
Azure Blob 存储 \* .blob.core.windows.net
Azure 文件 *.file.core.windows.net
Azure 队列存储 *.queue.core.windows.net
Azure 表存储 *.table.core.windows.net
Azure 通信服务 *.communications.azure.com
必应搜索 api.bing.microsoft.com
Azure Key Vault *.vault.azure.net
Azure AI 搜索 *.search.windows.net

出站防火墙规则 控制机制可用于进一步限制对外部终结点的出站访问。

注意

如果要调用不在允许列表中的 REST 服务,可以使用API 管理安全地公开所需的服务并使其可供sp_invoke_external_rest_endpoint使用。

限制

有效负载大小

在接收时和发送时,有效负载在通过线路发送时都经过 UTF-8 编码。 在该格式中,其大小限制为 100 MB。

URL 长度

最大 URL 长度(使用 @url 参数生成,并将指定的凭据添加到查询字符串(如果有)为 8 知识库(KB);最大查询字符串长度(查询字符串 + 凭据查询字符串)为 4 知识库(KB)。

标头大小

最大请求和响应标头大小(所有标头字段:通过@headers参数 + 凭据标头 + 系统提供的标头传递的标头)为 8 知识库(KB)。

限制

通过 sp_invoke_external_rest_endpoint 外部终结点进行的并发连接数上限为工作线程的 10%,最多有 150 个辅助角色。 在数据库级别强制执行单一 数据库 限制,同时 在数据库和池级别强制实施弹性池 限制。

若要检查数据库可以维持的并发连接数,请运行以下查询:

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();

如果已达到最大并发连接数时尝试使用 sp_invoke_external_rest_endpoint 的外部终结点的新连接,将引发错误 10928(如果已达到弹性池限制,则为 10936)。 例如:

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.

凭据

某些 REST 终结点需要身份验证才能正确调用。 通常可以通过在查询字符串或请求设置的 HTTP 标头中传递一些特定的键值对来完成身份验证。

可以使用 DATABA标准版 SCOPED 凭据安全地存储身份验证数据(例如持有者令牌),以便调用sp_invoke_external_rest_endpoint受保护的终结点。 创建 DATABA标准版 SCOPED CREDENTIAL 时,请使用 IDENTITY 参数指定将传递给调用的终结点的身份验证数据以及操作方法。 IDENTITY 支持三个选项:

  • HTTPEndpointHeaders:使用 请求标头发送指定的身份验证数据
  • HTTPEndpointQueryString:使用 查询字符串发送指定的身份验证数据
  • Managed Identity:使用请求标头发送系统分配的托管标识

创建的 DATABA标准版 SCOPED CREDENTIAL 可通过 @credential 参数使用

EXEC 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>]

使用此 IDENTITY 值,DATABA标准版 SCOPED CREDENTIAL 将添加到请求标头。 包含身份验证信息的键值对必须通过平面 JSON 格式的 标准版CRET 参数提供。 例如:

CREATE DATABASE SCOPED CREDENTIAL [https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>]
WITH IDENTITY = 'HTTPEndpointHeaders', SECRET = '{"x-functions-key":"<your-function-key-here>"}';

凭据名称规则

创建的 DATABA标准版 SCOPED CREDENTIAL 必须遵循特定规则才能用于 sp_invoke_external_rest_endpoint。 规则如下:

  • 必须是有效的 URI
  • URL 域必须是允许列表中包含的域之一
  • URL 不得包含查询字符串
  • 调用 URL 的协议 + 完全限定域名 (FQDN) 必须与凭据名称的 Protocol + FQDN 匹配
  • 调用的 URL 路径的每个部分必须与凭据名称中相应 URL 路径的相应部分完全匹配
  • 凭据必须指向比请求 URL 更通用的路径。 例如,为路径 https://northwind.azurewebsite.net/customers 创建的凭据不能用于 URL https://northwind.azurewebsite.net

排序规则和凭据名称规则

RFC 3986 第 6.2.2.1 节指出,“当 URI 使用泛型语法的组件时,组件语法等价规则始终适用:即方案与主机不区分大小写“,RFC 7230 第 2.7.3 节提及”以区分大小写的方式进行比较”。

由于数据库级别设置了排序规则,因此将应用以下逻辑,以便与数据库排序规则和上述 RFC 提及一致。 (所描述的规则可能比 RFC 规则更严格,例如,如果数据库设置为使用区分大小写的排序规则)。

  1. 使用 RFC 检查 URL 和凭据是否匹配,这意味着:
    • 使用不区分大小写的排序规则检查方案和主机 (Latin1_General_100_CI_AS_KS_WS_SC
    • 检查 URL 的所有其他段是否在区分大小写的排序规则中进行比较(Latin1_General_100_BIN2
  2. 使用数据库排序规则检查 URL 和凭据是否匹配(无需执行任何 URL 编码)。

授予使用凭据的权限

访问 DATABA 的数据库用户标准版 SCOPED CREDENTIAL 必须有权使用该凭据。

若要使用凭据,数据库用户必须具有 REFERENCES 特定凭据的权限:

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[<CREDENTIAL_NAME>] TO [<PRINCIPAL>];

注解

等待类型

当等待调用的服务完成调用时 sp_invoke_external_rest_endpoint ,它将报告HTTP_EXTERNAL_CONNECTION等待类型。

HTTPS 和 TLS

仅支持配置为至少使用 TLS 1.2 加密协议的 HTTPS 的终结点。

HTTP 重定向

sp_invoke_external_rest_endpoint 不会自动遵循从调用的终结点接收的任何 HTTP 重定向作为响应。

HTTP 头

sp_invoke_external_rest_endpoint 将自动在 HTTP 请求中注入以下标头:

  • content-type:设置为 application/json; charset=utf-8
  • accept:设置为 application/json
  • user-agent:设置 <EDITION>/<PRODUCT VERSION> 例如: SQL Azure/12.0.2000.8

虽然用户代理将始终被存储过程覆盖,但内容类型和接受标头值可以通过 @headers 参数定义。 仅允许在内容类型中指定媒体类型指令,并且不能指定字符集或边界指令。

请求和响应有效负载支持的 媒体类型

以下是标头 内容类型的接受值。

  • application/json
  • application/vnd.microsoft.*.json
  • application/xml
  • application/vnd.microsoft.*.xml
  • application/vnd.microsoft.*+xml
  • application/x-www-form-urlencoded
  • 文本/*

对于 accept 标头,以下是接受的值。

  • application/json
  • application/xml
  • 文本/*

有关文本标头类型的详细信息,请参阅 IANA 中的文本类型注册表。

注意

如果要使用其他工具(如 cURL 或任何新式 REST 客户端(如 Insomnia)测试 REST 终结点的调用,请确保包含自动注入sp_invoke_external_rest_endpoint的相同标头,使其具有相同的行为和结果。

最佳实践

使用批处理技术

如果需要将一组行发送到 REST 终结点(例如,发送到 Azure 函数或事件中心),建议将行批处理到单个 JSON 文档中,以避免发送的每一行的 HTTPS 调用开销。 可以使用语句完成 FOR JSON 此操作,例如:

-- 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 INT,
    @response AS NVARCHAR(MAX);

EXEC @retcode = sp_invoke_external_rest_endpoint @url = '<REST_endpoint>',
    @payload = @payload,
    @response = @response OUTPUT;

-- return the result
SELECT @retcode, @response;

示例

可在此处找到有关如何sp_invoke_external_rest_endpoint与常见 Azure 服务(如 Azure Functions 或 Azure 事件中心)集成的一些示例。 可以在 GitHub找到更多与其他服务集成的示例。

A. 使用不使用身份验证的 HTTP 触发器绑定调用 Azure 函数

以下示例使用允许匿名访问的 HTTP 触发器绑定调用 Azure 函数。

DECLARE @ret INT, @response NVARCHAR(MAX);

EXEC @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;

B. 使用具有授权密钥的 HTTP 触发器绑定调用 Azure 函数

以下示例使用配置为需要授权密钥的 HTTP 触发器绑定调用 Azure 函数。 授权密钥将按照 Azure Functions 的要求传入 x-function-key 标头中。 有关详细信息,请参阅 Azure Functions - API 密钥授权

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 INT, @response NVARCHAR(MAX);

EXEC @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;

°C 使用 SAS 令牌从Azure Blob 存储读取文件的内容

此示例使用 SAS 令牌从Azure Blob 存储读取文件进行身份验证。 结果将在 XML 中返回,因此需要使用标头 "Accept":"application/xml"

DECLARE @ret INT, @response NVARCHAR(MAX);

EXEC @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;

D. 使用Azure SQL 数据库托管标识将消息发送到事件中心

此示例演示如何使用 Azure SQL 托管标识将消息发送到事件中心。 确保已为托管数据库的Azure SQL 数据库逻辑服务器配置系统托管标识,例如:

az sql server update -g <resource-group> -n <azure-sql-server> --identity-type SystemAssigned

之后,将事件中心配置为允许 Azure SQL Server 的托管标识能够将消息(“Azure 事件中心数据发送者”角色)发送到所需的事件中心。 有关详细信息,请参阅 将事件中心与托管标识配合使用。

完成此操作后,可以在定义要使用的sp_invoke_external_rest_endpoint数据库作用域凭据时使用Managed Identity标识名称。 如使用 Microsoft Entra ID 对应用程序进行身份验证以访问事件中心资源中所述,使用 Microsoft Entra 身份验证时要使用的资源名称(或 ID)为 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 UNIQUEIDENTIFIER = NEWID();
DECLARE @payload NVARCHAR(MAX) = (
        SELECT *
        FROM (
            VALUES (@Id, 'John', 'Doe')
            ) AS UserTable(UserId, FirstName, LastName)
        FOR JSON AUTO,
            WITHOUT_ARRAY_WRAPPER
        )
DECLARE @url NVARCHAR(4000) = 'https://<EVENT-HUBS-NAME>.servicebus.windows.net/from-sql/messages';
DECLARE @headers NVARCHAR(4000) = N'{"BrokerProperties": "' + STRING_ESCAPE('{"PartitionKey": "' + CAST(@Id AS NVARCHAR(36)) + '"}', 'json') + '"}'
DECLARE @ret INT, @response NVARCHAR(MAX);

EXEC @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;

E. 使用Azure SQL 数据库作用域凭据读取文件并将其写入 Azure 文件存储

此示例使用Azure SQL 数据库作用域凭据将文件写入 Azure 文件存储进行身份验证,然后返回内容。 结果将在 XML 中返回,因此需要使用标头 "Accept":"application/xml"

首先为Azure SQL 数据库创建主密钥

create master key encryption by password = '2112templesmlm2BTS21.qwqw!@0dvd'
go

然后,使用 Azure Blob 存储 帐户提供的 SAS 令牌创建数据库范围的凭据。

create database scoped credential [filestore]
with identity='SHARED ACCESS SIGNATURE',
secret='sv=2022-11-02&ss=bfqt&srt=sco&sp=seespotrun&se=2023-08-03T02:21:25Z&st=2023-08-02T18:21:25Z&spr=https&sig=WWwwWWwwWWYaKCheeseNXCCCCCCDDDDDSSSSSU%3D'
go

接下来,创建文件并使用以下两个语句向其添加文本:

declare @payload nvarchar(max) = (select * from (values('Hello from Azure SQL!', sysdatetime())) payload([message], [timestamp])for json auto, without_array_wrapper)
declare @response nvarchar(max), @url nvarchar(max), @headers nvarchar(1000);
declare @len int = len(@payload)

-- Create the File
set @url = 'https://myfiles.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')
exec 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://myfiles.file.core.windows.net/myfiles/test-me-from-azure-sql.json'
set @url += '?comp=range'
exec sp_invoke_external_rest_endpoint
    @url = @url,
    @method = 'PUT',
    @headers = @headers,
    @payload = @payload,
    @credential = [filestore],
    @response = @response output
select cast(@response as xml)
go

最后,使用以下语句读取文件

declare @response nvarchar(max);
declare @url nvarchar(max) = 'https://myfiles.file.core.windows.net/myfiles/test-me-from-azure-sql.json'
exec sp_invoke_external_rest_endpoint
    @url = @url,
    @headers = '{"Accept":"application/xml"}',
    @credential = [filestore],
    @method = 'GET',
    @response = @response output
select cast(@response as xml)
go