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
- In Central Administration, in the Application Management section, click Manage service applications.
- Click the Excel Services service application.
- Click Global Settings.
- On the Excel Services Application Settings page, in the External Data section, select the Use the EffectiveUserName property check box.
- Click OK.
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
- On the SharePoint Central Administration Web site home page, click Security.
- On the Security page, under General Security, click Configure service accounts.
- 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.
- 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
- In SQL Server Management Studio, connect to Analysis Services.
- Right click the Analysis Services top node, and then click Properties.
- On the Security page, click Add.
- Type the name of the account that runs the Excel Services application pool, and then click OK.
- Click OK.