Application Pool Security - a single application pool account is used to make all connections to data sources. A PPS Monitoring server's default, out-of-the-box, behaviour is to connect to all data sources using a PPS application pool identity account.
- Advantage: because one account makes the data source connections the monitoring server caches results providing greater speed responsiveness to all users.
- Disadvantage: no data security is possible based on who the user is because all users are connecting via one the account; the database does not know who they are.
Application Pools
The Monitoring server configuration process creates and uses several Application Pools. For the purposes of this post we are only looking at two: PPSMonitoringWebService, PPSMonitoringPreview. Nonetheless we must also be wary of identity used by the SharePoint - 80 app pool too. When you initially configure your monitoring server you are given the chance to stipulate which account will be used as the identity in all app pools. So whatever you choose will be configured for all. The identity can be changed for each individual app pool as needed. Best practice here is to always use a specifically-created domain account.
PPSMonitoringWebService app pool identity account is used to connect to the Monitoring web service when you are working in Dashboard Designer (i.e. creating data sources, scorecards, KPIs etc.).
PPSMonitoringPreview app pool identity is used by the preview web site. This site allows designers to deploy and preview dashboards instead of having to deploy to SharePoint every time. If you deploy your dashboard to a preview site, any data source connections are made using the identity configured within this app pool.
SharePoint - 80 application pool identity is used to make connections once a dashboard has been deployed to SharePoint. Ensuring this app pool identity has appropriate access to the PPSMonitoring DB and data sources is essential. It is recommended to make the identity account used by this app pool to be the same as the PPS-created app pools. This just saves the hassles of aligning the permission set of two accounts rather than just one.
TIP: When trying to troubleshooting a connection/permission problem of any sort run a Profiler trace to see precisely who is making the connection attempt (and failing in the process). If you have different identities in the various app pools this will at least point you in the right direction as to where the permissions problem may be originating from.
Identity Permissions
The screenshot below is the most common error seen by many people using Dashboard Designer to create a data source.
[I'm simply repeating the text from the screenshot so that search engines will pick it up and make this post more find-able]
"The PerformancePoint Server could not connect to the specified data source. Verify that either the current user or application pool user has Read permissions to the data source, depending on your security configuration. Also verify that all required connection information is provided and correct"
This problem is easy to troubleshoot if you know where to look. First place not to look is the mirror; the problem here is not your own personal permission set.
Here are two simple troubleshooting steps:
1. Determine what account is configured for the app pool identity. If you have performed a "Next > Next > Next" install this will probably be Network Service. This account is fine for testing when all the bits you need are on the one machine (ServerA). If, however, your SSAS server, where your cubes are housed exists on a separate machine (ServerB) the Network Service account from ServerA will not be able to make a connection to it. As mentioned above, best practice here is to use a purpose-built domain account. Get into IIS and look at the Identity tab on the app pool properties to confirm precisely which account is being used.
2. Impersonate the identity account and try to connect to the data source in question. Leave Dashboard Designer alone for a while. Forget about it. Either log in as, or Run As... using the app pool identity account and try to access the data you're looking for using another application. For example, if you're having problems with a cube try creating a PivotTable in Excel or browse the cube in SSMS. The most important thing here is do not test the connection as you, do it as the identity account - this is what PPS is trying to do. If you can't connect to the data source when impersonating the identity account then PPS doesn't have much of a chance either.
