locked
Connecting to cubes RRS feed

  • Question

  • Hi,

     

    i have been trying to connect to DashBoard designer to the cubes on the server. First i cannot see the performance cubes when i create a new analysis connection, and then i also get this 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."

     

    I am in the administrators group on the server, also in sql users and global admin and all of admin roles in performance point.

     

    So please help me how to connect dashboard designer to my perfpoint cubes?

     

    Thank you

     

    Marko

     

    Tuesday, December 18, 2007 2:57 PM

Answers

  • Marko,

     

    If you have not changed any of the installation configurations then it sounds like your problem is simple - it is the NT Authority login that does not have the right access. This is the default application security configuration for PPS - the application pool account must have access to connect to the data source. I thought you read my blog post on the topic as per Tim's recommendation earlier in this same thread (http://nickbarclay.blogspot.com/2007/11/pps-data-source-connection-problems.html

     

    Please add NT AUTHORITY\NETWORK SERVICE to the same cube role you are in. Ensure the role is properly deployed to the Analysis Server and try connecting again, I think you may have more luck this time.

     

    Let me know how you go.

     

    Cheers,

    Nick

     

    Thursday, January 17, 2008 9:25 AM

All replies

  • Hi Marko

     

    I suspect it's due to the Application Pool ID you're using to connect.

     

    Have a read of Nick's document which should give you the answer:

     

    http://nickbarclay.blogspot.com/2007/11/pps-data-source-connection-problems.html

     

    Hope this helps

     

    Tim

     

    Tuesday, December 18, 2007 3:07 PM
  • Hi Tim,

     

    thank you very much. Looking @ the blog right now. Will post wether this helped.

     

    Thank you once again

     

    Marko

     

    Tuesday, December 18, 2007 3:43 PM
  • Hi,

     

    i have read the blog, and it did helped a bit. checked the IIS application pool, and all monitoring iis services had network services identity. planning iis services were on PPS_planning identity. So i have changed monitoring iis to PPS_monitoring identity which was created before the installation of PPS. so now i have another message that comes up:

     

    "The database connection failed. Please contact your administrator."

     

    I checked with PPS_monitoring identity if i can open app database and the cubes, and i can do that. and i can browse the cubes. so it is not the problem in sql and olap permissions of PPS_monitoring user.  

     

    So what did i do wrong? Which identity should PPS monitoring use? What permissions should have the PPS monitoring identity?

     

    PPS_monitoring indentity is created on the network and i think added to the sql network group.

     

    Please help.

     

    Thank you

     

    Marko

     

    Wednesday, December 19, 2007 9:56 AM
  • Maybe a dumb question, but is your dashboard on the same server as your cube? if not, did you run a trace on SQL to see which user tries to log on when you open the dashboard? might be its not using the PPS_monitoring user.

     

    Also, the user has rights on the DB? Or on the cube itself? I myself have been searching a while when I had some security issues, turned out I only allowed my user to connect to the DB, but not on the cube...

    Wednesday, December 19, 2007 10:49 AM
  • In addition to Morion's suggestion - try starting Excel using "Run As" (right click o the icon) and log in using the user who you have set in the PPS monitoring App Pool.  Then connect to the cube in Excel

     

    Also apologies if it's stating the obvious but make sure you run IISreset when you change the App Pool (I think recycle the app pool will do the job but never sure!)

     

    Let us know how you get on

     

    Wednesday, December 19, 2007 11:00 AM
  • Hi,

     

    forgot to say that. Monitoring, Dashboard and Planning are on the same server. I went to check on permission of the PPS_m user and i logged onto the server as PPS_M and was able to acces the cubes and the db.

     

    PPS_m is in the groups: IIS_WPG (IIs work process group), PPS3PlanningWindowsService on the local machine. in other groups such as the sql and olap groups either network service is used or the network sql group identity.

     

    Where should PPS_M be added? In which groups?

     

    Thank You

     

    Marko

     

     

    Wednesday, December 19, 2007 11:08 AM
  • Hi,

     

    i did all of that. I changed the PPS monitoring App pool, and then run iisreset, and then logged of the system and logged onto the server as the pps m user and accessed the cubes and the database. So i do not think that the pps_m user identity does not have the right read prermissions on the cube and the database.

     

    Then i logged in back as the admin and tryed to run dashboarding and got the above mentioned message.

     

    I know that its probably an obvious solution to this problem but at this moment i just cant see it.

     

    Thank you

     

    Marko

     

    Wednesday, December 19, 2007 11:13 AM
  • Hi Marko

     

    Who are you logging on to Dashboard Designer as?

     

    In DD if you click the Office icon top left, then options, then the server tab what happens when you click the connect button?

     

     

     

    Wednesday, December 19, 2007 11:19 AM
  • Hi,

     

    I am loggin as the Windows server administrator (a domain user with admin permissions on the local machine). This user is also the global admin for planning application, also user, data and model admin.

     

    I went to DD options and was able to connect to the server. the only thing is that i went to the permissions settings and my user was no there. Is this perhaps the problem?

     

     

    Thank you

     

    Marko

    Wednesday, December 19, 2007 11:30 AM
  • Hi

     

    local admins are automatically added to the Dashboard design admin group so that's not a problem. 

     

    Did you try Morion's suggestion of running a SQL profiler trace?

     

    Bit of a long shot but also have a look in the event viewer for any errors

     

    Tim

    Wednesday, December 19, 2007 11:50 AM
  • Hi,

     

    i have just ran the sql profiler and saw that i was trying to connect to the cubes as my PPS_M account, part of performancepoint.

     

    I went to the event viewer, and i can see the next error:

     

    Event Type: Error
    Event Source: PerformancePoint Monitoring Server
    Event Category: None
    Event ID: 200
    Date:  12/19/2007
    Time:  12:13:34 PM
    User:  N/A
    Computer: SERVER

    Description:
    The database connection failed. Please contact an administrator.
    System.Data.SqlClient.SqlException: Login failed for user 'DOMAIN\PPS_M'.
       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at Microsoft.PerformancePoint.Scorecards.Server.Dao.BaseDao.GetConnection()

     

    this it the error that is reported in event viewer. Is there a chance that we did not install the monitoring and dashboard correctly? Perhaps i might reinstall this?

     

    Thank You

     

    Marko
     

     

    Wednesday, December 19, 2007 1:06 PM
  • Hi Marko

     

    This looks like the PPS_M user does not have the relevant access to (I'm guessing) the PPS_Monitoring SQL database (this is the Monitoring System database).

     

    Can you grant PPS_M full access to this database and try again?

     

    Cheers

     

    Tim

     

    Wednesday, December 19, 2007 1:13 PM
  • Hi,

     

    ok, i have added the PPS_M to the server login, and then added it to PPSMonitoring database. I restarted sql and olap services and iis, and then tryed to connect to the server. also added pps_m to all admin roles for that database.

     

    I got this message:

     

    "An unknown error has occurred. If the problem persists contact an administrator. There may be additional information in the server application event log."

     

    in the event log is this:

     

    Event Type: Warning
    Event Source: PerformancePoint Monitoring Server
    Event Category: None
    Event ID: 0
    Date:  12/19/2007
    Time:  2:36:03 PM
    User:  N/A
    Computer: SERVER
    Description:
    The EXECUTE permission was denied on the object 'SettingGetAllCurrent', database 'PPSMonitoring', schema 'dbo'.

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

    and then this:

     

    Event Type: Error
    Event Source: PerformancePoint Monitoring Server
    Event Category: None
    Event ID: 0
    Date:  12/19/2007
    Time:  2:36:03 PM
    User:  N/A
    Computer: SERVER

    Description:
    System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'SettingGetAllCurrent', database 'PPSMonitoring', schema 'dbo'.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader()
       at Microsoft.PerformancePoint.Scorecards.Server.SettingsDao.GetSettings()
       at Microsoft.PerformancePoint.Scorecards.Server.Settings.LoadTable()
       at Microsoft.PerformancePoint.Scorecards.Server.Settings.GetString(String key)
       at Microsoft.PerformancePoint.Scorecards.Server.PmServer.Initialize()


     

     

    Thank you for your patiente with me,

     

    Marko

     

    Wednesday, December 19, 2007 1:42 PM
  • Hi Marko

     

    No problem - glad to help

     

    We're defintiely making some progress!  The correct thing to do here would be to re-run the Monitoring Configuration tool and make sure you configure the Monitoring database correctly. 

     

    However to prove the problem is permissions the easiest way will be to make PPS_M a SQL sysadmin on the server(assuming you're not working on a Production box).  If this works then you can work backwards and reduce the PPS_M permissions until you find the problem

     

    HTH

     

    Tim

    Wednesday, December 19, 2007 1:51 PM
  • Hi Tim,

     

    no luck there either. i get the same message from DD about the application pull.

     

    but i got this error in event viewer:

     

    Event Type: Error
    Event Source: Perflib
    Event Category: None
    Event ID: 1023
    Date:  12/19/2007
    Time:  2:54:57 PM
    User:  N/A
    Computer: SERVER
    Description:
    Windows cannot load extensible counter DLL MSSQLServerOLAPService, the first DWORD in data section is the Windows error code.


    Data:
    0000: b1 36 00 00               ±6..   

     

    could this be the problem with olap perhaps? and i'm thinking of rerunning the monitoring configuration at this point. cause i cant figure it out why this all does not work. have to first back up all the business rules (downloaded the hotfix for excel addin) and want to test first that and will do the monitoring part later today or tomorrow morning and let you know if i had any success.

     

    Thank you once again for all the help today,

     

    Marko

     


     

     

    Wednesday, December 19, 2007 2:09 PM
  • Hi Marko,

     

    When it comes to Cubes, I usually check the basics.

    Since PPS uses App Pool Account to Access the Cube while In Dashboard Designer.

    Please Verify that the App Pool Account had been granted access to the Cube.

     

    Most of us use "Network Service" as a login default (To enable Kerberos support).

    Please Verify the Following Cube Issues:

    1. Your Analysis Database has Security Role with read definition.

    2. The Role has the Network Service or the equivalent login from the PPS application Pool.

    3. Verify that your Cube has been granted "Read" definition within this role.

    4. Verify that the Cube is in a processed mode.

     

    Additional thought might be to check under the webconfig of the PPS Monitoring Service:

    "BPM Connection per User = True". This section should be found under App Settings within the config file.

    It would ensure that the correct Logon of the user is transferred to the cube.

     

    I hope this helped a littlie bit.

     

    If my answer helped you please click the "yes" button on top.

     

    Eran

     

    Wednesday, December 19, 2007 2:29 PM
  • Hi,

     

    i have done tryed all the things above, and also no luck there.

     

    i have returned the app pool to network service, checked the app and monitoring database, and the network service has read rights to the app db and monitoring db, also has the right to app db in analysis services. i just cant see the network service as a role in the cubes, but there i only have the defined admins in planning console.

     

    i also changed "BPM Connection per User = True". in web congif file. and that also did not change the fact that i cant log onto the server through dashboard designer. after every change i restarted all the services. 

     

    also gave sysadmin and after that all the control to network service and to pps_m account to the SQL server, and that did not help also.

     

    i will install everything again (monitoring part and planning) to a new machine (perhaps we made a mistake when installing monitoring part - as planning is working normally). so i will let you know if i made any progress, as i need to give the users at our client a chance to test the planning part. 

     

    Thank you once again for trying to help me here, i will pay attention to all the things you have suggested while installing the monitoring part.

     

    Thank you and your efforts are much appreciated

     

     

    Best regards

     

    Marko 

     

    Thursday, December 20, 2007 10:24 AM
  • Marko, when you granted the PPS_M account access to the PPSMonitoring database did you add it to the BPMDeveloper role?

     

    Cheers,

    Nick

    Thursday, December 20, 2007 6:48 PM
  • Hi,

    Sorry for my late reply. Had some problems lately openning ms forum, and also the holyday. So first MERRY X-MAS to all!.

    Ok, Nick, i did add the PPS_M account to BPMDeveloper role. Tried first that and afterwards i tried to add the account to all roles, but could not connect to the cubes.

    I am working with Evaluation version of PPS. Perhaps that is the problem. I will reinstall the monitoring part and then try again.

    Thank you all for your help and advice. Will post about my progress after i reinstall the monitoring part.

    Cheers

    Marko
    Wednesday, December 26, 2007 4:13 PM
  • Hi,

    Ok i have installed PPS rtm version (or i think its the one - i have downloaded it from MSDN partner subscription site).

    I did everything by the book. I am the admin on this machine where SQL server is installed and can do what ever i want. I installed PPS Monitoring using Network Services account to be used as app pool.

    I have the PPS CTP4 virtual machine and there dashboarding works. So i checked all the settings and they are the same as on my system.

    But i get the same error as before:

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

    I have checked all the users permissions and they do have the right to see the data source.

    So really i do not know what to do.

    Please help me.

    Thank you in advance


    Marko


    P.S. i have checked all the things that are stated in the posts before this one (and i thank you once again for the guidance). and yes i have restarted the computer after installation.



    Wednesday, January 16, 2008 12:45 PM
  • Marko, have you traced your analysis server to determine which account is making the connection?

     

    Have you got roles defined on your cube which grant specific user access to the cube. If so are you referencing the role names in the data source?

     

    Cheers,

    Nick

    Wednesday, January 16, 2008 6:48 PM
  • Hi Nick,

    i went to activity monitor and saw that NT AUTHORITY\NETWORK SERVICE is connecting to PPS Monitoring database.

    i went to check the roles defined for my cube and i'm the only one there (PERFORMANCEPINT@DOMAIN@MARKO). i checked the permissions of this role and i have all the read/write rights to all of the cubes and dimensions aswell as drillthrough. Also i have not made any changes to the default permissions set by PPS during installation.

    And i checked the membership of the role and there i am (DOMAIN\Marko). And i am in the admin group for SQL server.

    I just do not know where i did a mistake while installing PPS Monitoring. I followed the install guide. It should work. There is no logic in this message that App pool cant connect to the Analysis server. And i'm connected to my network.

    i do not understand this.

    thank you

    Marko
    Thursday, January 17, 2008 9:11 AM
  • Marko,

     

    If you have not changed any of the installation configurations then it sounds like your problem is simple - it is the NT Authority login that does not have the right access. This is the default application security configuration for PPS - the application pool account must have access to connect to the data source. I thought you read my blog post on the topic as per Tim's recommendation earlier in this same thread (http://nickbarclay.blogspot.com/2007/11/pps-data-source-connection-problems.html

     

    Please add NT AUTHORITY\NETWORK SERVICE to the same cube role you are in. Ensure the role is properly deployed to the Analysis Server and try connecting again, I think you may have more luck this time.

     

    Let me know how you go.

     

    Cheers,

    Nick

     

    Thursday, January 17, 2008 9:25 AM
  • Hi Nick,

    yes it was that simple. i created a new role and added network service to it and now it works. i thought that PPS handles roles during installation and development of models.

    Thank you for you answer and all the help

    Cheers

    Marko
    Thursday, January 17, 2008 11:36 AM