none
Most reports work, some display an error RRS feed

  • Question

  • I have randomly running through different reports after getting and error when trying to execute one report and am noticing that there are several (haven't gone through all of them yet) that throw an error when trying to run them from the Admin Console or from the SRS reporting website.

    Site server is Server 2008 R2, SQL 2012 CU2.

    Here's an example of a report that fails: "Software Distribution - Application Monitoring>Application Infrastructure errors"

    From the console when clicking run I get this error immediately:

    An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'ApplicationNames'. ---> System.Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors

    -------------------------------
    Microsoft.ConfigurationManagement.ManagementProvider.SmsException
    An error has occurred during report processing.


    Stack Trace:
       at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ParameterPresenter.GetParameters()
       at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ParameterPresenter.LoadParameters(IReport report, Collection`1 navigationParameters, IResultObject resultObject)
       at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ReportViewerPresenter.Worker_DoWork(Object sender, DoWorkEventArgs e)
       at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
       at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

    -------------------------------

    System.Web.Services.Protocols.SoapException
    An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'ApplicationNames'. ---> System.Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors

    Stack Trace:
       at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ParameterPresenter.GetParameters()
       at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ParameterPresenter.LoadParameters(IReport report, Collection`1 navigationParameters, IResultObject resultObject)
       at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ReportViewerPresenter.Worker_DoWork(Object sender, DoWorkEventArgs e)
       at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
       at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

    The same report from the srs website:

    • An error has occurred during report processing.
      (rsProcessingAborted)
      • Query execution failed for dataset
        'ApplicationNames'. (rsErrorExecutingCommand)
        • For more information about this error navigate to
          the report server on the local server machine, or enable remote errors

    An example of a report that works just fine is "Software Updates - A Compliance>Compliance 1 - Overall compliance"

    Any ideas where to start troubleshooting this?

    Thanks!



    Thursday, March 21, 2013 9:47 PM

Answers

  • So it does appear to be missing permissions..  We found a Russian article that we translated with Google to English.  Here's the link:

    http://translate.google.com/translate?hl=en&sl=ru&u=http://bazanovv.livejournal.com/&prev=/search%3Fq%3D%2527fnGetSiteNumber%2527,%2Bdatabase%26hl%3Den%26biw%3D1536%26bih%3D788&sa=X&ei=tXdMUbK6IYOcyQG1z4CgAQ&ved=0CD8Q7gEwAQ

    Here's the article itself..

    Reports category Software Distribution - Tracking applications Software Distribution - Deploy packages and programs are empty (no application), although in fact the application is successfully deployed.

     Open the report all deployment (base) category Software Distribution - Tracking applications, poke in a choice of - the application, we get an error SQL:
     System.Web.Services.Protocols.SoapExcept ion: An error has occurred during report processing.  ---> Microsoft.ReportingServices.ReportProces sing.ProcessingAbortedException: An error has occurred during report processing.  ---> Microsoft.ReportingServices.ReportProces sing.ReportProcessingException: Request failed for the data set "FirstMatchingAppOrCollection".  ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'fnGetSiteNumber', database 'CM_CM2', schema 'dbo'.
     in Microsoft.ReportingServices.Library.Repo rtingService2005Impl.GetReportParameters (S tring Report, String HistoryID, Boolean ForRendering, ParameterValue [] Values, DataSourceCredentials [] Credentials, ParameterInfoCollection & Parameters)
     in Microsoft.ReportingServices.WebServer.Re portingService2005.GetReportParameters (S tring Report, String HistoryID, Boolean ForRendering, ParameterValue [] Values, DataSourceCredentials [] Credentials, ReportParameter [] & Parameters)

     -------------------------------
     Microsoft.ConfigurationManagement.Manage mentProvider.SmsException
     An error has occurred during report processing.


     Stack Trace:
     in Microsoft.ConfigurationManagement.AdminC onsole.SrsReporting.ParameterPresenter.G etParameters ()
     report, Collection `1 navigationParameters, IResultObject resultObject)
     sender, DoWorkEventArgs e)
     in System.ComponentModel.BackgroundWorker.O nDoWork (DoWorkEventArgs e)
     in System.ComponentModel.BackgroundWorker.W orkerThreadStart (Object argument)

     -------------------------------

     System.Web.Services.Protocols.SoapExcept ion
     --->  --->  ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'fnGetSiteNumber', database 'CM_CM2', schema 'dbo'.
     in Microsoft.ReportingServices.Library.Repo rtingService2005Impl.GetReportParameters (S tring Report, String HistoryID, Boolean ForRendering, ParameterValue [] Values, DataSourceCredentials [] Credentials, ParameterInfoCollection & Parameters)
     in Microsoft.ReportingServices.WebServer.Re portingService2005.GetReportParameters (S tring Report, String HistoryID, Boolean ForRendering, ParameterValue [] Values, DataSourceCredentials [] Credentials, ReportParameter [] & Parameters)


     Stack Trace:
     in Microsoft.ConfigurationManagement.AdminC onsole.SrsReporting.ParameterPresenter.G etParameters ()
     report, Collection `1 navigationParameters, IResultObject resultObject)
     sender, DoWorkEventArgs e)
     in System.ComponentModel.BackgroundWorker.O nDoWork (DoWorkEventArgs e)
     in System.ComponentModel.BackgroundWorker.W orkerThreadStart (Object argument)

     -------------------------------

     From which, after reading http://www.sqlteam.com/forums/topic.asp?T OPIC_ID = 98768 can be understood that the user who run reports (we have it sccm-report-user) does not have permission to perform integrated procedures in the main database SCCM (we have it CM_CM2).

     Quick fix - right at the base - properties - Permissions tab - select user sccm-report-user, in the column below Grant put the check in front of Execute - error disappears, reports work.

     A better fix - open base - Programmability - Functions - Scalar-valued - find fnGetSiteNumber, through security can see that the use must be a member as smsdbrole_DmpConnector (Device Management Point role), click Search, then Browse, scroll to the end of the list - there will be a role smsschm_users, of which the users of the report, select the part and give it the right to Execute column Grant.

    Once we did this against the object that was failing (fnGetSiteNumber, determined by the SQL log above) the reports started working.  Curious since I see this in my lab and in production if others see this as well though?  Either way this appears to have resolved our issue.  ;-)


    Friday, March 22, 2013 3:43 PM

All replies

  • You could check srsrp.log when the report is run, and look in smsdbmon.log to see if the site is having any issues in the database.

    Could get the SQL for the report and run it in Management Studio, provide the correct inputs, run it.


    Rob Marshall | UK | My Blog | WMUG | File CM12 Feedback | CM12 Docs | CM12 Release Notes

    Thursday, March 21, 2013 10:26 PM
  • Thanks Rob.  The logs appear to be good, no errors when running the report (that throws the error above).  I'll try the SQL in Management Studio next..
    Friday, March 22, 2013 2:33 PM
  • Interesting.. Here's the error in the SQL log file:

    processing!ReportServer_0-10!291c!03/22/2013-11:23:13:: w WARN: Data source 'AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_': Report processing has been aborted.
    processing!ReportServer_0-10!291c!03/22/2013-11:23:13:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: , Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'ApplicationNames'. ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'fnGetSiteNumber', database 'CM_P10', schema 'dbo'.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)


    I get the same error whether I use my Config Mgr administrator account OR the service account I installed  SCCM with.  Both have full admin privileges to SCCM.  Maybe some permissions didn't get applied properly during install?  I vaguely recall something similar to this in SCCM 2007..

    Friday, March 22, 2013 3:26 PM
  • I should also state, I see the exact same behavior in my SCCM 2012 SP1 Lab..
    Friday, March 22, 2013 3:28 PM
  • Can someone else please run the report "Application infrastructure errors"  and let me know if it executes correctly for you?
    Friday, March 22, 2013 3:29 PM
  • So it does appear to be missing permissions..  We found a Russian article that we translated with Google to English.  Here's the link:

    http://translate.google.com/translate?hl=en&sl=ru&u=http://bazanovv.livejournal.com/&prev=/search%3Fq%3D%2527fnGetSiteNumber%2527,%2Bdatabase%26hl%3Den%26biw%3D1536%26bih%3D788&sa=X&ei=tXdMUbK6IYOcyQG1z4CgAQ&ved=0CD8Q7gEwAQ

    Here's the article itself..

    Reports category Software Distribution - Tracking applications Software Distribution - Deploy packages and programs are empty (no application), although in fact the application is successfully deployed.

     Open the report all deployment (base) category Software Distribution - Tracking applications, poke in a choice of - the application, we get an error SQL:
     System.Web.Services.Protocols.SoapExcept ion: An error has occurred during report processing.  ---> Microsoft.ReportingServices.ReportProces sing.ProcessingAbortedException: An error has occurred during report processing.  ---> Microsoft.ReportingServices.ReportProces sing.ReportProcessingException: Request failed for the data set "FirstMatchingAppOrCollection".  ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'fnGetSiteNumber', database 'CM_CM2', schema 'dbo'.
     in Microsoft.ReportingServices.Library.Repo rtingService2005Impl.GetReportParameters (S tring Report, String HistoryID, Boolean ForRendering, ParameterValue [] Values, DataSourceCredentials [] Credentials, ParameterInfoCollection & Parameters)
     in Microsoft.ReportingServices.WebServer.Re portingService2005.GetReportParameters (S tring Report, String HistoryID, Boolean ForRendering, ParameterValue [] Values, DataSourceCredentials [] Credentials, ReportParameter [] & Parameters)

     -------------------------------
     Microsoft.ConfigurationManagement.Manage mentProvider.SmsException
     An error has occurred during report processing.


     Stack Trace:
     in Microsoft.ConfigurationManagement.AdminC onsole.SrsReporting.ParameterPresenter.G etParameters ()
     report, Collection `1 navigationParameters, IResultObject resultObject)
     sender, DoWorkEventArgs e)
     in System.ComponentModel.BackgroundWorker.O nDoWork (DoWorkEventArgs e)
     in System.ComponentModel.BackgroundWorker.W orkerThreadStart (Object argument)

     -------------------------------

     System.Web.Services.Protocols.SoapExcept ion
     --->  --->  ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'fnGetSiteNumber', database 'CM_CM2', schema 'dbo'.
     in Microsoft.ReportingServices.Library.Repo rtingService2005Impl.GetReportParameters (S tring Report, String HistoryID, Boolean ForRendering, ParameterValue [] Values, DataSourceCredentials [] Credentials, ParameterInfoCollection & Parameters)
     in Microsoft.ReportingServices.WebServer.Re portingService2005.GetReportParameters (S tring Report, String HistoryID, Boolean ForRendering, ParameterValue [] Values, DataSourceCredentials [] Credentials, ReportParameter [] & Parameters)


     Stack Trace:
     in Microsoft.ConfigurationManagement.AdminC onsole.SrsReporting.ParameterPresenter.G etParameters ()
     report, Collection `1 navigationParameters, IResultObject resultObject)
     sender, DoWorkEventArgs e)
     in System.ComponentModel.BackgroundWorker.O nDoWork (DoWorkEventArgs e)
     in System.ComponentModel.BackgroundWorker.W orkerThreadStart (Object argument)

     -------------------------------

     From which, after reading http://www.sqlteam.com/forums/topic.asp?T OPIC_ID = 98768 can be understood that the user who run reports (we have it sccm-report-user) does not have permission to perform integrated procedures in the main database SCCM (we have it CM_CM2).

     Quick fix - right at the base - properties - Permissions tab - select user sccm-report-user, in the column below Grant put the check in front of Execute - error disappears, reports work.

     A better fix - open base - Programmability - Functions - Scalar-valued - find fnGetSiteNumber, through security can see that the use must be a member as smsdbrole_DmpConnector (Device Management Point role), click Search, then Browse, scroll to the end of the list - there will be a role smsschm_users, of which the users of the report, select the part and give it the right to Execute column Grant.

    Once we did this against the object that was failing (fnGetSiteNumber, determined by the SQL log above) the reports started working.  Curious since I see this in my lab and in production if others see this as well though?  Either way this appears to have resolved our issue.  ;-)


    Friday, March 22, 2013 3:43 PM
  • I am seeing this error in this and most all of the Application Monitoring reports.
    Monday, April 8, 2013 7:44 PM
  • I had the same problem. This was on a WS 2012, with CM 2012 SP1 CU1 and SQL 2012 CU2. Problem still present when upgrading SQL to SP1.

    When I did the "A better fix" part and set the Execute permissions for smsschm_users, my Application reports started working.

    Thank you so much for this solution.


    /Tom Erik

    Saturday, April 20, 2013 4:52 PM
  • Same setup as Tom, above.  Performing the "a better fix" steps from William's post got the report running immediately for us.
    Monday, May 13, 2013 5:22 PM
  • Also wanted to say "Thank You" for the "better fix". Works great.

    Thank you :-)

    Monday, June 17, 2013 2:47 PM
  • I try But my not resolve yet.. below two errors occur when we fetch report from SCCM 2012 SP1

    Below is Error detail.

    ERROR # 1

    An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot impersonate user for data source 'AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_'. ---> Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Log on failed. Ensure the user name and password are correct. ---> System.Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors

    -------------------------------

    Microsoft.ConfigurationManagement.ManagementProvider.SmsException

    An error has occurred during report processing.

    Stack Trace:

       at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ParameterPresenter.GetParameters()

       at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ParameterPresenter.LoadParameters(IReport report, Collection`1 navigationParameters, IResultObject resultObject)

       at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ReportViewerPresenter.Worker_DoWork(Object sender, DoWorkEventArgs e)

       at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)

       at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

    -------------------------------

    System.Web.Services.Protocols.SoapException

    An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot impersonate user for data source 'AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_'. ---> Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Log on failed. Ensure the user name and password are correct. ---> System.Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors

    Stack Trace:

       at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ParameterPresenter.GetParameters()

       at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ParameterPresenter.LoadParameters(IReport report, Collection`1 navigationParameters, IResultObject resultObject)

       at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ReportViewerPresenter.Worker_DoWork(Object sender, DoWorkEventArgs e)

       at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)

       at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

    -------------------------------

    ERROR # 2

    Microsoft.Reporting.WinForms.ReportServerException

    An error has occurred during report processing. (rsProcessingAborted)

    Stack Trace:

       at Microsoft.Reporting.WinForms.ServerReportSoapProxy.OnSoapException(SoapException e)

       at Microsoft.Reporting.WinForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.ProxyMethodInvocation.Execute[TReturn](RSExecutionConnection connection, ProxyMethod`1 initialMethod, ProxyMethod`1 retryMethod)

       at Microsoft.Reporting.WinForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.LoadReport(String Report, String HistoryID)

       at Microsoft.Reporting.WinForms.ServerReport.EnsureExecutionSession()

       at Microsoft.Reporting.WinForms.ServerReport.SetParameters(IEnumerable`1 parameters)

       at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ReportViewerWindowsForms.SetParameterValues_DoWork(Object sender, DoWorkEventArgs e)

    -------------------------------

    Microsoft.Reporting.WinForms.ReportServerException

    Cannot impersonate user for data source 'AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_'. (rsErrorImpersonatingUser)

    Stack Trace:

       at Microsoft.Reporting.WinForms.ServerReportSoapProxy.OnSoapException(SoapException e)

       at Microsoft.Reporting.WinForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.ProxyMethodInvocation.Execute[TReturn](RSExecutionConnection connection, ProxyMethod`1 initialMethod, ProxyMethod`1 retryMethod)

       at Microsoft.Reporting.WinForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.LoadReport(String Report, String HistoryID)

       at Microsoft.Reporting.WinForms.ServerReport.EnsureExecutionSession()

       at Microsoft.Reporting.WinForms.ServerReport.SetParameters(IEnumerable`1 parameters)

       at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ReportViewerWindowsForms.SetParameterValues_DoWork(Object sender, DoWorkEventArgs e)

    -------------------------------

    Microsoft.Reporting.WinForms.ReportServerException

    Log on failed. Ensure the user name and password are correct. (rsLogonFailed)

    Stack Trace:

       at Microsoft.Reporting.WinForms.ServerReportSoapProxy.OnSoapException(SoapException e)

       at Microsoft.Reporting.WinForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.ProxyMethodInvocation.Execute[TReturn](RSExecutionConnection connection, ProxyMethod`1 initialMethod, ProxyMethod`1 retryMethod)

       at Microsoft.Reporting.WinForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.LoadReport(String Report, String HistoryID)

       at Microsoft.Reporting.WinForms.ServerReport.EnsureExecutionSession()

       at Microsoft.Reporting.WinForms.ServerReport.SetParameters(IEnumerable`1 parameters)

       at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ReportViewerWindowsForms.SetParameterValues_DoWork(Object sender, DoWorkEventArgs e)

    -------------------------------

    Microsoft.Reporting.WinForms.ReportServerException

    For more information about this error navigate to the report server on the local server machine, or enable remote errors

    Stack Trace:

       at Microsoft.Reporting.WinForms.ServerReportSoapProxy.OnSoapException(SoapException e)

       at Microsoft.Reporting.WinForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.ProxyMethodInvocation.Execute[TReturn](RSExecutionConnection connection, ProxyMethod`1 initialMethod, ProxyMethod`1 retryMethod)

       at Microsoft.Reporting.WinForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.LoadReport(String Report, String HistoryID)

       at Microsoft.Reporting.WinForms.ServerReport.EnsureExecutionSession()

       at Microsoft.Reporting.WinForms.ServerReport.SetParameters(IEnumerable`1 parameters)

       at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ReportViewerWindowsForms.SetParameterValues_DoWork(Object sender, DoWorkEventArgs e)

    -------------------------------

    Monday, July 8, 2013 7:04 AM
  • Hi Syed, your error is not the same. You are getting a access denied logon to the SSRS data source. Have you check your data source's user name and password?

    Cannot impersonate user for data source 'AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_'. ---> Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Log on failed. Ensure the user name and password are correct.


    http://www.enhansoft.com/

    Monday, July 8, 2013 12:22 PM
    Moderator