Speaking the language of business intelligence with an Australian accent

Saturday, November 3, 2007

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.

ConnectionFailed

[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.

AppPool

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).

UnableToConnect

[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.

WebServiceConnectionString

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.

MonitoringCentral

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."

69 comments:

Wade Dorrell (MSFT) said...

Thanks for the write-up, Nick!

kart said...

Nick...It is a useful information - but by any chance have you tried monitoring Server with Win XP & IIS 5.1. There the account is ASPNET as there is no App Pool concept - eventhough you give Access to Cubes for ASPNET user - it is not connecting (all local) any thoughts...Having Kerberos is not possible for sampling

Ronen said...

Exelent!!! Just like every one should deploy help on BLOGS.

Carmine001 said...

Thanks !!!

Anonymous said...

Rock ON! I never leave comments. This clear answer deserves praise. SO NICE to put in the whole text so search engines pick it up.

Nick Barclay said...

Glad it was helpful!

Phil said...

In answer to kart's comment about XP and IIS 5.1 see my post here: http://phil-austin.blogspot.com/2008/04/performancepoint-monitoring-data-source.html

Nick Barclay said...

Thanks for that post Phil. Yes the ASPNET account is used when running IIS 5.1. As per one of the tips I listed above, running a trace on the SSAS server to see "who" is making the connections would help in identifying the account and adjusting their permission set. Sorry I didn't make this clearer - I will update the post

Cheers,
Nick

Anonymous said...

Finalmente leo tu blog ! :)
Lisset

Rocky said...

Thanks, your blog was very useful. You saved the day for me :)

Romina said...

Thanks for this useful information!

Anonymous said...

Great blog.

nidhi said...

hey Nick...
Im getting this error when i try to connect to a database while creating a datasource in dashboard designer..
My web service application pool identity is "predefined(network service)".. and the server url already is http://servername:40000/WebService/PmService.asmx...
i have no clue wats going wrong....

Nick Barclay said...

Does the Network Service account have read access to the cube you're trying to connect to?

nidhi said...

How do i check that... i haven't created any cube.
Im just trying to connect it to a sql server 2005 db table.
and i read some other blogs as well.. im guessing i have a version problem here...
please answer my dumb question.. Does ppsmonitoring server use .net framework 3.0.
If yes... why do i see 1.0 version
instances in all the web.configs.
Do i have to make changes there?

Nick Barclay said...

OK, I'll rephrase. Does the Network Service account have read access to the SQL table you're trying to connect to?

For now, don't worry about .NET versions, just check that your application pool account has the correct access permissions to your data sources.

Cheers,
Nick

nidhi said...

Can you please tell me how to check that....

Nick Barclay said...

nidhi, it depends on how your DB security is configured. You should check to see whether the application pool account has at least SELECT permissions on the table / view you're trying to access.

nidhi said...

Hi Nick,
As my App pool Identity is network service.. i added this account in my Sql Server.. so it has full access.Even now im getting error.
Im attaching snapshots to give you a better picture.. when i try to connect from DD->options->Server->
Server Options, Connect.. i get a simple message box "Unable to connect to server".
If i enter the url:http://localhost:40000/WebService/PmService.asmx in my IE i get..

Parser Error Message: The page must have a webservice class="MyNamespace.MyClass" directive.


Source File: /WebService/PmService.asmx Line: 1


please help...

yousef said...
This comment has been removed by the author.
yousef said...

Hi all,
thanks for your services.
I am working on PPS from last month and everything is good.
but now I faced the following problem(Unable to connect to the specified server. Make sure the address is correct.)but I checked the server address and URL from the Options menu but when I click on Connect button the following error was display(Unable to connect to server)
kindly I need your help.

Best Regards,
Yousef Amarneh

Manoj said...

Hi Nick
I have installed Monitoring Server on my desktop(Win XP Professional).
but i m not able to connect to the web service in server option of dashboard Designer.
it is giving the error
Unable to connect to the server
I m using the following string for the server name
http://localhost/WebService/PmService.asmx

Thanks in advance

Nick Barclay said...

Manoj,
try http://localhost:40000/WebService/PmService.asmx

You left the port number out.

Cheers,
Nick

Nick Barclay said...

Yousef, can you use a browser to view the web service? Try opening up IE and navigate to the web service URL.

Cheers,
Nick

Gan said...

HI Nick,
After reading your blog, I still not able to create the data source connection, which is connecting to my local database.
Below is the information of my system:

1. PPSMonitoringWebService App Pool - NT Authority\Network Service
2. Database Security for Network Service
- PPSMonitoring (db_owner, BpmDeveloper)
- AdventureWorksDW (db_owner)

I have used the SQL Profiler tool to capture the credential being used to access to database and confirmed the Network Service was being used. Anyway, I still facing the same error:

"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."

Any thoughts? Thanks

Nick Barclay said...

Gan, are you trying to connect to an SSAS cube? If so, does the account have permission to access it?

Anonymous said...

Nick,

I followed your directions and still can't connect to the datasource. Getting the error:
"The PerformancePoint Server could not connect to the specified data source..."
The domain identity account has permissions to access the cube, I was able to access the cube using this account through excel.
I should note that PPS is in one server and SSAS in another. However, I am RDP into the actual PPS server and trying to connect from there.

Any suggestions?

Kory said...

Thanks Nick! To round-out your discussion, please mention the "Cause 2" issue outline in this article: http://support.microsoft.com/kb/947378

It recently caused us to scratch our heads even after reading your posting because we thought we tried everything you suggested. Turns out it was Kerberos that needed to be configured on the PPS servers.

Anonymous said...

We have two users that do not have a problem when they first log in to there computer, I can only assume that the configuration is ok. However after a while the two users access the page from Sharepoint and receives the error: After user logs off and back on the PC they again get access for about 30 minutes. Any Thoughts.
Thanks, I am thinking it would be a setting in IE.

Harsha said...

Great Blog Nick ....

Suresh Kumar said...

Thanks for hte useful information Nick. Very helpful for the beginners in Performnace Point.

mhc said...

Hi, thanks for your post - but do you have a hint what it could be if the account under which the webapp is running can connect to the cubes from excel - from the server and from the clients - but not with the dashboard designer?

Nick Barclay said...

I would say it's the same problem as the post documents. When you connect to an Excel cube, by default, you're connecting as "you". By default DD attempts to connect to the cube using the application pool account. Remember there are three different application pools here.

Cheers,
Nick

Corey Hulse said...

Hello Nick,

Found your post when looking at another post you replied to.

https://forums.microsoft.com/technet/showpost.aspx?postid=2570873&siteid=17&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

I can successfully deploy to the preview server and view my dashboards there under http://SERVERNAME:40000/

I created a new web and site collection under http://SERVERNAME:11953/ and when I deploy, I get "The database connection failed. Please contact an administrator."

Why would I be able to see it successfully on the Preview page but not my own?

I looked at the Identity tab the the ID which is currently there is marked as "BpmDeveloper"

Any help you can give would be appreciated.

Corey Hulse said...

Solved the problem! Everything was set correctly with the "PPSMonitoringWebService", but my "SharePoint - 11953" application pool was using NT_Authority and not the special login we created. Thanks for the post, it was really helpful!

Nick Barclay said...

Glad you got everything sorted, Corey. Thanks for the positive feedback.

Cheers,
Nick

Corey Hulse said...

Gah! Now I've got a new error. I was happily deploying away to my new SharePoint site, when it suddenly stopped working!

I made changes, deployed to my Preview site and my actual site, and everything was fine. I made a change to one of my Dashboards, and hit "Publish All", and then the "Unable to connect to the specified server. Make sure the address is correct" error.

Nothing's changed in terms of the settings, and when I navigate to "http://SERVERNAME:40000/WebService/PmService.asmx" I get "SERVICE UNAVAILABLE"

I haven't touched any of the Application Pool settings since I fixed my first error.

Did something turn off somewhere? Why would it work then suddenly not work? I can still see my dashboards just fine when I look on both the preview site and my actual site, but now I can't deploy new changes!

Best,
Corey

Corey Hulse said...

Following up again, the account being used for "PPSMonitoringWebService" was reset back to a user account that didn't have permissions! I guess this goes back to the lesson of this post which is to always check permissions!

Corey

Aedna said...

another cause of Service Unavailable for PmService (PPSMonitoringWebService) is AppPool identity must be granted the right "logon as service" and be in IIS_WPG gtoup

Anonymous said...

hi guys .. sorry for the interuption but i wanted to know if any one has had any problems regarding that the button does not display...so i cant even download the dashboard designer...
thanx

Afrah said...

Thanks a million for the wonderful article. It was such a clear step-by-step write up. Rock on!!!

Ben said...

This post helped lead me in the right direction, although I had a different issue entirely.

I also had a problem connecting to the monitoring web service. When I tried entering the URL directly into the web browser, I saw an error message that "assembly System.Web.Extensions, version 1.0.61025.0" could not be loaded. I looked in c:\windows\Assembly, and found the DLL for System.Web.Extensions, and the version number was 3.5.0.0. So I updated all references in web.config to version 3.5.0.0, and this got me over the hump.

zenso said...

Hi there,
Newbie to PPS and Application Pools - I get the word "ERROR" in every cell when viewing my scorecard in Dashboard Desgner. I am connection to a cube on one server and publishing to PPS services on another server. I have created a role under the cube in AS and added the same account as used on the PPS Monitoring Application Pool Identity Tab but it doesnt work.
Thank you for any help in advance.

Billy said...

Hi Nick, if you can assist me here I would really appreciate it. I have installed PPS SP2 on win server 2008 box. The box has SQL server 08 and SQL server 05 Client components installed. I get the "PerformancePoint Server could not connect ..." error when trying to connect to my SSAS(08) cube. I have added the network service account in an admin role on the cube. I have also changed all the web.config files to revert to .NET 3.5.0. I'm not really sure what to do from here, it can't be kerebos because everything is on the local machine. Any ideas?
Thanks Will

Huder said...

The App Pool account must also have 'BpmViwer' rights on the Monitoring database. It was not configured this way after running the 'Next->Next->Next->' install. After 2 days of looking and relooking into app poos accounts and permissions, etc. I noticed that the documentation mentions that this role is required. Odd they wouldnt have the install configure it that way. Oh well. I hope this helps someone else.

Anonymous said...

hi i tried what u did but i still could not get any database when i create a data source.

Arash Aghajani said...

Thanks Nick, very helpful post.
you saved my time!

Arash

Anonymous said...

I have also simillar problem. After some effort i get it done as it is explained below.

System Info:-
---------------------
OS - Windows 2003
SQL Server - 2005
PPS - 2007
MOSS 2007

Steps:-

I configured monitoring services with my Admin account because it is a local workstation used by me. I changed Securiry account of App Pools(PPSMonitoringPreview,PPSMonitoringWebservice) account to administrator. After that I logged in to Analysis Services. Go to Database properties and change Database Impersonation Info from default to desired type(in my case it is current user).

Now Close Dashboard Desiganer and run once again.

Waha! it's now yours.

Saugat said...

Nick, quite often the PPS Web service becomes unavailable and the sharepoint URL on which the PPS Dashboards are hosted start giving a Web Service Unavailable error.However restarting the server fixes the problem. Can you please suggest a permanent fix or what could be the actual problem?

Nick Barclay said...

Not sure Sugat, what service pack are you running?

Saugat said...

Nick,I am using the evaluation version of PPS.

Nick Barclay said...

What service pack?

Saugat said...

Service Pack 1.

123 said...

It is extremely interesting for me to read the article. Thanks for it. I like such themes and anything connected to this matter. I would like to read a bit more soon.
Alex
Cell phone blocker

MrStuck said...

Hi,
Could you point me in the direction of where I could find out what permissions the identity account needs in order to work if SSAS is not on the same server as PP?
Thanks

Nick Barclay said...

You need to have a domain account that you can use to connect and browse the cube. The location of PPS and SSAS should not really come into it in this case.

Dawn said...

I am getting the Identity Error message when attempting to connect to an Excel Data source using ODBC, can anyone help me out. I'm not sure how to solve this.

Nick Barclay said...

Does the application pool service account have access to open the Excel file (or the directory that it is contained in?)

Saugat said...

Nick,
I just wanted to get my concepts right...after a PPS dashboard has been published to Sharepoint, how does it connect to the data source?
I mean does it connect to the Webservice which in turn connects to the datasource, or does it uses the Sharepoint-80 app pool identity credentials to connect to the cube directly bypassing the PPS web service?

Nick Barclay said...

Saugat, I don't think there is any bypassing of the PPS web service anywhere. The PPS web service needs to connect to the data source and uses the SharePoint - 80 App Pool account to authenticate against the data source.

Saugat said...

Nick,
I have two roles in my AS Db. Role1 defines a security over a dimension named "Segment" and another role "Role2" defines security on Region.
For some people Role1 security should apply and for some Role2 security should apply. Both have distinct set of users i.e. no common person.
In PPS datasource, in the role field i type in Role1,Role2 but it results in no security.
If I apply only one role i.e. Role1 or Role2 it works perfectly.
Can you help?

Mike said...

Ni Nick, great article, cheers.

I have a question though - I *need* my credentials to find their way from Sharepoint to PerformancePoint and through to the Analysis Services server, since the cube itself has specific role-based permissions on it. Is there a similar article to yours (as well written as yours!) about how to set up and troubleshoot the impersonation scenario, not the service account one?

Nick Barclay said...

Mike, does this post have some of the stuff you need? http://nickbarclay.blogspot.com/2008/01/pps-data-connection-security-with.html

Biswajit said...

Hi Nick,
We are getting some error like while refreshing the 'PPS Dashboard designer ' to get the all the dashboards deployed in to the MOSS.
while trying to connect to the pmservice through the server tab and pressing connect,it is giving the following error.
"You do not have Administrator privileges on the Monitoring Server 'http://localhost:40000/WebService/PmService.asmx'. Contact your Monitoring Server Administrator"

Please find the role settings on the different app pools below:
PPSMonitoringWebservice : domain\x
PPSMonitoringcentral :networkservice
sharepoint-80 :domain\x
sharepointcentraladministarion:domain\x

Dwayne o'cornor said...



I found your blog when I was looking for a different sort of information but I was very happy and glad to read through your blog. The information available here is great


Alarm Monitoring

Fariha Chowdhury said...

Burglars can be very intelligent these days, which is why home security system is now a necessity. This post gives an idea how burglars will think and react in situations where security systems are installed at home. Given that you already have the most effective security system at home, it is also advisable for you to have your police contact number ready anytime.

Alarm Monitoring

Anonymous said...

I have read so many content concerning PerformancePoint issues and this piece of writing is in fact helpful. Thank you.

Need a billing software? Check this out:
Medisoft billing software
Recognized by many medical professionals

Kabita Panda said...

Two way voice alarm systems for security monitoring. Monitoring starts from $14.95 a month.
security systems

Kabita Panda said...

Home security systems are simply a great value .Beyond the important peace of mind for you and your family, a home security system has many benefits to protect your home, your family and your possessions.
Security systems Minneapolis