How to configure EffectiveUserName in SharePoint 2013

EffectiveUserName is a SQL Server Analysis Services connection string property that contains the name of the user who is accessing a report or dashboard.

In SharePoint Server 2013, we can use this property in conjunction with Excel Services or PerformancePoint Services to pass the identity of the user who is viewing the report or dashboard to SQL Server Analysis Services.

This allows per-user identity WITHOUT the need to configure Kerberos delegation

Using the EffectiveUserName feature with Excel Services requires the following:

  • The Excel Services application pool account must be an Analysis Services Administrator.
  • You must enable the EffectiveUserName option in Excel Services Global Settings.
  • You must select the Use the authenticated user’s account option in the Excel Services Authentication Settings in Excel.

Use the following procedure to enable the EffectiveUserName feature in Excel Services.

To enable EffectiveUserName in Excel Services

  1. In Central Administration, in the Application Management section, click Manage service applications.
  2. Click the Excel Services service application.
  3. Click Global Settings.
  4. On the Excel Services Application Settings page, in the External Data section, select the Use the EffectiveUserName property check box.
  5. Click OK.

.

Configure Analysis Services access

 Using the EffectiveUserName feature requires that the account that is running the Excel Services application pool be an Analysis Services administrator.

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.

To determine the Excel Services application pool account

  1. On the SharePoint Central Administration Web site home page, click Security.
  2. On the Security page, under General Security, click Configure service accounts.
  3. On the Service Account page, in the Credential Management section, from the drop-down list, select the application pool that runs Excel Services Application.

    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.

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.

To add an Analysis Services administrator

  1. In SQL Server Management Studio, connect to Analysis Services.
  2. Right click the Analysis Services top node, and then click Properties.
  3. On the Security page, click Add.
  4. Type the name of the account that runs the Excel Services application pool, and then click OK.
  5. Click OK.

Technet reference – Use Analysis Services EffectiveUserName in SharePoint Server 2013

Technet Reference – how to determine the account that is running the Excel Services application pool

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s