The following error occurs when attempting to make a data connection using the PerformancePoint Dashboard Designer and the SQL Server Table data source template:
Before I continue I want to make it clear that if you encounter this issue then you have not performed initial configuration/installation correctly for your needs and you should revisit installations guides. In my situation I’m just hacking together a dev environment which requires the use of PerformancePoint. To reiterate, better solutions to this issue exist in the form of installation guides. I wrote this because when I googled for a dirty resolution to this issue I couldn’t find any references to the error message.
Back to the issue at hand – of course this is a configuration issue. The issue for me was that the PerformancePoint service application was configured to run in a general ‘SPServices’ application pool, where the application pool account does not have access to the PerformancePoint database (correctly). To get around this issue I configured the service application to run in its own application pool, where the application pool account is a new domain account configured to have access to the PerformancePoint database. This approach meets the least privileges approach to security which we must all strive to uphold! (I actually just reused the ‘SPFarm’ (god) domain account as the application pool account to get it working in a dev environment, but that’s the theory…)
You will most likely want to use a different account when you configure the Unattended Service Account.
Note on Analysis Services
By following the above steps I managed to get the SQL Server Table data source working, but the Analysis Services data source was still throwing up the same error dialog. Upon setting the PerformancePoint service application properties, a dialog prompts you to install the PowerPivot for SharePoint installation package. After doing this not only was I still getting the same error dialog when attempting to create an Analysis Services data source but I could no longer create a SQL Services Table data source either. Running the PowerPivot for SharePoint 2013 Configuration resolved this issue, obvious really.
ULS Log entries (included for SEO purposes)
- Log categories: PerformancePoint Services, Database
System.Data.SqlClient.SqlException (0x80131904): Cannot open database "WSS_Content" requested by the login. The login failed. Login failed for user 'DOMAIN\apppoolaccount'.
SQL Database 'WSS_Content' on SQL Server instance 'SERVER' not found. Additional error information from SQL Server is included below. Cannot open database "WSS_Content" requested by the login. The login failed. Login failed for user 'DOMAIN\apppoolaccount'.
An unexpected error occurred. Error 7451.
No windows identity for DOMAIN\apppoolaccount.
Unable to load custom data source provider type: Microsoft.PerformancePoint.Scorecards.DataSourceProviders.AdomdDataSourceProvider, Microsoft.PerformancePoint.Scorecards.DataSourceProviders.Standard, Version=18.104.22.168, Culture=neutral, PublicKeyToken=71e9bce111e9429c System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.AnalysisServices.AdomdClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. File name: 'Microsoft.AnalysisServices.AdomdClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'