使用 Kerberos 进行从 Power BI 到本地数据源的 SSO(单一登录)Use Kerberos for SSO (single sign-on) from Power BI to on-premises data sources

通过使用 Kerberos 配置本地数据网关,可以获得无缝的单一登录连接,从而使 Power BI 报表和仪表板可以从本地数据进行更新。You can get seamless single sign-on connectivity, enabling Power BI reports and dashboards to update from on-premises data, by configuring your on-premises data gateway with Kerberos. 本地数据网关使用用于连接到本地数据源的 DirectQuery 实现单一登录 (SSO)。The on-premises data gateway facilitates single sign-on (SSO) using DirectQuery, which it uses to connect to on-premises data sources.

目前支持以下数据源:SQL Server、SAP HANA 和 Teradata,全部基于 Kerberos 约束委派The following data sources are currently supported, SQL Server, SAP HANA, and Teradata, all based on Kerberos Constrained Delegation.

  • SQL ServerSQL Server
  • SAP HANASAP HANA
  • TeradataTeradata

当用户与 Power BI 服务中的 DirectQuery 报表进行交互时,每个交叉筛选、切片、排序和报表编辑操作都可能会导致针对基础的本地数据源执行实时查询。When a user interacts with a DirectQuery report in the Power BI Service, each cross-filter, slice, sorting, and report editing operation can result in queries executing live against the underlying on-premises data source. 当为数据源配置单一登录时,查询将以与 Power BI 交互的用户身份(即通过 Web 体验或 Power BI 移动应用)执行。When single sign-on is configured for the data source, queries execute under the identity of the user interacting with Power BI (that is, through the web experience or Power BI mobile apps). 因此,每个用户都可以精确地看到自己在基础数据源中拥有权限的数据 - 配置单一登录后,不同用户之间没有共享的数据缓存。Thereby, each user sees precisely the data for which they have permissions in the underlying data source – with single sign-on configured, there is no shared data caching across different users.

使用 SSO 运行查询 - 发生的步骤Running a query with SSO - steps that occur

使用 SSO 运行的查询由三个步骤组成,如下图所示。A query that runs with SSO consists of three steps, as shown in the following diagram.

备注

尚未启用 SSO for Oracle 功能,但正在开发此功能,该功能即将上市。SSO for Oracle is not enabled yet, but is under development and coming soon.

以下是有关这些步骤的其他详细信息:Here are additional details about those steps:

  1. 对于每个查询,当向配置的网关发送查询请求时,Power BI 服务包括用户主体名称 (UPN)。For each query, the Power BI service includes the user principal name (UPN) when sending a query request to the configured gateway.
  2. 网关需要将 Azure Active Directory UPN 映射到本地 Active Directory 标识。The gateway needs to map the Azure Active Directory UPN to a local Active Directory identity.

    a.a. 如果已配置 AAD DirSync(也称为“AAD Connect”),则会自动在网关中进行映射。If AAD DirSync (also known as AAD Connect) is configured, then the mapping works automatically in the gateway.

    b.b. 或者,网关可以通过在本地 Active Directory 域进行查找的方式来查找 Azure AD UPN,并将其映射到本地用户。Otherwise, the gateway can look up and map the Azure AD UPN to a local user by performing a lookup against the local Active Directory domain.

  3. 网关服务进程模拟映射的本地用户,打开与基础数据库的连接并发送查询。The gateway service process impersonates the mapped local user, opens the connection to the underlying database and sends the query. 网关不需要与数据库安装在同一台计算机上。The gateway does not need to be installed on the same machine as the database.

    • 只有在网关服务帐户是域帐户(或服务 SID)并且已配置 Kerberos 约束委派以使数据库接受来自网关服务帐户的 Kerberos 票据时,用户模拟和数据库连接才会成功。The user impersonation and connection to the database is only successful if the gateway service account is a domain account (or service SID), and if Kerberos constrained delegation was configured for the database to accept Kerberos tickets from the gateway service account.

    备注

    对于服务 SID,如果配置了 AAD DirSync / Connect 并且用户帐户已同步,则网关服务不需要在运行时执行本地 AD 查找,并且可以将本地服务 SID(而不是请求域帐户)用于网关服务。Regarding the service sid, if AAD DirSync / Connect is configured and user accounts are synchronized, the gateway service does not need perform local AD lookups at runtime, and you can use the local Service SID (instead of requiring a domain account) for the gateway service. 本文档所述的 Kerberos 约束委派配置步骤完全相同(只是基于服务 SID 而不是域帐户来应用)。The Kerberos constrained delegation configuration steps outlined in this document are the same (just applied based on the service SID, instead of domain account).

备注

若要为 SAP HANA 启用 SSO,需要向 SAP 应用以下两个 HANA 专属配置修复程序:To enable SSO for SAP HANA, you need to apply the following two HANA-specific configuration fixes to SAP:

  1. 使用 SAP 在 2017 年 10 月底发布的 HANA 修补程序 122.13 升级 SAP HANA 服务器。Upgrade SAP HANA server with SAP’s HANA Patch 122.13, released by SAP at the end of October 2017.
  2. 在网关计算机上,安装 SAP 的最新 HANA ODBC 驱动程序。On the gateway machine, install SAP’s latest HANA ODBC driver. 最低版本为 2017 年 8 月发布的 HANA ODBC 版本 2.00.020.00。The minimum version is HANA ODBC version 2.00.020.00 from August 2017.

Kerberos 配置不全导致的错误Errors from an insufficient Kerberos configuration

如果基础数据库服务器和网关未针对 Kerberos 约束委派正确配置,则可能会收到以下错误消息:If the underlying database server and gateway are not configured properly for Kerberos Constrained Delegation, you may receive the following error message:

与错误消息相关的技术详细信息可能如下所示:And the technical details associated with the error message may look like the following:

结果是由于 Kerberos 配置不全,网关无法正确模拟原始用户,并且数据库连接尝试失败。The result is that the because of insufficient Kerberos configuration, the gateway could not impersonate the originating user properly, and the database connection attempt failed.

准备 Kerberos 约束委派Preparing for Kerberos Constrained Delegation

必须配置多个项才能使 Kerberos 约束委派正常工作,其中包括服务主体名称 (SPN) 和服务帐户上的委派设置。Several items must be configured in order for Kerberos Constrained Delegation to work properly, including Service Principal Names (SPN) and delegation settings on service accounts.

先决条件 1:安装并配置本地数据网关Prerequisite 1: Install & configure the on-premises data gateway

此版本的本地数据网关支持就地升级,以及现有网关的设置接管。This release of the on-premises data gateway supports an in-place upgrade, as well as settings take-over of existing gateways.

先决条件 2:将网关 Windows 服务作为域帐户运行Prerequisite 2: Run the gateway Windows service as a domain account

在标准安装中,网关作为计算机本地服务帐户(具体而言,NT Service\PBIEgwService)运行,如下图所示:In a standard installation, the gateway runs as a machine-local service account (specifically, NT Service\PBIEgwService) such as what's shown in the following image:

若要启用 Kerberos 约束委派,网关必须作为域帐户运行,除非 AAD 已与本地 Active Directory 同步(使用 AAD DirSync/Connect)。To enable Kerberos Constrained Delegation, the gateway must run as a domain account, unless your AAD is already synchronized with your local Active Directory (using AAD DirSync/Connect). 为了使此帐户更改正常工作,有以下两个选项可供选择:For this account change to work correctly, you have two options:

  • 如果从以前版本的本地数据网关开始,请准确地按照以下文章中所述的顺序执行所有 5 个步骤(包括在步骤 3 中运行网关配置程序):If you started with a previous version of the on-premises data gateway, follow precisely all five steps in sequence (including running the gateway configurator in step 3) described in the following article:

    • 将网关服务帐户更改为域用户Changing the gateway service account to a domain user
    • 如果你已经安装了本地数据网关的预览版本,则会有一个新的用户界面指导方法直接从网关的配置程序中切换服务帐户。If you already installed the Preview version of the on-premises data gateway, there is a new UI-guided approach to switch service accounts directly from within the gateway’s configurator. 请参阅本文末尾处的“将本网关切换到域帐户”部分。See the Switching the gateway to a domain account section near the end of this article.

备注

如果配置了 AAD DirSync / Connect 并且用户帐户已同步,则网关服务不需要在运行时执行本地 AD 查找,并且可以将本地服务 SID(而不是请求域帐户)用于网关服务。If AAD DirSync / Connect is configured and user accounts are synchronized, the gateway service does not need to perform local AD lookups at runtime, and you can use the local Service SID (instead of requiring a domain account) for the gateway service. 本文所述的 Kerberos 约束委派配置步骤与该配置相同(它们只需基于服务 SID 应用,而不是域帐户)。The Kerberos Constrained Delegation configuration steps outlined in this article are the same as that configuration (they are simply applied based on the service SID, instead of domain account).

先决条件 3:具有域管理员权限才能配置 SPN (SetSPN) 和 Kerberos 约束委派设置Prerequisite 3: Have domain admin rights to configure SPNs (SetSPN) and Kerberos Constrained Delegation settings

虽然从技术上讲域管理员可以暂时或永久地允许其他人配置 SPN 和 Kerberos 委派,且无需域管理员权限,但这不是建议的方法。While it is technically possible for a domain administrator to temporarily or permanently allow rights to someone else to configure SPNs and Kerberos delegation, without requiring domain admin rights, that's not the recommended approach. 在下一节中,将详细介绍先决条件 3 所需的配置步骤。In the following section, the configuration steps necessary for Pre-requisite 3 in detail.

为网关和数据源配置 Kerberos 约束委派Configuring Kerberos Constrained Delegation for the gateway and data source

若要正确配置系统,我们需要配置或验证以下两项:To properly configure the system, we need to configure or validate the following two items:

  1. 如果需要,为网关服务域帐户配置 SPN(如果尚未创建)。If needed, configure an SPN for the gateway service domain account (if none are created yet).
  2. 在网关服务域帐户上配置委派设置。Configure delegation settings on the gateway service domain account.

请注意,你必须是域管理员才能执行这两个配置步骤。Note that you must be a domain administrator to perform those two configuration steps.

下列各节将依次介绍这些步骤。The following sections describe these steps in turn.

为网关服务帐户配置 SPNConfigure an SPN for the gateway service account

首先,确定是否已经为用作网关服务帐户的域帐户创建了 SPN,请执行以下步骤:First, determine whether an SPN was already created for the domain account used as the gateway service account, but following these steps:

  1. 以域管理员身份启动“Active Directory 用户和计算机”As a domain administrator, launch Active Directory Users and Computers
  2. 右键单击该域,选择“查找”,然后输入网关服务帐户的帐户名Right-click on the domain, select Find, and type in the account name of the gateway service account
  3. 在搜索结果中,右键单击网关服务帐户,然后选择“属性”。In the search result, right-click on the gateway service account and select Properties.

    • 如果 “委派” 选项卡在 “属性” 对话框中可见,则表明已创建 SPN,并且可以跳到有关配置委派设置的下一小节。If the Delegation tab is visible on the Properties dialog, then an SPN was already created and you can jump ahead to the next subsection about configuring Delegation settings.

如果“属性”对话框中没有“委派”选项卡,则可以在添加“委派”选项卡的帐户上手动创建一个 SPN(这是配置委派设置的最简单的方法)。If there is no Delegation tab on the Properties dialog, you can manually create an SPN on that account which adds the Delegation tab (that is the easiest way to configure delegation settings). 创建 SPN 可以使用 Windows 附带的 setspn 工具来完成(需要域管理员权限才能创建 SPN)。Creating an SPN can be done using the setspn tool that comes with Windows (you need domain admin rights to create the SPN).

例如,假设网关服务帐户为“PBIEgwTest\GatewaySvc”,并且运行网关服务的计算机名为 Machine1。For example, imagine the gateway service account is “PBIEgwTest\GatewaySvc”, and the machine name with the gateway service running is called Machine1. 若要为本示例中计算机的网关服务帐户设置 SPN,可以运行以下命令:To set the SPN for the gateway service account for that machine in this example, you would run the following command:

完成该步骤后,我们可以继续配置委派设置。With that step completed, we can move on to configuring delegation settings.

在网关服务帐户上配置委派设置Configure delegation settings on the gateway service account

第二个配置要求是网关服务帐户上的委派设置。The second configuration requirement is the delegation settings on the gateway service account. 有多种工具可以用来执行这些步骤。There are multiple tools you can use to perform these steps. 在本文中,我们将使用“Active Directory 用户和计算机” ,这是一个 Microsoft 管理控制台 (MMC) 管理单元,可用于管理和发布目录中的信息,并且默认在域控制器上可用。In this article, we'll use Active Directory Users and Computers, which is a Microsoft Management Console (MMC) snap-in that you can use to administer and publish information in the directory, and available on domain controllers by default. 也可以通过其他计算机上的“Windows 功能”配置来启用它。You can also enable it through Windows Feature configuration on other machines.

我们需要通过协议转换来配置“Kerberos 约束委派”。We need to configure Kerberos Constrained Delegation with protocol transiting. 通过约束委派,必须显式设置要委派哪些服务 - 例如,只有 SQL Server 或 SAP HANA 服务器才能接受来自网关服务帐户的委派调用。With constrained delegation, you must be explicit with which services you want to delegate to – for example, only your SQL Server or your SAP HANA server will accept delegation calls from the gateway service account.

本节假定你已经为基础数据源(例如 SQL Server、SAP HANA、Teradata 等)配置了 SPN。This section assumes you have already configured SPNs for your underlying data sources (such as SQL Server, SAP HANA, Teradata, so on). 若要了解如何配置这些数据源服务器 SPN,请参阅相应数据库服务器的技术文档。To learn how to configure those data source server SPNs, please refer to technical documentation for the respective database server. 另外,也可以查看描述你的应用需要哪种 SPN?的博客文章。You can also look at the blog post that describes What SPN does your app require?

在下面的步骤中,我们假定一个本地环境具有两台计算机:一台网关计算机和一台数据库服务器(SQL Server 数据库),为了此示例,我们还假定以下设置和名称:In the following steps we assume an on-premises environment with two machines: a gateway machine and a database server (SQL Server database), and for the sake of this example we'll also assume the following settings and names:

  • 网关计算机名:PBIEgwTestGWGateway machine name: PBIEgwTestGW
  • 网关服务帐户:PBIEgwTest\GatewaySvc(帐户显示名:网关连接器)Gateway service account: PBIEgwTest\GatewaySvc (account display name: Gateway Connector)
  • SQL Server 数据源计算机名:PBIEgwTestSQLSQL Server data source machine name: PBIEgwTestSQL
  • SQL Server 数据源服务帐户:PBIEgwTest\SQLServiceSQL Server data source service account: PBIEgwTest\SQLService

给定这些示例名和设置,配置步骤如下:Given those example names and settings, the configuration steps are the following:

  1. 通过域管理员权限,启动“Active Directory用户和计算机”。With domain administrator rights, launch Active Directory Users and Computers.
  2. 右键单击网关服务帐户 (PBIEgwTest\GatewaySvc),然后选择“属性”。Right-click on the gateway service account (PBIEgwTest\GatewaySvc) and select Properties.
  3. 选择“委派”选项卡。Select the Delegation tab.
  4. 选择“仅信任此计算机来委派指定的服务”。Select Trust this computer for delegation to specified services only.
  5. 选择“使用任何身份验证协议”。Select Use any authentication protocol.
  6. 在“可以由此帐户提供委派凭据的服务:”下选择“添加”。Under the Services to which this account can present delegated credentials: select Add.
  7. 在新对话框中,选择“用户或计算机”。In the new dialog, select Users or Computers.
  8. 输入 SQL Server 数据库服务 (PBIEgwTest\SQLService) 的服务帐户,然后选择“确定”。Enter the service account for the SQL Server Database service (PBIEgwTest\SQLService) and select OK.
  9. 选择你为数据库服务器创建的 SPN。Select the SPN that you created for the database server. 在我们的示例中,SPN 将以 MSSQLSvc 开头。In our example, the SPN will begin with MSSQLSvc. 如果你为数据库服务添加了 FQDN 和 NetBIOS SPN,请同时选择两者。If you added both the FQDN and the NetBIOS SPN for your database service, select both. 你可能只会看到其中一个。You may only see one.
  10. 选择确定Select OK. 现在,列表中应该会显示 SPN。You should see the SPN in the list now.
  11. 或者,你可以选择“已展开”以在以下位置同时显示 FQDN 和 NetBIOS SPNOptionally, you can select Expanded to show both the FQDN and NetBIOS SPN in
  12. 如果你选中了“已展开”,则对话框将如下所示。The dialog will look similar to the following if you checked Expanded.

  13. 选择确定Select OK.

    最后,在运行网关服务(在我们的示例中为 PBIEgwTestGW)的计算机上,必须为网关服务帐户授予本地策略“身份验证后模拟客户端”。Finally, on the machine running the gateway service (PBIEgwTestGW in our example), the gateway service account must be granted the local policy “Impersonate a client after authentication”. 可以使用本地组策略编辑器 (gpedit) 执行/验证此操作。You can perform/verify this with the Local Group Policy Editor (gpedit).

  14. 在网关计算机上运行:gpedit.mscOn the gateway machine, run: gpedit.msc
  15. 导航到“本地计算机策略”>“计算机配置”>“Windows设置”>“安全设置”>“本地策略”>“用户权限分配”,如下图所示。Navigate to Local Computer Policy > Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment, as shown in the following image.

  16. 从“用户权限分配”下的策略列表中,选择“身份验证后模拟客户端”。From the list of policies under User Rights Assignment, select Impersonate a client after authentication.

    右键单击并打开“身份验证后模拟客户端”的“属性”,并检查帐户列表。Right-click and open the Properties for Impersonate a client after authentication and check the list of accounts. 其中必须包括网关服务帐户 (PBIEgwTest\GatewaySvc)。It must include the gateway service account (PBIEgwTest\GatewaySvc).

  17. 从“用户权限分配”下的策略列表中,选择“以操作系统方式执行”(SeTcbPrivilege)。From the list of policies under User Rights Assignment, select Act as part of the operating system (SeTcbPrivilege). 确保网关服务帐户也包括在帐户列表中。Ensure that the gateway service account is included in the list of accounts as well.
  18. 重启“本地数据网关”服务进程。Restart the on-premises data gateway service process.

运行 Power BI 报表Running a Power BI report

完成本文前述的所有配置步骤后,可以使用 Power BI 中的“管理网关”页配置数据源,并在其“高级设置”下启用 SSO,然后发布绑定到该数据源的报表和数据集。After all the configuration steps outlined earlier in this article have been completed, you can use the Manage Gateway page in Power BI to configure the data source, and under its Advanced Settings, enable SSO, then publish reports and datasets binding to that data source.

此配置将在大多数情况下有效。This configuration will work in most cases. 但是,使用 Kerberos 时,根据你的环境可以有不同的配置。However, with Kerberos there can be different configurations depending on your environment. 如果报表仍无法加载,则需要联系你的域管理员进一步调查。If the report still won't load, you'll need to contact your domain administrator to investigate further.

将网关切换到域帐户Switching the gateway to a domain account

在本文前面部分,我们讨论了使用本地数据网关用户界面将网关从本地服务帐户切换为作为域帐户运行。Earlier in this article, we discussed switching the gateway from a local service account to run as a domain account, using the on-premises data gateway user interface. 以下是执行此操作必需的步骤。Here are the steps necessary to do so.

  1. 启动“本地数据网关”配置工具。Launch the on-premises data gateway configuration tool.

  2. 选择主页上的“登录”按钮,然后使用你的 Power BI 帐户登录。Select the Sign-in button on the main page, and sign in with your Power BI account.
  3. 登录完成后,选择“服务设置”选项卡。After sign-in is completed, select the Service Settings tab.
  4. 单击“更改帐户”以开始指导说明,如下图所示。Click Change account to start the guided walk-through, as shown in the following figure.

后续步骤Next steps

有关“本地数据网关”和 DirectQuery 的详细信息,请查看以下资源:For more information about the on-premises data gateway and DirectQuery, check out the following resources: