使用 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.

注意

Oracle 尚未啟用 SSO,但已在開發且很快就會推出。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 ensure the following HANA-specific configurations are met for SAP:

  1. 確定 SAP HANA 伺服器執行的版本是 2.00.022* 或更高/更新版。Ensure the SAP HANA server is running version 2.00.022* or higher / later.
  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.

下列 SAP 修補程式及升級的連結可能有幫助。The following links to patches and upgrades from SAP may be useful. 請注意,您必須使用您的 SAP 支援帳戶登入下列資源,該 SAP 可能會變更或更新這些連結。Note that you must log in to the following resources using your SAP Support account, and that SAP may change or update these links.

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:

  • 如果從舊版內部部署資料閘道開始,請完全依序遵循下列文章中所述的所有五個步驟 (包括執行步驟 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
    • 如果您已經安裝內部部署資料閘道的預覽版本,有一個直接從閘道設定程式切換到服務帳戶的新 UI 引導式方法。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”,執行閘道服務的電腦名稱是 Machine1For 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 Management Console (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 Database),為了此範例,我們也假設下列設定和名稱: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 Database 服務的服務帳戶 (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 SPN。Optionally, 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: