通过

使用 SQL 查询数据

Microsoft Dataverse 业务层提供一个表格数据流（TDS）终结点，用于模拟 SQL 数据连接。 SQL 连接提供对目标 Dataverse 环境的表数据的只读访问权限，使你能够对 Dataverse 数据表执行 SQL 查询。 不提供数据的自定义视图。 Dataverse 终结点 SQL 连接使用 Dataverse 安全模型进行数据访问。 可以为用户有权访问的所有 Dataverse 表获取数据。

先决条件

在您的环境中必须启用 TDS 终结点 设置。 默认情况下，该设置处于启用状态。 详细信息：管理功能设置

若要防止数据外泄，请 为 TDS 终结点启用用户级访问控制。 将具有数据访问权限的最低特权安全角色分配给用户需要访问的表，并授予 允许用户访问 TDS 终结点 杂项权限。

应用程序支持

接下来将介绍对 Power BI 和 SQL Server Management Studio 的 TDS （SQL） 终结点应用程序支持。

SQL Server Management Studio

还可以将 SQL Server Management Studio 版本 18.12.1 或更高版本与 Dataverse 终结点 SQL 连接配合使用。 图中显示了将 SSMS 与 SQL 数据连接配合使用的示例。

展开的帐户表。

安全性和身份验证

仅支持Microsoft Entra ID 身份验证。 不支持 SQL 身份验证和 Windows 身份验证。 下图显示了如何在 SSMS 中登录到 SQL 连接的示例。 请注意，服务器名称是组织地址 URL。

连接对话框。

注释

需要启用端口 1433 和/或 5558 才能从客户端应用程序（如 SSMS）使用 TDS 终结点。 如果仅启用端口 5558，则用户必须在 SSMS 的 “连接到服务器 ”对话框中将该端口号追加到服务器名称 ， 例如：myorgname.crm.dynamics.com，5558。

有关终结点加密的信息： 传输中数据保护

表数据查询示例

下面是由 SSMS 编写的几个示例查询。 第一张图像显示使用别名和结果排序的简单查询。

select top 5 a.name as [VIP customer], a.address1_postalcode as [ZIP code] from account a order by a.address1_postalcode desc

使用别名和排序的简单查询。

下一个查询显示 JOIN。

select name, fullname from account a inner join contact c on a.primarycontactid = c.contactid

使用 JOIN 的另一个查询。

Power BI （正式版）

可以使用 Power Apps 中的“在 Power BI 中分析>数据表”选项（通过> 在 Power BI Desktop 中分析数据）。 详细信息： 在 Power BI Desktop 中查看表数据

注释

若要启用此功能，请参阅 “管理功能设置”中的 TDS 终结点设置。 启用后，应在 Power Apps 命令栏中看到“ 在 Power BI 中分析 ”按钮。

支持的操作和数据类型

执行尝试修改数据（即 INSERT、UPDATE）操作的任何操作在此只读的 SQL 数据连接中无效。 有关 Dataverse 终结点上支持的 SQL 操作的详细列表，请参阅 Dataverse SQL 与 Transact-SQL 的不同。

SQL 连接不支持以下 Dataverse 数据类型：binary、、imagesql_variant、varbinaryvirtual、HierarchyId、、、managedpropertyfilexml、partylist、。 timestampchoices 此外，当前暂不支持表类型“virtual”和“audit”。

小窍门

partylist 属性可以改为通过联接到 activityparty 表进行查询，如下例所示。

select act.activityid, act.subject, string_agg([to].partyidname, ', ')
from activitypointer as act
left outer join activityparty as [to] on act.activityid = [to].activityid and [to].participationtypemask = 2
group by act.activityid, act.subject

查找列类型的行为特性

Dataverse 查找列在结果集中表示为 <查找>ID 和 <查找>名称。

选择列类型行为

Dataverse 选择列在结果集中表示为 <选项>名称 和 <选项>标签。

小窍门

对选择列的标签进行更改后，需要发布表的自定义配置。

注释

在查询中包含大量选择标签会对性能产生重大影响。 最好尽可能使用少于 10 个标签。 由于选择标签已本地化，因此返回本地化字符串的成本更高。

报告的 SQL 版本

Dataverse TDS 端点通过 Dataverse 业务逻辑模拟 Microsoft SQL Server 只读查询功能。 Dataverse 返回当前 SQL Azure 版本 12.0.2000.8。select @@version

性能指南

通过 TDS 终结点检索数据时，应使用一些关键查询模式。 在下一节中介绍，这些查询模式管理结果集的性能和大小。

仅必要的列

生成查询时，仅返回所需的列。 此方法有助于执行查询，同时将结果传输回客户端应用程序。 一般情况下，建议将查询保留在不超过 100 列。

选择项列

选择列被展开为两列，这提高了可用性。 但是，重要的是必须针对选项列的值部分执行任何聚合和筛选。 值部分可以具有索引，并存储在基表中。 但是，标签部分（“choicecolumn”名称）单独存储，因此检索成本更高且无法编制索引。 使用大量选择标签列可能会生成性能较慢的查询。

使用 Top X

请务必在查询中使用 top 子句来防止尝试返回整个数据表。 例如，使用 Select Top 1000 accountid,name From account Where revenue > 50000 将结果限制为前 1,000 个帐户。

请勿使用 NOLOCK

生成查询时，请勿使用表提示 NOLOCK。 此提示可防止 Dataverse 优化查询。

局限性

Dataverse TDS 终结点不再具有硬的最大大小限制。 而是有固定的五（5）分钟超时。 在引入数据流之后，可以在固定的五（5）分钟超时时限内检索尽可能多的数据。 请考虑使用 Azure Synapse Link for Dataverse 和 数据流 等数据集成工具，以应对需要超过五（5）分钟才能完成的大型数据查询。 详细信息： 导入和导出数据

小窍门

为了帮助将返回的数据的大小保持在可接受的限制内，请尽可能少使用多行文本列和选择列。

警告

根据查询复杂性，可以将五（5）分钟超时调整为两（2）分钟。 例如，包含 SELECT *的 NESTED FROMs and/or JOINs 查询会自动将超时限制调整为两（2）分钟，因为这些查询在长时间运行时给服务器施加了太大的压力。 建议避免在 SQL 中使用这些模式以获得最佳性能。

查询结果中返回的日期的格式为世界时协调（UTC）。 以前，日期返回为本地时间。

使用 SQL 查询数据不会触发注册在RetrieveMultipleRequest 或 RetrieveRequest消息上的任何插件。 此类插件通常执行的查询或结果的任何重写都不会影响 SQL 查询。

使用 TDS 终结点的查询在服务保护 API 限制下执行。

TDS 终结点不能与弹性表一起使用。 详细信息： 弹性表

解决连接问题

让我们看看一些已知的错误条件以及如何解决它们。

注释

需要启用端口 1433 和/或 5558 才能从客户端应用程序（如 SSMS）使用 TDS 终结点。 如果仅启用端口 5558，则用户必须在 SSMS 的 “连接到服务器 ”对话框中将该端口号追加到服务器名称 ， 例如：myorgname.crm.dynamics.com，5558。

Authentication

Dataverse 终结点 SQL 连接仅支持Microsoft Entra ID 身份验证。 首选身份验证机制是具有多重身份验证的“Microsoft Entra ID – Universal”（MFA）。 但是，如果未配置 MFA，则“Microsoft Entra ID – Password”有效。 如果尝试使用其他形式的身份验证，可能会看到如下所示的错误。

  • 使用 Microsoft Entra ID – 集成 身份验证时返回的错误。

“登录失败：客户端身份验证方案”Anonymous“禁止 HTTP 请求。 RequestId: TDS;81d8a4f7-0d49-4d21-8f50-04364bddd370;2 时间: 2020-12-17T01:10:59.8628578Z (.Net SqlClient 数据提供程序)

  • 使用 SQL Server 身份验证时返回的错误。

“登录失败：请求未进行身份验证。 RequestId：TDS;918aa372-ccc4-438a-813e-91b086355343;1 时间：2020-12-17T01：13：14.4986739Z（.Net SqlClient 数据提供程序）

  • 使用 Windows 身份验证时返回的错误。

“登录失败：请求未进行身份验证。 RequestId：TDS;fda17c60-93f7-4d5a-ad79-7ddfbb917979;1 时间：2020-12-17T01：15：01.0497703Z（.Net SqlClient 数据提供程序）

被阻塞的端口

阻止的端口错误可能如下所示。

错误消息。

解决方案是验证来自客户端的 TCP 端口 1433 或 5558 是否已解除阻止。 使用以下方法之一取消阻止端口，如下所述。

使用 PowerShell 验证与 TDS 终结点的连接

  1. 打开 PowerShell 命令窗口。
  2. 运行 Test-connection 命令。
    Test-NetConnection -ComputerName <environment>.crm.dynamics.com -port 1433

如果连接成功，则返回一行“TcpTestSucceeded ： True”。

在某些情况下，可以直接在 IP 级别阻止流量。 若要验证 IP 地址是否也有效，请获取从上述域测试连接返回的 IP 地址，并将 ComputerName 参数值替换为 IP 地址。

  1. 请将上述命令返回的地址视为“RemoteAddress”
  2. 请运行 Test-NetConnection -ComputerName <RemoteAddress> -port 1433

此命令应返回“TcpTestSucceeded ： True”

建立 Telnet 会话到 TDS 终结点

  1. 在Microsoft Windows 计算机上，安装/启用 telnet。
    1. 选择开始。
    2. 选择“控制面板”。
    3. 选择 “程序和功能”。
    4. 选择“启用或关闭 Windows 功能”。
    5. 选择 Telnet 客户端 选项。
    6. 选择“确定”。 此时会显示一个对话框以确认安装。 telnet 命令现在应可用。
  2. 在命令窗口中运行 telnet 命令。
    telnet <environmentname>.crm.dynamics.com 1433

如果连接成功，您将进入活动的 telnet 会话。 如果失败，将收到错误：

“连接到 <environmentname.crm.dynamics.com>... 无法在端口 1433 上打开与主机的连接：连接失败。

此错误消息表示端口在客户端上被阻止。

从非 SSL 到 SSL 的端口重定向

由于端口重定向从 1433/5558 到 443，使用第三方应用程序时，TDS 连接可能会失败。 发生此失败是因为 SSL 检查规则可能会阻止通信，阻止的原因是从非 SSL 端口重定向到 SSL 端口。 解决方案是允许使用 IP 地址在 Web 代理上列出 Dataverse TDS 通信。

有关用于访问服务的正式 IP 地址值的信息，请参阅 IP-Addreses-Required。

连接到 Dataverse TDS 时，单单将主机名加入白名单是不够的，因为端口重定向（从端口 1433/5558 到 433）是通过 IP 地址而非主机名实现的。

另请参阅

Dataverse SQL 和 Transact-SQL 的区别虚拟表（实体）入门
使用 FetchXml服务保护 API 限制查询数据

  • Last updated on