PerformancePoint Monitoring Data Source Connection Problems

Now that the PPS user groups are live on TechNet I have already seen several posts about data source connection issues. This was similar to posts in the BSM newsgroup on the same topic. I have been meaning to post about this for a while. Now is as good a time as any.

Data Source Connection Failed!

The inability to connect to a data source is all about permissions. Well, duh! Many people using PPS M&A (and BSM) for the first time pull their hair out trying to determine why data source connections keep failing. Most of the time they are looking at their own permission levels as the source of the problem "...but I'm an administrator on all these boxes!!"

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.

That last sentence is important so it's worth repeating...

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.

It does not matter if your personal administrative account "rules them all" within your IT infrastructure; if the identity account configured in the appropriate PPS application pool does not have permission to connect to and read data from any data source (OLAP, relational DB, an Excel file, a SharePoint list... anything) your connection will fail. Period.

By default PPS is not trying to make a connection as you, it makes the connection as the identity account configured within one of the Monitoring app pools.

NB: this post is all about the default data source connection behaviour of PPS M&A. Per User connections (where you are making the connection as yourself) can be configured, but that is another kettle of fish altogether. Per User connections take a bit of manual web.config work to configure, so you will certainly know if this has been set up (it's not just a simple checkbox that can be inadvertently ticked).

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.

UPDATE (04 April 2008): Thanks to Phil Austin who wrote this post on using IIS 5.1 in Win XP. I did not cover this situation adequately in the original version of this post. Because there are no application pools in this version of IIS you cannot determine which account is being used as the identity via the IIS manager. However a quick profiler trace as recommended above will expose the ASPNET as the account that is trying to connect. Ensure it has appropriate access to the data source and you're right to go.

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.

Web Service Connection

Data source connections are made using app pool identities, we know that now. However, in order to determine the app pool identity you first need to connect to the Monitoring web service. If you are seeing the message below then your first problem is web service related, it's not data source (yet).

[Once more repeating the text for search engines]

"Unable to connect to the specified server. Make sure the address is correct"

This is an error that you may see in Dashboard Designer. It means you cannot connect to the Monitoring web service configured in the Server tab of Dashboard Designer options, specifically the Server URL property.

This error is far less common than that of data sources but is still worth mentioning. Often, this is an app pool identity permissions problem, but sometimes it's a result of the web service URL settings within a specific workspace.

App Pool Identity Permissions (again)

If you originally had Network Service (or something else) set up as an app pool identity account and subsequently changed it to a domain account you may have problems connecting to the Monitoring web service. If you change the app pool account to something else manually be sure to grant the new account membership to the BPMDeveloper role in the PPSMonitoring system database.

Workspace URL Setting

Workspace files contain the URL of the Monitoring server they were built against / published to. for example if you have just opened up one of the sample workspaces that ship with PPS M&A you may find that you cannot connect to your perfectly-configured Monitoring web service any more. The sample MS workspaces were all built and tested using a monitoring server called ab004-23. The web service address within these workspaces is set to http://ab004-23:40000/WebService/PmService.asmx. Unless you have a server with the same name in your domain (with a PPS Monitoring Server on it) you will not be able to connect. All that you need to do here is change the server name to that of your monitoring server:  http://SERVERNAME:40000/WebService/PmService.asmx. This is all documented in the ReadMe that ships with the samples but it's worth mentioning here again.

It is also worth noting that if you open an MS sample workspace and then close the workspace, (keeping Dashboard Designer open) the Server URL and Server name properties will continue to point to the monitoring server on ab004-23 (or the most recent workspace file). This is not a problem and is common (by design behaviour) to all workspaces, not just the MS samples.

You have three simple choices:

1. manually change the URLs in the Options > Server tab
2. Open up a workspace you have already worked on that is configured to point towards your monitoring server. The URLs will be automatically populated
3. Close down the Dashboard Designer application and fire it up again. Dashboard Designer is a ClickOnce application. Once initially installed, each time it is subsequently launched it will repopulate the Server URL and Server name text boxes with the PPS Monitoring web service URL of the Monitoring Central site it was last launched from.

In Conclusion

So, the next time you are having PPS data source connection problems, don't immediately think the problem lies within your own personal permission set. Instead think of your PPS application pool identity account saying, as Seinfeld's George Costanza would, "It's not you, it's me."