#ProjectServer 2010 #Excel / Excel Services Report Authentication #PS2010 #EPM #SharePoint #SP2010

Project Server 2010 uses Excel Services as the primary reporting technology, this post gives an overview on how users authenticate to each data source along with the errors seen if users / accounts don’t have access. There are two different data sources by default, the Reporting database and the OLAP Cube.

Authentication In Excel Client

When creating or editing Excel Services reports the credentials of the logged on user who launched Excel will be used to authenticate the user against the data source. Firstly we will look at the Reporting database reports. To read data from a SQL database the user will need Data Reader (db_datareader) permission against the database, in this case the PWA_Reporting database. The recommended approach from Microsoft is to create a new Active Directory Security Group for Report Authors and add all users who create / edit reports to the this group. This group is then added to the PWA_Reporting database with data reader permissions, this saves adding users directly to the database. Excel will connect directly to the PWA_Reporting database with the logged on users credentials, the logged on user must be a part of the Reporting Authors AD group otherwise they will just see a SQL Server login box in Excel unless they have access directly to the SQL server / database.

When editing or creating Excel reports that use the OLAP Cube data, the logged on users credentials are used to authenticate at the data source. The access to the OLAP Cube data is controlled differently to the PWA_Reporting database. To access the OLAP Cube data the logged on user needs to be a member of the ProjectServerViewOlapDataRole against the Cube as shown below:

All users that are members of a Project Server security group that allows “View OLAP Data” will be added to the ProjectServerViewOlapDataRole role when the Project Server cube is built. Without the “View OLAP Data” permission allowed, the user will not be able to create or edit OLAP reports in Excel. If the user doesn’t have permission to the cube they will see this login box:

Authentication In Excel Services

Viewing Reports in Excel Services will authenticate users using the ProjectServerApplication Secure Store Service App. This is the same for both data sources, when the ProjectServerApplication target application is created there is a members field, this is where you allow users access. Microsoft recommends to create another Active Directory Security group for Report Viewers, then add this group in the members field as shown below:

All users who need to view the reports need to be added to the Report Viewers AD group. Once you have created the ProjectServerApplication target app, you then need to set the credentials of the user account that will be used to authenticate against each data source, this is shown below:

This account will need data reader access to the PWA_Reporting database and at least the ProjectServerViewOlapDataRole against the OLAP cube. When users refresh the Excel Services reports in the web, firstly it will check that they are a member of the ProjectServerApplication target app then the data source will be accessed using the credentials set for the ProjectServerApplication target app. If the account used for the ProjectServerApplication doesn’t have access to the data sources you will see the errors below:

Excel Services Error:

An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh:

The same error is seen for each data source apart from the connection name.

ULS log error:

ConnectionManager.GetConnection: Caught an exception: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException: Exception of type ‘Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException’ was thrown.     at Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInterop.InitConnection()     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.<>c__DisplayClass1.<CreateConnection>b__0()     at Microsoft.Office.Excel.Server.Credentials.TryExecuteImpersonated(WindowsIdentity wi, ExecuteImpersonatedMethod method, Boolean dispose)     at Microsoft.Office.Excel.Server.Credentials.TryExecuteImpersonated.