Project Server 2010: Prompted for SQL Server Login upon editing Business Intelligence Center reports in Excel client application

While working on a PWA site, you attempt to edit reports in the Business Intelligence Center using the Excel client application and receive a SQL Server Login dialog box, as shown below.

To resolve this issue, ensure the Active Directory user or group has the db_datareader role on the PWA reporting database. When editing reports, the Excel client application connects directly to the PWA reporting database using the credentials of whoever launches it, so they must have the correct permissions in SQL.

Also, keep in mind that the Excel Web App uses the Secure Store Service for opening and refreshing reports in the Business Intelligence Center, not the logged on user credentials as in the Excel client application. To see who can open and refresh reports in the Excel Web App, go to Central Administration -> Manage service applications -> Secure Store Service. Click on the Target Application ID, click Next twice, and see the Members. Add the Active Directory users or group as needed.