Use EffectiveUserName with Excel Services (SharePoint Server 2013)Use EffectiveUserName with Excel Services (SharePoint Server 2013)

摘要:重新整理資料連線的活頁簿中使用的活頁簿檢視者的 Windows 身分識別的 SharePoint Server 2013 的 Excel Services 中使用 Analysis Services EffectiveUserName 功能。Summary: Use the Analysis Services EffectiveUserName feature to refresh data-connected workbooks in Excel Services in SharePoint Server 2013 using the workbook viewer's Windows identity.


此案例僅適用於 Excel Services 與 SharePoint Server 2013 Enterprise 上 Analysis Services 資料來源。This scenario applies only to Excel Services with an Analysis Services data source on SharePoint Server 2013 Enterprise.

案例概觀Scenario overview

使用 Excel Services 的 EffectiveUserName 功能可讓使用者檢視的報告傳遞至 SQL Server Analysis Services 的身分識別。這可讓您指定適當的指定使用者的資料存取層級上的 OLAP cube 本身擷取。Using the EffectiveUserName feature with Excel Services allows the identity of a user viewing a report to be passed to SQL Server Analysis Services. This allows you to specify the appropriate level of data access for a given user on the OLAP cube itself.

使用 EffectiveUserName 選項允許將 SQL Server Analysis services 的使用者身分識別傳遞給而不需要設定安全認證儲存或 Kerberos 委派。Using the EffectiveUserName option allows passing the user's identity to SQL Server Analysis Services without the need to configure Secure Store or Kerberos delegation.

開始之前Before you begin

開始之前,請閱讀下列有關權限及軟體需求的資訊。Before starting, read the following information about permissions and software requirements.

設定 Excel Services 通用設定Configure Excel Services Global Settings

設定的 EffectiveUserName 功能的第一個步驟是啟用 Excel Services 通用設定] 中的功能。使用下列程序可啟用的 EffectiveUserName 功能。The first step in configuring the EffectiveUserName feature is to enable the feature in Excel Services global settings. Use the following procedure to enable the EffectiveUserName feature.

若要啟用 Excel Services 的 EffectiveUserNameTo enable EffectiveUserName in Excel Services

  1. 在管理中心的應用程式管理] 下按一下 [管理服務應用程式]。In Central Administration, under Application Management, click Manage service applications.

  2. 按一下 [Excel Services] 服務應用程式。Click the Excel Services service application.

  3. 按一下 [通用設定Click Global Settings.

  4. 在 [Excel Services 應用程式設定] 頁面上,[外部資料] 區段中選取 [使用 EffectiveUserName 屬性] 核取方塊。On the Excel Services Application Settings page, in the External Data section, select the Use the EffectiveUserName property check box.

  5. 按一下 [ OK ]。Click OK.

設定 Analysis Services 存取Configure Analysis Services access

使用 EffectiveUserName 功能需要執行 Excel Services 應用程式集區的帳戶是 Analysis Services 管理員。Using the EffectiveUserName feature requires that the account that is running the Excel Services application pool be an Analysis Services administrator.

如果您不知道哪個帳戶伺服器陣列中執行 Excel Services 應用程式集區,請使用下列程序來判定帳戶。如果您知道帳戶,請略過此程序。If you do not know what account is running the Excel Services application pool in your farm, use the following procedure to determine the account. If you know the account, skip this procedure.

若要判斷 Excel Services 應用程式集區帳戶To determine the Excel Services application pool account

  1. 在 SharePoint 管理中心網站首頁上,按一下 [安全性]。On the SharePoint Central Administration Web site home page, click Security.

  2. 在 [安全性] 頁面上的 [一般安全性] 下按一下 [設定服務帳戶]。On the Security page, under General Security, click Configure service accounts.

  3. 在 [服務帳戶] 頁面上 [認證管理] 區段中,從下拉式清單中選取執行 Excel Services 應用程式的應用程式集區。On the Service Account page, in the Credential Management section, from the drop-down list, select the application pool that runs Excel Services Application.

    選取此選項時,Excel Services 服務應用程式的名稱會出現在下拉式清單下方的方塊。顯示在 [選取此元件的帳戶] 下拉式清單中的帳戶是您要新增為 Analysis Services 管理員的 Windows 身分識別。When this option is selected, the name of the Excel Services service application appears in the box underneath the drop-down list. The account shown in the Select an account for this component dropdown list is the Windows identity that you need to add as an Analysis Services administrator.

  4. 按一下 [取消]。Click Cancel.

您必須將 Excel Services 應用程式集區帳戶新增為 Analysis Services 管理員。若要將此帳戶新增為 Analysis services 管理員使用下列程序。You must add the Excel Services application pool account as an Analysis Services administrator. Use the following procedure to add this account as an administrator in Analysis Services.

若要新增 Analysis Services 管理員To add an Analysis Services administrator

  1. 在 SQL Server Management Studio,連線至 Analysis Services。In SQL Server Management Studio, connect to Analysis Services.

  2. 以滑鼠右鍵按一下 Analysis Services 最頂端的節點,並再按一下 [內容Right click the Analysis Services top node, and then click Properties.

  3. 在 [安全性] 頁面上按一下 [新增]。On the Security page, click Add.

  4. 輸入執行 Excel Services 應用程式集區帳戶的名稱,然後按一下 [確定]Type the name of the account that runs the Excel Services application pool, and then click OK.

  5. 按一下 [ OK ]。Click OK.

設定 OLAP cube 的存取Configure OLAP cube access

您必須授與存取 OLAP cube 的使用者將會建立或檢視 Excel Services 報表。為達成此目的,您必須建立 OLAP cube 中的角色。(您可以可以使用現有的角色如果先前建立一個)。You must grant access to the OLAP cube for the users who will be creating or viewing Excel Services reports. To do this, you must create a role in the OLAP cube. (You can use an existing role if you have created one previously.)

在角色,您可以授與存取權的使用者或 Active Directory 群組。我們建議使用更輕鬆地管理 Active Directory 群組。Within the role, you can grant access to users or Active Directory groups. We recommend using Active Directory groups for easier administration.

Analysis Services 提供各種指定之角色的存取選項。您可以建立多個角色如果您有不同的所需的 cube 的存取權的不同層級的使用者群組。Analysis Services provides a variety of access options for a given role. You can create multiple roles if you have different groups of users who need different levels of access to the cube.

使用下列程序來建立角色指派權限給使用者。Use the following procedure to create a role and assign permissions to users.


此程序說明如何授與 cube 的讀取權限。根據需要為您的使用者可以調整角色的權限。This procedure describes how to grant read access to a cube. You can adjust the permissions for the role as needed for your users.

若要建立角色To create a role

  1. 在 SQL Server Management Studio,連線至 Analysis Services。In SQL Server Management Studio, connect to Analysis Services.

  2. 展開 [資料庫] 並展開您要建立之角色的資料庫。Expand Databases and expand the database where you want to create the role.

  3. 以滑鼠右鍵按一下 [角色並按一下 [新增角色Right-click Roles and click New Role.

  4. 在 [一般] 頁面上輸入角色的名稱。On the General page, type a name for the role.

  5. 在 [成員資格] 頁面上新增的使用者或包含您要授與 cube 存取權之的使用者的 Active Directory 群組。On the Membership page, add the users or Active Directory group containing the users to whom you want to grant cube access.

  6. 在 [Cube] 頁面上選取 [讀取您想要授與存取權之 cube 的 [存取] 下拉式清單中。On the Cubes page, select Read from the Access dropdown list for the cubes that you want to grant access to.

  7. 按一下 [ OK ]。Click OK.

授與一次的讀取權限至 OLAP cube,使用者將能夠連線至 excel 來建立報表 cube 和也能重新整理 Excel Services 中的資料。Once granted read permissions to the OLAP cube, users will be able to connect to the cube in Excel to create reports and will also be able to refresh the data in Excel Services.


一旦授與至 OLAP cube 的存取權,使用者也可以連線到直接在 SQL Server Management Studio 中的 cube 中。他們會授與 cube 存取決定他們可以在 Management Studio 中執行的動作。Once granted access to an OLAP cube, users can also connect to the cube directly in SQL Server Management Studio. The access that they are granted to the cube determines what actions they can perform in Management Studio.

建立及發佈報表Create and publish a report

一旦已授與使用者 cube 的存取權,他們才能連線至其在 Excel 中。連線至 cube 中使用下列程序。Once a user has been granted access to the cube, they can connect to it in Excel. Use the following procedure to connect to the cube.

連線至 OLAP 資料來源To connect to an OLAP data source

  1. 在 Excel 中,[資料] 索引標籤的 [取得外部資料] 區段中按一下 [從其他來源] 和 [從 Analysis ServicesIn Excel, on the Data tab, in the Get External Data section, click From Other Sources, and then click From Analysis Services.

  2. 在 [伺服器名稱] 文字方塊中輸入您想要連線至 Analysis Services 執行個體的名稱和 [下一步In the Server name text box, type the name of the instance of Analysis Services that you want to connect to, and then click Next.

  3. 選取您想要連線的 cube,然後按一下 [下一步Select the cube that you want to connect to, and then click Next.

  4. 按一下 [完成]。Click Finish.

為了讓已發佈的報告中使用 EffectiveUserName 功能,必須設定 Excel Services 驗證設定為使用 Windows 驗證。使用下列程序來設定您的資料來源的 Excel Services 驗證設定。In order for the EffectiveUserName feature to be used in a published report, the Excel Services authentication settings must be configured to use Windows authentication. Use the following procedure to configure the Excel Services authentication settings for your data source.

設定 Excel Services 驗證設定To configure Excel Services authentication settings

  1. 在 Excel 中,在 [資料] 索引標籤的 [連線] 區段中,按一下 [連線In Excel, on the Data tab, in the Connections section, click Connections.

  2. 選取連線至 Analysis Services cube,然後再按一下 [內容Select the connection to your Analysis Services cube, and then click Properties.

  3. 在 [定義] 索引標籤上按一下 [驗證設定]。On the Definition tab, click Authentication Settings.

  4. 在 [ Excel Services 驗證設定] 對話方塊中,選取 [使用驗證的使用者帳戶(Excel 2016) 或Windows 驗證(Excel 2010)] 選項,然後再按一下 [確定]On the Excel Services Authentication Settings dialog box, select the Use the authenticated user's account (Excel 2016) or Windows Authentication (Excel 2010) option, and then click OK.

  5. 按一下 [確定] ,然後按一下 [關閉Click OK and then click Close.

完成建立您的報表之後下, 一步是將其儲存至 SharePoint Server 2013 文件庫已設定為 Excel services 信任的檔案位置。若要儲存活頁簿中使用下列程序。When you have finished creating your report, the next step is to save it to a SharePoint Server 2013 document library that has been configured as a trusted file location in Excel Services. Use the following procedure to save your workbook.


此程序會使用 Excel 2016。在 Excel 2010 中使用 [檔案],儲存&傳送活頁簿發佈至 SharePoint Server 2013。This procedure uses Excel 2016. In Excel 2010, use File, Save & Send to publish the workbook to SharePoint Server 2013.

若要將報表發佈至 SharePoint ServerTo publish the report to SharePoint Server

  1. 在 Excel 中,在 [檔案] 索引標籤上按一下 [儲存In Excel, on the File tab, click Save.

  2. 按一下 [電腦] 和 [瀏覽]Click Computer, and then click Browse.

  3. 輸入您要儲存檔案並將 SharePoint 文件庫的 URL。Type the URL of the SharePoint document library where you want to save the file.

  4. 輸入檔案名稱的方式,,然後按一下 [儲存Type a filename, and then click Save.

一旦已儲存至 SharePoint Server 2013 的活頁簿、 您可以使用 Excel Services 轉譯及資料會重新整理以重新整理設定的設定中的 Excel Services 信任的檔案位置設定。Once the workbook has been saved to SharePoint Server 2013, you can render it using Excel Services and the data will refresh based on the refresh settings configured in the Excel Services trusted file location settings.