locked
Build a connection string in Dashboard Designer RRS feed

  • Question

  • Hello Experts,

     

    Today I try to work with the Microsoft Dashboard Designer. There is one main failure I can’t resolve.

    Side Information:

    SQL Server 2005 and PPS was installed on a Virtual Machine.

     

    I tried to build a connection string but there came always the same error that the PerformancePoint Server cannot build a connection.

     

    I tried the following strings:

     

    1

    Data Source=myserver; Provider=MSOLAP.3; Initial Catalog=mycatalog; Persist Security Info=False; UserID=Administrator;Password="mypass"

     

    2

    provider=MSOLAP.3; data source= myserver; initial catalog=mycatalog; ConnectTo=9.0

     

    Is there something I forget or don’t understand?

    Hope someone can help me.

     

    Best regards,

    Alex

    Wednesday, October 31, 2007 2:18 PM

Answers

  • Hi Alex

     

    Your connection string looks fine - I pasted it into my dashboard designer replacing my own details and it worked.

     

    I suspect it is because the Application Pool identity (set in IIS) does not have appropriate permissions to connect to the cube.

     

    There's a good article about all the monitoring security here:

     

    http://blogs.msdn.com/performancepoint/archive/2007/07/12/security-considerations-for-monitoring-server.aspx

     

    Hope this helps - let me know if this doesn't work

     

    Tim

     

    Wednesday, October 31, 2007 3:32 PM
  • Hi Alex

     

    Yes both connection strings work fine for for both a local AS server and a remote one. 

     

    Just to clarify you are running PPS and Sharepoint on one virtual machine and SQL/AS on another?

     

    Does the user you are logged onto the machine you are running the Dashboard Designer have correct permissions?  If you go into the options for the app (click on Office symbol top right then options button) you'll find a Server tab.  Click the Connect button and you may get a permissions error. 

     

    If not then go into IIS Manager (in Administatrative tools) on the PPS server and you'll find Application Pools as one of the folders in the server tree. 

    Expand this and you'll see a bunch of application pools including one called PPSMonitoringWebService.

    If you right click on this and select properties you'll see an identity tab.

    On this tab select the configurable option and enter a user who will have access to the cubes on the AS box (and any other AS boxes you may want to use in future)

    Finally perform an IISReset by going to start menu, run, then typing IISreset

     

    Hope this helps you get a bit further - let me know if I've not explained anything clearly and apologies if I've over simplified anything Smile

     

    Tim

     

    BTW - You may have already found this but there's lot's more good information on the PPS technet site which covers lots on security:

     

    http://technet.microsoft.com/en-us/library/bb794633.aspx

     

    Thursday, November 1, 2007 10:41 AM
  • Hi Alex,

    Have you tried connecting to the cube without actually building a connection string manually. Try using the data source GUI and test the connection that way.

    Also, try using the App Pool identity account (User1) in applications other than Dashboard Designer in order to connect to the cube. Use Run As... to attempt to connect to the cube in Excel or SSMS.

    Cheers,

    Nick

    Saturday, November 3, 2007 12:42 AM
  • RobinSong, DashboardDesigner is a ClickOnce application i.e. it has a web-based install mechanism. Once your Monitoring Server is installed browse to Monitoring Central at http://<YouServerName>:40000/Central and you will find an installation link. You should also find a link to the Monitoring Central from the Start menu on the server you installed on (Start > Programs > Microsoft Office PerformancePoint Server 2007 > Monitoring Central)

     

    In future, please create a new thread, that way other members of the forum will see your question and have an opportunity to help you.

     

    Cheers,

    Nick

    Thursday, February 14, 2008 9:35 PM

All replies

  • Hi Alex

     

    Your connection string looks fine - I pasted it into my dashboard designer replacing my own details and it worked.

     

    I suspect it is because the Application Pool identity (set in IIS) does not have appropriate permissions to connect to the cube.

     

    There's a good article about all the monitoring security here:

     

    http://blogs.msdn.com/performancepoint/archive/2007/07/12/security-considerations-for-monitoring-server.aspx

     

    Hope this helps - let me know if this doesn't work

     

    Tim

     

    Wednesday, October 31, 2007 3:32 PM
  • Hello Tim,

    Which of the connection string going well, or both?

    I got a ready Virtual Machine with installed SQL Server, Performance Point Server and SharePoint Server.

    Thanks a lot for the article. Yesterday I founded this article too. MyProblem is that I don’t know there I have to change these settings.  

    To work with localhost seems to go well but to connect with the other machine don’t.

    I get always this error message:

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

    The SQL Server on the other virtual machine runs in mixed mode.

    Have you got a good User Guide how I should set all my settings?

     

    Kind regards,

    Alex

     

    Thursday, November 1, 2007 8:53 AM
  • Hi Alex

     

    Yes both connection strings work fine for for both a local AS server and a remote one. 

     

    Just to clarify you are running PPS and Sharepoint on one virtual machine and SQL/AS on another?

     

    Does the user you are logged onto the machine you are running the Dashboard Designer have correct permissions?  If you go into the options for the app (click on Office symbol top right then options button) you'll find a Server tab.  Click the Connect button and you may get a permissions error. 

     

    If not then go into IIS Manager (in Administatrative tools) on the PPS server and you'll find Application Pools as one of the folders in the server tree. 

    Expand this and you'll see a bunch of application pools including one called PPSMonitoringWebService.

    If you right click on this and select properties you'll see an identity tab.

    On this tab select the configurable option and enter a user who will have access to the cubes on the AS box (and any other AS boxes you may want to use in future)

    Finally perform an IISReset by going to start menu, run, then typing IISreset

     

    Hope this helps you get a bit further - let me know if I've not explained anything clearly and apologies if I've over simplified anything Smile

     

    Tim

     

    BTW - You may have already found this but there's lot's more good information on the PPS technet site which covers lots on security:

     

    http://technet.microsoft.com/en-us/library/bb794633.aspx

     

    Thursday, November 1, 2007 10:41 AM
  • Hello Tim,

     

    Yes you are right. On the first VM I tried to use PPS (SQL2005, SharePoint are installed, but at first I don’t need them) and on the second VM is SQL Server 2005 and all Engines installed (AS, RS, IS …)

     

    To push the Connection Button didn’t release any events.

    I made all the changes you described, but the error stay.

    Maybe I have to use another service in the Application Pool?

     

    I made a cross test and test if I can work with my second VM and it goes well.

    I build I little project (on the VM_1) in Visual Studio and had no problem to work with the SQL Server on the VM_2. I tested database and the analysis engines.

     

    Thanks a lot and your explanation is very good. I located everything ….

    Where have you all these information from?

    Yes I have found this side but there is not so much content.

     

    Greetings,

    Alex

    Thursday, November 1, 2007 1:20 PM
  • Hi Alex

     

    Have to admit I'm a bit stumped on this one.  The only other thing I can suggest is to set up a user as a full administrator on the AS box and use that as the App pool identity

     

    Regards

     

    Tim

     

    Thursday, November 1, 2007 2:54 PM
  • Just to clarify under AS box you understand the second virtual machine with Analysis Services?

     

    There is one more question I don’t understand.

    If I configure for example a user called user1, have a user called user2 the possibility to login to the PerformancePoint Server and work with the Analysis Service although in the PPSMonitoringWebServices just user1 is configured?

     

    Thanks,

    Alex

     

    Thursday, November 1, 2007 3:15 PM
  • Hi Alex

     

    Yes thats correct - the second virtual machine with Analysis Services.

     

    Re your question, the User1 is the user that will make the actual connection to and read AS regardless of the user that is veiwing the report.

     

    You can configure it to use a connection per user by changing the Bpm.ServerConnectionPerUser setting in the monitoring server web.config but if you are running the AS server seperate to the PPS server then you will have to implement Kerberos Authentication which is not an easy task!

     

    However if you want to use AS roles (e.g user1 can see different data to user2) you will have to use this latter option

     

    Let me know how you get on

     

    By the way once you have the App pool identity correctly set you shouldn't have to use the connection string type connection, you should be able to simply type the name of your second virtual machine in the server box on the data source and any databases will then be shown in the drop down below

     

    Thanks

     

    Tim

     

    Thursday, November 1, 2007 3:45 PM
  • One other thing to check is the application log in windows event viewer

     

    Thursday, November 1, 2007 4:16 PM
  • Good Morning Tim,

     

    Okay thank you for the explanation. Yes I would like to use different roles, but its a problem for later.
    I think I have to resolve the simple problem at first before I try to upgrade my program.

     

    Summary:

    VM1(PPS)                            VM2(AS box)
    USER1(Administrator)           USER2(Administrator)
    App pool ->
    Identify -> Username: USER1

     

    I tried different Usernames in identify tab (domain\username):
    vm1\user1
    vm2\user2
    user2

     

    I looked up the event viewer and there are no relevant errors.

     

    I have to say thanks Wink

     

    Thanks,
    Alex

    Friday, November 2, 2007 8:48 AM
  • Morning Alex

     

    You seem to have everything set up correctly from an App Pool identity  So a few more questions. 

     

    Can I just confirm that you are logged onto dashboard designer on the VM1 box and as the Administrator User1?

     

    Also which server is the PPSMonitoring SQL database installed on? And is AS installed on VM1?

     

    Also, excuse the obvious question but you are running the RTM version?

     

    Thanks

     

    Tim

     

     

    Friday, November 2, 2007 9:18 AM
  • Hello Tim,

     

    Yes I’m logged in and working on the VM1 with the account USER1.

    AS is installed on VM1 and is running.

    The database PPSMonitoring is installed on VM1.

     

    I don’t know what you mean with RTM. Google said that RMT is “release to manufacturing”. It’s something like test version? Then yes.

     

    Thanks a lot,

    Alex

    Friday, November 2, 2007 10:22 AM
  • Yes RTM is release to manufacturing - basically the latest available version rather than the CTP\Beta versions.

     

    http://www.microsoft.com/downloads/details.aspx?FamilyId=6372C24F-67DD-42DD-B034-748907B23420&displaylang=en

     

    I'm starting to run out if suggestions now! 

     

    Can you connect to any cubes on the VM1? 

    User1 definitely has access to Cubes on VM2?

    Have you tried using IP address instead of server name?

     

    My last suggestion would be to do a complete uninstall\re-install of monitoring on the VM1!

    Friday, November 2, 2007 11:20 AM
  • Hello Tim,

     

    I'm starting to run out if suggestions, too.

    Today I tried to work with Trace (SQL Server Tool) on VM2 and look what happens when I try to access from VM1.

    Then I try to work with “stored User Names and password” in the settings. But this didn’t bring the expected result too.

     

    With the little Project in Visual Studio on VM1 I can deploy and browse a Cube on VM2.

    No, I didn’t try to work with IP addresses. (I thought I can make minimum one Dashboard this weak and now I lost much time with this connection problem. Now I have a new issues with the Dashboard … after try this new trick I will write the result)

     

    Right now I bring the database and the Cube from VM2 to VM1 and would try to work there with it.

     

    Could be that the error lies on the RTM version, I hope so.

     

    Don’t worry and have a nice day!

    Thanks,

    Alex

    Friday, November 2, 2007 11:40 AM
  • Hi Alex,

    Have you tried connecting to the cube without actually building a connection string manually. Try using the data source GUI and test the connection that way.

    Also, try using the App Pool identity account (User1) in applications other than Dashboard Designer in order to connect to the cube. Use Run As... to attempt to connect to the cube in Excel or SSMS.

    Cheers,

    Nick

    Saturday, November 3, 2007 12:42 AM
  • Hello Nick,

     

    Yes I did. But always came the same error.

    To working with SSMS and VS going well with the Cube from VM1 on VM2.

     

    Best Regards,

    Alex

     

    Monday, November 5, 2007 11:20 AM
  • Sorry Alex, I didn't understand. Are you saying that you were not having any problems accessing the cube while impersonating the user?

     

    If you are not able to successfully browse the cube as the app pool identity user then this is where you need to focus your attention. Forget about PPS for now. You should be concentrating on accessing the cube data "as" the application pool account. Once you have resolved this issue you should try connecting to the cube with PPS.

     

    Cheers,

    Nick

    Monday, November 5, 2007 9:10 PM
  • Hello Tim and Nick,

    I found my mistake. I set the wrong setting into PPSMonitoringWebService.
    Yesterday I tried to set the user to “vm2\user” (computer name of the AS box \ username). Now I can connect to the cubes form VM1 to VM2

     

    Thanks a lot,
    Alex

    Friday, November 9, 2007 7:24 AM
  •  

    HI tschi2001 ,

     

    Where can I find Dashboard Designer to downlaod or install? Is it in some microsoft products?

     

    Thanks .

     

    Robin

    Thursday, February 14, 2008 9:21 PM
  • RobinSong, DashboardDesigner is a ClickOnce application i.e. it has a web-based install mechanism. Once your Monitoring Server is installed browse to Monitoring Central at http://<YouServerName>:40000/Central and you will find an installation link. You should also find a link to the Monitoring Central from the Start menu on the server you installed on (Start > Programs > Microsoft Office PerformancePoint Server 2007 > Monitoring Central)

     

    In future, please create a new thread, that way other members of the forum will see your question and have an opportunity to help you.

     

    Cheers,

    Nick

    Thursday, February 14, 2008 9:35 PM
  •  

    Thank you Nick!

     

    Robin

    Sunday, February 17, 2008 4:00 PM
  • 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.

    Tuesday, December 8, 2009 12:00 PM