none
PPS M&A and Reporting Services 2005 - Error in filters due to security RRS feed

  • Question

  • I  have a reporting services report which was developed in SSRS2005. While trying to create a SQL server report in PPS M&A it threw an unhandled exception saying "few components of ReportViewer were missing". I found a work around for this issue on the Net suggesting to use Report viewer redistributable 2008. 

    After having Reportviewer redistributable 2008 installed the exception was gone but while trying to access the report in "Report center" or "Native mode" it threw an error "Unable to connect to server". I changed the Server mode from "Report center" to "SharePoint Integrated Mode" .

    Now when I try to Preview the dashboard which has the Reporting services report and two filters created using "MDX selection" and displays the value in a Tree View I get the following error 

    "No selections available. Contact your system administrator for assistance.
    Contact the administrator for more details."

    The issue is with the way the datasource is getting accessed because in the event viewer under applications this has been recorded as a warning and the details are

    An exception occurred while querying the named set with expression '{DESCENDANTS({[Product].[Product].[Company].members},[Product].[Product].[Brand],SELF_AND_BEFORE) ,[Product].[Product].[ALL]}'. The following information may help diagnose this problem:\r\n Microsoft.PerformancePoint.Scorecards.BpmException: 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. ---> System.ArgumentException: The connection string is not valid.

    I am able to access and query the cube from SSMS. I am not quite sure as to where the issue is.

    We have enabled Kerberos and have also set bpm.ServerConnectionPeruser to "True" in Preview,Webservice,Sharepoint site web config.

    Can you please help because we are in the middle of a deployment and the schedule is getting delayed because of this particular issue.

    Tuesday, June 23, 2009 5:50 AM

Answers

  • Hi,

    Just a few checks

    1)   What happens when you create a separate Tree view filter and don't link to the report.
    2)  When you have enabled kerberos you should not specify any IP name to connect to the data source. It has to be only the host name or the computer name.


    regards,
    Ram
    • Marked as answer by Aparna Anand Thursday, July 2, 2009 11:08 AM
    Tuesday, June 23, 2009 7:53 AM
  • When you are trying to setup the filter run a Profiler trace on the SSAS instance and check to see what credentials are being passed.  You might need to verify that Kerberos was setup properly.  If you have any questions about the configuration you can take a look at this video - Configuring Kerberos Delegation with PerformancePoint Monitoring Server.
    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Marked as answer by Aparna Anand Thursday, July 2, 2009 11:08 AM
    Tuesday, June 23, 2009 10:29 AM
  • Hi,

      This means that the credentials are not being passed from one server to another. I would suggest you to use the following tool to debug kerberos issues.

    http://blogs.iis.net/brian-murphy-booth/archive/2007/03/09/delegconfig-delegation-configuration-reporting-tool.aspx

    Regards,
    Ram
    • Marked as answer by Aparna Anand Thursday, July 2, 2009 11:10 AM
    Tuesday, June 23, 2009 11:52 AM
  • Have you tried using the fully qualified DNS name at all?  So you would connect with something like <hostname>.<domainname>.com or something along those lines.  If you use the IP address that will bypass Kerberos.

    Kerberos Authentication and Delegation in Analysis Services 2005
    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Marked as answer by Aparna Anand Thursday, July 2, 2009 11:09 AM
    Thursday, June 25, 2009 2:25 PM
  • Thank you for all the help.I got it working at last. My Kerberos configuration was correct and thanks for all the help.

    I had given admin rights on ths ssas database for all authenticated users for testing purpose. The test user had minimal previleges on the server and had browser permisson on the reports.

    For the last issue I had given the Problematic user id Reader access on the PPS M&A datasource connection object where as the test user for which the dashboard was working had editor access. I changed it from 'Reader' to 'Editor' for the problematic user and the dashboard started working. I have given 'editor' access for all authenticated users on the datasource obejct.


    • Marked as answer by Aparna Anand Thursday, July 2, 2009 11:08 AM
    Thursday, July 2, 2009 11:05 AM

All replies

  • The report shows up when the Filters are removed or if the filters are of the type List instead of Tree view.

    Tuesday, June 23, 2009 7:25 AM
  • Hi,

    Just a few checks

    1)   What happens when you create a separate Tree view filter and don't link to the report.
    2)  When you have enabled kerberos you should not specify any IP name to connect to the data source. It has to be only the host name or the computer name.


    regards,
    Ram
    • Marked as answer by Aparna Anand Thursday, July 2, 2009 11:08 AM
    Tuesday, June 23, 2009 7:53 AM
  • Thank you for your response. The filter works when it is not linked to the report.

    Currently I have given the IP to connect to the source server. I will change this and let you know.
    Tuesday, June 23, 2009 9:29 AM
  • When you are trying to setup the filter run a Profiler trace on the SSAS instance and check to see what credentials are being passed.  You might need to verify that Kerberos was setup properly.  If you have any questions about the configuration you can take a look at this video - Configuring Kerberos Delegation with PerformancePoint Monitoring Server.
    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Marked as answer by Aparna Anand Thursday, July 2, 2009 11:08 AM
    Tuesday, June 23, 2009 10:29 AM
  • Thank you all. I am not quite sure how this helped but I removed "Internet Explorer Enhanced security Configuration" from add or remove windows components. I had to do this because while trying to view the report without the filters it displayed the page with 'X' boxes where the charts were supposed to be displayed.

    Charts being retrieved as images from the remote report server were blocked due to this IE enhanced security configuration component.

    I restarted IIS after removing it. On trying to preview the dashboard with the filters it worked perfectly fine.

    Please let me know if you are aware of the reason behind the Data source access issue and IE Enhanced security configuration.
    Tuesday, June 23, 2009 11:13 AM
  • I am sorry I dint realise that I was given Administrative previleges. It worked because I was included in the administartor group but it gives me the same error when I try to login as a different user.

    I ran the profiler and found that it gives me the error immediately after Audit Logon where it uses "anonymous user" to login.

    Please help.
    Tuesday, June 23, 2009 11:34 AM
  • Hi,

      This means that the credentials are not being passed from one server to another. I would suggest you to use the following tool to debug kerberos issues.

    http://blogs.iis.net/brian-murphy-booth/archive/2007/03/09/delegconfig-delegation-configuration-reporting-tool.aspx

    Regards,
    Ram
    • Marked as answer by Aparna Anand Thursday, July 2, 2009 11:10 AM
    Tuesday, June 23, 2009 11:52 AM
  • I initially thought that something was wrong with our Kerberos setup but now I am having a wierd issue.

    As mentioned above with Administrative previleges I was able to deploy my dashboards to the SharePoint page.  Now say 'LoginX' is an Administrator and 'LoginY' is a normal user.

    When I login as LoginX on the server and to the Sharepoint site everything works fine(both filters and Report). Same happens when I login as LoginY on the server and SharePoint site. Everything works fine.

    Issue is When I login to the server as 'LoginX' and access the SharePoint site using 'LoginY' then my filters show the error mentioned above.

    "No selections available. Contact your system administrator for assistance.
    Contact the administrator for more details."

     Same happens when I login as 'LoginY' to the server and access SharePoint site as 'LoginX'.

    We have our SSAS database(Database box) on a separate server and SharePoint,PPS M&A(Application Box) on a separate server. When I try to access the SSAS database from SSMS on the Applicatoin Server I get an error saying 'Authentication failed. Target Pricipal name is incorrect'. This happens when I use the HostName to connect to the SSAS server. When I try connecting using the IP Address it connects immediately. I have checked the DNS entries using nslookup and everything seems to be fine.

    Can you please tell me as to why I am unable to connect to my SSAS database using Hostname from a Remote server and If my issue with the PPS M&A dashboard is related to this?

    Wednesday, June 24, 2009 8:25 AM
  • Hi Aparna,

     
       I would like you to test the application in different way.

    1) Please do not use IP to connect to any data source. If you use it your kerberos is likely to fail. we had a similar issue .We had used IP to connect to the data source from PPS and we got the above error "No selections available" . So we changed it to the host name. I think so its not able to resolve between the IP and the hostname. if you say that its not able to connect using host name and with IP , the how are your SPN's created . have you used IP or the host name.

    2) I would suggest you to test the application from the client machine and not from the server. Access the URL from the client machine.


    Regards,
    Ram
    Wednesday, June 24, 2009 4:11 PM
  • The spn's were created using Hostnames. I am not quite sure as to why I am not able to connect to the SSAS instance using its Hostname. I am forced to use IP address in that case because creating the datasource in PPS M&A was not possible.

    I have tested the application from the client side as well. When I am logged on as an Adminitrator to the sharepoint site evreything seems to work but when I login as any other user it gives me the same error. I wanted to test if Kerberos for my Sharepoint site is set correctly from the client server. I created an analysis services filter and tested it. For an admin account analysis services filter works fine but gives me an error saying "Unable to retrieve filter values" when logged in as a normal user.

    The security tab in event viewer shows an extra entry for administrator called "Special privileges assigned to new logon
    Privileges: SeImpersonatePrivilege
       SeSecurityPrivilege
       SeBackupPrivilege
       SeRestorePrivilege
       SeTakeOwnershipPrivilege
       SeDebugPrivilege
       SeSystemEnvironmentPrivilege
       SeLoadDriverPrivilege

    This entry is not present for a Normal user but it is always a Successful Network Logon using Kerberos.

    I generated a report using the application in the link you had specified. it has three red crosses.

    Authentication method: it says browser is using Basic authentication to connect to IIS. But i have specifically changed the option in IE to use windows Integrated authentication.

    Impersonation level: Impersonate. I gues it should be delegation instead but I am not sure where to change it. 
     
    Will the delegation succeed? : It says with the current configuration delegation will not succeed.

    Please let me know if you are aware of any fixes for these. let me know if you need more information
    Thursday, June 25, 2009 6:54 AM
  • Have you tried using the fully qualified DNS name at all?  So you would connect with something like <hostname>.<domainname>.com or something along those lines.  If you use the IP address that will bypass Kerberos.

    Kerberos Authentication and Delegation in Analysis Services 2005
    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Marked as answer by Aparna Anand Thursday, July 2, 2009 11:09 AM
    Thursday, June 25, 2009 2:25 PM
  • I have tried all the alternatives. I tested the application from a client system using the domain account (domain account for the services). We have all our services running under the same domain account.

    It works. I tested it using a user account that I had used for testing earlier and it works. 

    But thats it. Looks like it works only for these two user accounts and nothing else. I have checked the trace and Event log security tab. It says Kereberos when I try to login using the two user accounts, but shows an Anonymous Logon with other accounts. Can you please explain this behaviour?

    I am not quite sure what to fix and change. I have used HostNames in all the connection strings and the SPN's for the application are present.

    Please help.
    Tuesday, June 30, 2009 10:51 AM
  • hi,

      Just a few checks. When you say for one test user the reports are appearing fine, what is the permission level set for that user other than the administrator. Just to clarify is the user that you are testing which is giving an error is a valid domain account?? What is the permission level for those users that are given at the cube level. If you have created a role in SSAS what is it that you have given in the membership role. What happens when you create a filter in PPS and then try to access this filter in the preview mode or sharepoint with the problematic user?? Also what happens when you try to browse the SSRS report in the report manager with the problematic user??

    regards,
    Ram
    Tuesday, June 30, 2009 5:27 PM
  • Thank you for all the help.I got it working at last. My Kerberos configuration was correct and thanks for all the help.

    I had given admin rights on ths ssas database for all authenticated users for testing purpose. The test user had minimal previleges on the server and had browser permisson on the reports.

    For the last issue I had given the Problematic user id Reader access on the PPS M&A datasource connection object where as the test user for which the dashboard was working had editor access. I changed it from 'Reader' to 'Editor' for the problematic user and the dashboard started working. I have given 'editor' access for all authenticated users on the datasource obejct.


    • Marked as answer by Aparna Anand Thursday, July 2, 2009 11:08 AM
    Thursday, July 2, 2009 11:05 AM