Project Server 2010: Business Intelligence Center–Common Errors

This follows on from the posting on the structure of the Business Intelligence Center in Project Server 2010 – http://blogs.msdn.com/b/brismith/archive/2010/08/24/project-server-2010-business-intelligence-center-behind-the-scenes.aspx and covers a few of the possible errors you might run in to.

For Project Server 2007 one of my most read, or at least “most viewed” and commented pages was Common Error Messages when Building Cubes with Project Server 2007.  I think with the architecture of 2010 we resolve many of those cube building difficulties, but the configuration or Excel Services Application and Secure Store Service will fill the gap that leaves.  I am preparing some internal training on this topic so thought I’d re-purpose many of my screenshots in this blog posting.  I start with no configuration or Excel Services or the Secure Store Service at all, as if I had never read or even heard of the excellent article at http://technet.microsoft.com/en-us/library/ee662106.aspx that describes how things should be configured and then hit one error after another until finally I have it all working.  I will attempt to run the SimpleProjectList sample report from the default English (United States) library.  I will number the errors, and then at the end of the posting I will list the ULS log entries you can expect to see for each error, along with the text from these error dialogs, and hopefully this will allow you to work backwards from the logs to the cause.  I want to include the logs so the search engines have something cool to read without breaking the flow of the story too much – and the text from the dialogs as the search engines may struggle with the pictures.

image

Error 1

image

The first error I see tells me that it will not open that report as the Data Connection file is not in a trusted location.  Well that’s an easy one to fix (referring to the TechNet document referenced above) so I add the right URL as a trusted location and move on…

Error 2

image

Well at least this ones just a warning – and I can continue just by clicking “Yes”, but we should really fix that.  This is an interesting one as you might have wondered (if you know already what needs configuring) why we didn’t get an error about our file itself;f not being in a trusted location.  The reason is that by default there is a trusted location of http://.  And this is configured to trust children, and also to allow External Data.  In fact the only thing it is missing from our recommended configuration is that Refresh warning enabled is still checked – hence the warning we are getting at Error 2.  Best practice would probably be to delete the http:// location and then just explicitly set the locations as per the TechNet document.  So moving on…

Error 3

image

Now we have Excel Services Application configured just right – so we start hitting the Secure Store Errors.  In a bid to hit most of the possible errors I created a Secure Store Service Target Application but didn’t set any credentials…

Error 4

image

Hmm.  Same error, but the ULS logs distinguish between them (– see the section at the end of the blog for the differences.  So now I set some credentials, but put in a bad password…

Error 5

image

Different error, and this makes sense.  We have some credentials, but they don’t work.  I’ll put that password right, but for the group that can make use of this target application I will use a group that I do not belong to…

Error 6

image

Back to the generic error again, but ULS comes good and gives us a better idea what might be wrong. “Claims stored in the credentials did not match with the group claim for a group app.”  Put in the right group, of which I am a member and hey presto!  All that just for one project!

image

I’m sure there may be other combinations of issues that will give different errors, but I have been very impressed with the wording in the ULS logs (see below) which does make it quite easy to understand what might have gone wrong.

Here are the details of the errors again, more for the search engines, but feel free to read on.  I haven’t listed all the lines associated with the failure – just the main ones that will help identify the failure mode.  As mentioned above I have also included the text from each of the dialogs above

Error 1

Dialog - The Data Connection File <url to odc file> used in the workbook is not in a trusted location.  
The following connections failed to refresh:

Project Server - Simple Projects List
ULS - 
  • w3wp.exe (0x230C) 0x18DC Excel Services Application External Data 5647 Information The ODC file http://brismith8100. /PWA/ProjectBICenter/Data%20Connections%20for%20PerformancePoint/English%20(United%20States)/Project%20Server%20-%20Simple%20Projects%20List.odc is not in a DCL that is trusted by Excel Services Application. To fix this problem, add the DCL to the trusted DCL list in the administrator console.
  • w3wp.exe (0x230C) 0x18DC Excel Services Application Excel Calculation Services erw5 Medium ConnectionInfoManager.GetConnectionInfo: InitConnInfoFromOdcFile threw an exception: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException: Exception of type ‘Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException’ was thrown. at Microsoft.Office.Excel.Server.CalculationServer.ConnectionInfoManager.InitConnInfoFromOdcFile(ExtendedConnectionInfo extendedConnInfo) at Microsoft.Office.Excel.Server.CalculationServer.ConnectionInfoManager.GetConnectionInfo(Request request, String externalSourceName, Int32 externalSourceIndex, Boolean& shouldReportFailure)
Error 2
Dialog - This workbook contains one or more queries that refresh external data. A malicious user can design a query to access
confidential information and distribute it to other users or perform other harmful actions.
If you trust the source of this workbook, click Yes to enable queries to external data in this workbook. If you are not sure, click No so that changes are not applied to your workbook.
Do you want to enable queries to external data in this workbook?
ULS – Not applicable – just a warning
Error 3
Dialog -  An error occurred while accessing application id ProjectServerApplication
from Secure Store Service. The following connection failed to refresh:
Project Server - Simple Projects List
ULS -
  • w3wp.exe (0x230C) 0x2344 Secure Store Service Secure Store elm4 High SQL command failed: Sproc name: dbo.proc_sss_GetCredentials, Application Id: ProjectServerApplication, Error code: 80630490, Error message: Target application not found (application id: ProjectServerApplication).
  • w3wp.exe (0x230C) 0x2344 Secure Store Service Secure Store 7493 Critical The Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was ‘Target application not found (application id: ProjectServerApplication).’. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK).
  • w3wp.exe (0x230C) 0x2344 Secure Store Service Secure Store efp4 Monitorable GetCredentials failed with the following exception: System.ServiceModel.FaultException`1[Microsoft.Office.SecureStoreService.Server.SecureStoreServiceTargetApplicationNotFoundFault]: Target application not found (application id: ProjectServerApplication). (Fault Detail is equal to Microsoft.Office.SecureStoreService.Server.SecureStoreServiceTargetApplicationNotFoundFault).
  • w3wp.exe (0x230C) 0x21C0 Excel Services Application Web Front End 4969 Warning SSS has failed with the following exception: Target application not found (application id: ProjectServerApplication). [Session stuff here…]
Error 4
Dialog - Same as Error 3.
ULS -
  • w3wp.exe (0x1BF8) 0x09A8 Secure Store Service Secure Store elm4 High SQL command failed: Sproc name: dbo.proc_sss_GetCredentials, Application Id: ProjectServerApplication, Error code: 80630001, Error message: Credentials were not found for the current user within the target application ‘ProjectServerApplication’. Please set the credentials for the current user.
  • w3wp.exe (0x1BF8) 0x09A8 Secure Store Service Secure Store 7493 Critical The Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was ‘Credentials were not found for the current user within the target application ‘ProjectServerApplication’. Please set the credentials for the current user.’.
  • w3wp.exe (0x1BF8) 0x09A8 Secure Store Service Secure Store efp4 Monitorable GetCredentials failed with the following exception: System.ServiceModel.FaultException`1[Microsoft.Office.SecureStoreService.Server.SecureStoreServiceCredentialsNotFoundFault]: Credentials were not found for the current user within the target application ‘ProjectServerApplication’. Please set the credentials for the current user. (Fault Detail is equal to Microsoft.Office.SecureStoreService.Server.SecureStoreServiceCredentialsNotFoundFault).
  • w3wp.exe (0x1BF8) 0x12A8 Excel Services Application Web Front End 4969 Warning SSS has failed with the following exception: Credentials were not found for the current user within the target application ‘ProjectServerApplication’. Please set the credentials for the current user. [Session info here…]
  • w3wp.exe (0x1BF8) 0x12A8 Excel Services Application External Data 3238 Information Unable to establish a connection using credentials retrieved from SSS. This could be because the Unattended Service Account is not configured or because the credentials retrieved from SSS are not valid. [Session info here…]
Error 5
Dialog - Access was denied by the external data
source. The following connections failed to refresh:
Project Server - Simple Projects List
ULS -
  • w3wp.exe (0x1BF8) 0x242C Secure Store Service Secure Store efr5 High ValidateCredentialClaims – Access Denied: Claims stored in the credentials did not match with the group claim for a group app.
  • w3wp.exe (0x1BF8) 0x242C Secure Store Service Secure Store 7493 Critical The Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was ‘Access is denied.’. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK).
  • w3wp.exe (0x1BF8) 0x242C Secure Store Service Secure Store efp4 Monitorable GetCredentials failed with the following exception: System.ServiceModel.FaultException`1[Microsoft.Office.SecureStoreService.Server.SecureStoreServiceFault]: Access is denied. (Fault Detail is equal to Microsoft.Office.SecureStoreService.Server.SecureStoreServiceFault).
  • w3wp.exe (0x1BF8) 0x12A8 Excel Services Application Web Front End 4969 Warning SSS has failed with the following exception: Access is denied. [Session stuff here]
  • w3wp.exe (0x1BF8) 0x12A8 Excel Services Application External Data 3238 Information Unable to establish a connection using credentials retrieved from SSS. This could be because the Unattended Service Account is not configured or because the credentials retrieved from SSS are not valid. [Session stuff here…]

Error 6

Dialog – Same as Error 3.
ULS -
  • w3wp.exe (0x230C) 0x2344 Excel Services Application Excel Calculation Services 7mor Medium TrustedLocationCacheNode.Find: Found a uri that does not belong to any defined trusted location: https://brismith8100.redmond.corp.microsoft.com/PWA/ProjectBICenter/Sample%20Reports/English%20(United%20States)/SimpleProjectsList.xlsx
  • w3wp.exe (0x230C) 0x2344 SharePoint Server Logging Correlation Data xmnv Medium Result=Id=OpenWorkbookLocationNotTrusted; Microsoft.Office.Excel.Server.CalculationServer.FileOpenException: This workbook cannot be opened because it is not stored in an Excel Services Application trusted location. at Microsoft.Office.Excel.Server.CalculationServer.Session.OpenWorkbookAsync(AsyncHandler`1 callback, Object userState, Request request, Uri url, Boolean loadedOnDemand)…
  • w3wp.exe (0x230C) 0x2344 Excel Services Application Excel Calculation Services ech0 Medium ExcelService.PostProcessRequest: web method: OpenWorkbook, got exception Id=OpenWorkbookLocationNotTrusted; Microsoft.Office.Excel.Server.CalculationServer.FileOpenException: This workbook cannot be opened because it is not stored in an Excel Services Application trusted location. at Microsoft.Office.Excel.Server.CalculationServer.Session.OpenWorkbookAsync(AsyncHandler`1 callback, Object userState, Request request, Uri url, Boolean loadedOnDemand)…
  • w3wp.exe (0x230C) 0x2344 Excel Services Application Excel Calculation Services 2017 Information Failed to load ‘https://brismith8100.redmond.corp.microsoft.com/PWA/ProjectBICenter/Sample%20Reports/English%20(United%20States)/SimpleProjectsList.xlsx&#8217; with error: ‘The workbook is not in a trusted location.’. [Session stuff here…]