locked
Can't get CRM 2011 and Reporting Services to work RRS feed

  • Question

  • I have CRM 2011 running on-premises.  It is a two virtual server setup:  CRM 2011 on one server and SQL 2008 R2 database engine and Reporting Services on the second server.  I also have installed the Reporting Extensions on the SQL server.  All of the out-of-the-box reports were successfully published and I can see all of them in CRM.  CRM works great with the exception of the Reporting Services integration.

    The problem is that no matter which report I run, I always get this message in the Report Viewer:

    The error messages that show up in CRM Trace and the Windows Event logs on the CRM 2011 server are:

    Log Name:      Application
    Source:        MSCRMReporting
    Date:          3/7/2012 9:58:16 AM
    Event ID:      19968
    Task Category: None
    Level:         Error
    Keywords:      Classic
    User:          N/A
    Computer:      SPS-CRM.sps.local
    Description:
    Web service request ListExtensions to Report Server https://sps-sql08r2c.sps.local/ReportServer/ReportService2005.asmx failed. Error: The request failed with HTTP status 401: Unauthorized.
    Event Xml:
    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
      <System>
        <Provider Name="MSCRMReporting" />
        <EventID Qualifiers="49152">19968</EventID>
        <Level>2</Level>
        <Task>0</Task>
        <Keywords>0x80000000000000</Keywords>
        <TimeCreated SystemTime="2012-03-07T15:58:16.000000000Z" />
        <EventRecordID>4668930</EventRecordID>
        <Channel>Application</Channel>
        <Computer>SPS-CRM.sps.local</Computer>
        <Security />
      </System>
      <EventData>
        <Data>ListExtensions</Data>
        <Data>https://sps-sql08r2c.sps.local/ReportServer/ReportService2005.asmx</Data>
        <Data>The request failed with HTTP status 401: Unauthorized.</Data>
        <Data>w3wp</Data>
        <Data>C:\Program Files\Microsoft Dynamics CRM\CRMWeb\</Data>
      </EventData>
    </Event>

    And....

    Log Name:      Application
    Source:        ASP.NET 4.0.30319.0
    Date:          3/7/2012 9:58:16 AM
    Event ID:      1309
    Task Category: Web Event
    Level:         Warning
    Keywords:      Classic
    User:          N/A
    Computer:      SPS-CRM.sps.local
    Description:
    Event code: 3005
    Event message: An unhandled exception has occurred.
    Event time: 3/7/2012 9:58:16 AM
    Event time (UTC): 3/7/2012 3:58:16 PM
    Event ID: 7ecec6da4dba4d67990f4f5ae4b5a7b3
    Event sequence: 108571
    Event occurrence: 8
    Event detail code: 0
     
    Application information:
        Application domain: /LM/W3SVC/2/ROOT-1-129755548769757500
        Trust level: Full
        Application Virtual Path: /
        Application Path: C:\Program Files\Microsoft Dynamics CRM\CRMWeb\
        Machine name: SPS-CRM
     
    Process information:
        Process ID: 2864
        Process name: w3wp.exe
        Account name: SPS\CRM_SVC
     
    Exception information:
        Exception type: CrmException
        Exception message: Error occurred while fetching the list of data extensions installed on the report server.
       at Microsoft.Crm.Application.Platform.ServiceCommands.PlatformCommand.XrmExecuteInternal()
       at Microsoft.Crm.Web.Reporting.CrmReportServerCredentials.get_ImpersonationUser()
       at Microsoft.Reporting.WebForms.ServerReport.set_ReportServerCredentials(IReportServerCredentials value)
       at Microsoft.Reporting.WebForms.ReportViewer.ApplyConnectionToServerReport(IReportServerConnection connection, ServerReport serverReport)
       at Microsoft.Reporting.WebForms.ReportViewer.CreateServerReport()
       at Microsoft.Reporting.WebForms.ReportViewer.CreateReportHierarchy()
       at Microsoft.Reporting.WebForms.ReportViewer..ctor()
       at ASP.sps_crmreports_rsviewer_reportviewer_aspx.__BuildControlreportViewer()
       at ASP.sps_crmreports_rsviewer_reportviewer_aspx.__BuildControlform1()
       at ASP.sps_crmreports_rsviewer_reportviewer_aspx.FrameworkInitialize()
       at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
       at System.Web.UI.Page.ProcessRequest()
       at System.Web.UI.Page.ProcessRequest(HttpContext context)
       at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
       at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

     
     
    Request information:
        Request URL: https://crm.sharepointsolutions.com:444/SPS/CRMReports/rsviewer/reportviewer.aspx
        Request path: /SPS/CRMReports/rsviewer/reportviewer.aspx
        User host address: 192.168.10.2
        User: SPS\jeff
        Is authenticated: True
        Authentication Type: Federation
        Thread account name: SPS\CRM_SVC
     
    Thread information:
        Thread ID: 85
        Thread account name: SPS\CRM_SVC
        Is impersonating: True
        Stack trace:    at Microsoft.Crm.Application.Platform.ServiceCommands.PlatformCommand.XrmExecuteInternal()
       at Microsoft.Crm.Web.Reporting.CrmReportServerCredentials.get_ImpersonationUser()
       at Microsoft.Reporting.WebForms.ServerReport.set_ReportServerCredentials(IReportServerCredentials value)
       at Microsoft.Reporting.WebForms.ReportViewer.ApplyConnectionToServerReport(IReportServerConnection connection, ServerReport serverReport)
       at Microsoft.Reporting.WebForms.ReportViewer.CreateServerReport()
       at Microsoft.Reporting.WebForms.ReportViewer.CreateReportHierarchy()
       at Microsoft.Reporting.WebForms.ReportViewer..ctor()
       at ASP.sps_crmreports_rsviewer_reportviewer_aspx.__BuildControlreportViewer()
       at ASP.sps_crmreports_rsviewer_reportviewer_aspx.__BuildControlform1()
       at ASP.sps_crmreports_rsviewer_reportviewer_aspx.FrameworkInitialize()
       at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
       at System.Web.UI.Page.ProcessRequest()
       at System.Web.UI.Page.ProcessRequest(HttpContext context)
       at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
       at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
     
     
    Custom event details:

    Event Xml:
    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
      <System>
        <Provider Name="ASP.NET 4.0.30319.0" />
        <EventID Qualifiers="32768">1309</EventID>
        <Level>3</Level>
        <Task>3</Task>
        <Keywords>0x80000000000000</Keywords>
        <TimeCreated SystemTime="2012-03-07T15:58:16.000000000Z" />
        <EventRecordID>4668931</EventRecordID>
        <Channel>Application</Channel>
        <Computer>SPS-CRM.sps.local</Computer>
        <Security />
      </System>
      <EventData>
        <Data>3005</Data>
        <Data>An unhandled exception has occurred.</Data>
        <Data>3/7/2012 9:58:16 AM</Data>
        <Data>3/7/2012 3:58:16 PM</Data>
        <Data>7ecec6da4dba4d67990f4f5ae4b5a7b3</Data>
        <Data>108571</Data>
        <Data>8</Data>
        <Data>0</Data>
        <Data>/LM/W3SVC/2/ROOT-1-129755548769757500</Data>
        <Data>Full</Data>
        <Data>/</Data>
        <Data>C:\Program Files\Microsoft Dynamics CRM\CRMWeb\</Data>
        <Data>SPS-CRM</Data>
        <Data>
        </Data>
        <Data>2864</Data>
        <Data>w3wp.exe</Data>
        <Data>SPS\CRM_SVC</Data>
        <Data>CrmException</Data>
        <Data>Error occurred while fetching the list of data extensions installed on the report server.
       at Microsoft.Crm.Application.Platform.ServiceCommands.PlatformCommand.XrmExecuteInternal()
       at Microsoft.Crm.Web.Reporting.CrmReportServerCredentials.get_ImpersonationUser()
       at Microsoft.Reporting.WebForms.ServerReport.set_ReportServerCredentials(IReportServerCredentials value)
       at Microsoft.Reporting.WebForms.ReportViewer.ApplyConnectionToServerReport(IReportServerConnection connection, ServerReport serverReport)
       at Microsoft.Reporting.WebForms.ReportViewer.CreateServerReport()
       at Microsoft.Reporting.WebForms.ReportViewer.CreateReportHierarchy()
       at Microsoft.Reporting.WebForms.ReportViewer..ctor()
       at ASP.sps_crmreports_rsviewer_reportviewer_aspx.__BuildControlreportViewer()
       at ASP.sps_crmreports_rsviewer_reportviewer_aspx.__BuildControlform1()
       at ASP.sps_crmreports_rsviewer_reportviewer_aspx.FrameworkInitialize()
       at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
       at System.Web.UI.Page.ProcessRequest()
       at System.Web.UI.Page.ProcessRequest(HttpContext context)
       at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
       at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&amp; completedSynchronously)

    </Data>
        <Data>https://crm.sharepointsolutions.com:444/SPS/CRMReports/rsviewer/reportviewer.aspx</Data>
        <Data>/SPS/CRMReports/rsviewer/reportviewer.aspx</Data>
        <Data>192.168.10.2</Data>
        <Data>SPS\jeff</Data>
        <Data>True</Data>
        <Data>Federation</Data>
        <Data>SPS\CRM_SVC</Data>
        <Data>85</Data>
        <Data>SPS\CRM_SVC</Data>
        <Data>True</Data>
        <Data>   at Microsoft.Crm.Application.Platform.ServiceCommands.PlatformCommand.XrmExecuteInternal()
       at Microsoft.Crm.Web.Reporting.CrmReportServerCredentials.get_ImpersonationUser()
       at Microsoft.Reporting.WebForms.ServerReport.set_ReportServerCredentials(IReportServerCredentials value)
       at Microsoft.Reporting.WebForms.ReportViewer.ApplyConnectionToServerReport(IReportServerConnection connection, ServerReport serverReport)
       at Microsoft.Reporting.WebForms.ReportViewer.CreateServerReport()
       at Microsoft.Reporting.WebForms.ReportViewer.CreateReportHierarchy()
       at Microsoft.Reporting.WebForms.ReportViewer..ctor()
       at ASP.sps_crmreports_rsviewer_reportviewer_aspx.__BuildControlreportViewer()
       at ASP.sps_crmreports_rsviewer_reportviewer_aspx.__BuildControlform1()
       at ASP.sps_crmreports_rsviewer_reportviewer_aspx.FrameworkInitialize()
       at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
       at System.Web.UI.Page.ProcessRequest()
       at System.Web.UI.Page.ProcessRequest(HttpContext context)
       at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
       at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&amp; completedSynchronously)
    </Data>
      </EventData>
    </Event>

    Here is what the CRM Trace log shows:

    >Crm Exception: Message: Error occurred while fetching the list of data extensions installed on the report server., ErrorCode: -2147187941
    [2012-03-07 09:58:16.117] Process: w3wp |Organization:ee539ce9-82b7-45ba-a470-fc81c3bbf9bd |Thread:   85 |Category: Application |User: 00000000-0000-0000-0000-000000000000 |Level: Error | ErrorInformation.LogError
    >MSCRM Error Report:
    --------------------------------------------------------------------------------------------------------
    Error: Error occurred while fetching the list of data extensions installed on the report server.

    Error Number: 0x8004831B

    Error Message: Error occurred while fetching the list of data extensions installed on the report server.

    Error Details: Error occurred while fetching the list of data extensions installed on the report server.

    Source File: Not available

    Line Number: Not available

    Request URL: https://crm.sharepointsolutions.com:444/SPS/CRMReports/rsviewer/reportviewer.aspx

    Stack Trace Info: [CrmException: Error occurred while fetching the list of data extensions installed on the report server.]
       at Microsoft.Crm.Application.Platform.ServiceCommands.PlatformCommand.XrmExecuteInternal()
       at Microsoft.Crm.Web.Reporting.CrmReportServerCredentials.get_ImpersonationUser()
       at Microsoft.Reporting.WebForms.ServerReport.set_ReportServerCredentials(IReportServerCredentials value)
       at Microsoft.Reporting.WebForms.ReportViewer.ApplyConnectionToServerReport(IReportServerConnection connection, ServerReport serverReport)
       at Microsoft.Reporting.WebForms.ReportViewer.CreateServerReport()
       at Microsoft.Reporting.WebForms.ReportViewer.CreateReportHierarchy()
       at Microsoft.Reporting.WebForms.ReportViewer..ctor()
       at ASP.sps_crmreports_rsviewer_reportviewer_aspx.__BuildControlreportViewer()
       at ASP.sps_crmreports_rsviewer_reportviewer_aspx.__BuildControlform1()
       at ASP.sps_crmreports_rsviewer_reportviewer_aspx.FrameworkInitialize()
       at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
       at System.Web.UI.Page.ProcessRequest()
       at System.Web.UI.Page.ProcessRequest(HttpContext context)
       at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
       at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

    I have Googled and Binged until my brain hurts and I can't find any solution that seems to make any difference.

    My gut feel is that it might be a Kerberos error, but I though the whole point of the CRM 2011 Reporting Extensions was so that it would not be necessary to worry about Kerberos settings?


    Jeff Cate, Founder and President, SharePoint Solutions

    Wednesday, March 7, 2012 4:24 PM

Answers

  • I figured out the problem, which I might have avoided in the first place if the Microsoft Dynamics CRM 2011 Planning Guide and the Microsoft Dynamics CRM 2011 Installing Guide were better resources.

    On page 26, the Microsoft Dynamics CRM 2011 Plannning Guide makes this statement about the Microsoft Dynamics CRM Reporting Extensions:

    "For SQL-based reports the SQL data processing extension eliminates the need to enable delegation for the Kerberos double-hop authentication that is required when SQL Server Reporting Services are installed on a separate computer."

    I think they really need to insert the word "may" into this statement - "data processing extensions may eliminate the need to enable delegation for the Kerberos......".  From searching the Internet, I can find stacks of discussions and blog posts where people have indicated that they had to set up Kerberos delegation in order to get CRM 2011 and Reporting Services to play nice, so it clearly isn't the case that it is always true that all that is needed is the Reporting Extensions.

    In my case, I have CRM 2011 running on one server and its IIS application pool runs under a standard domain user account (domain\crm_svc).   I have SQL and Reporting Services running on another server and they both run under a domain user account that we use as a SQL service account (domain\sql_svc).

    Microsoft Dynamics CRM Reporting Extensions are installed on the SQL Server and the out-of-the-box reports have successfully been published.  But, neither the out-of-the-box CRM reports or custom Report Wizard reports will run.

    Here is what I had to do to get it to work:

    To get the out-of-the-box CRM reports to work:

    Add these Service Principal Name using SetSPN (on a DC):

    HTTP/{CRM server NETBIOS name}  domain\crm_svc

    HTTP/{CRM server FQDN} domain\crm_svc

    The above SPNs allowed the out-of-the-box CRM reports to run properly, but did not take care of the problem with the custom reports.

    To get Custom Report Wizard reports to run, I had to add this SPN as well:

    HTTP/{FQDN of SQL Server running Reporting Services} domain\sql_svc

    By making these three SPN additions, I got all reporting features of CRM 2011 working properly.

    My big "beef" in all of this is that the documentation Microsoft provides for planning and deploying on-premise CRM 2011 makes it sound like the Reporting Extensions are all you need.  I don't believe that is the case for many customers.  Maybe my findings here will help someone else avoid sinking days into troubleshooting this issue.  Maybe Microsoft will update their documentation to make it more accurate.


    Jeff Cate, Founder and President, SharePoint Solutions

    • Marked as answer by Jeff_Cate Wednesday, March 7, 2012 7:57 PM
    Wednesday, March 7, 2012 7:56 PM

All replies

  • I figured out the problem, which I might have avoided in the first place if the Microsoft Dynamics CRM 2011 Planning Guide and the Microsoft Dynamics CRM 2011 Installing Guide were better resources.

    On page 26, the Microsoft Dynamics CRM 2011 Plannning Guide makes this statement about the Microsoft Dynamics CRM Reporting Extensions:

    "For SQL-based reports the SQL data processing extension eliminates the need to enable delegation for the Kerberos double-hop authentication that is required when SQL Server Reporting Services are installed on a separate computer."

    I think they really need to insert the word "may" into this statement - "data processing extensions may eliminate the need to enable delegation for the Kerberos......".  From searching the Internet, I can find stacks of discussions and blog posts where people have indicated that they had to set up Kerberos delegation in order to get CRM 2011 and Reporting Services to play nice, so it clearly isn't the case that it is always true that all that is needed is the Reporting Extensions.

    In my case, I have CRM 2011 running on one server and its IIS application pool runs under a standard domain user account (domain\crm_svc).   I have SQL and Reporting Services running on another server and they both run under a domain user account that we use as a SQL service account (domain\sql_svc).

    Microsoft Dynamics CRM Reporting Extensions are installed on the SQL Server and the out-of-the-box reports have successfully been published.  But, neither the out-of-the-box CRM reports or custom Report Wizard reports will run.

    Here is what I had to do to get it to work:

    To get the out-of-the-box CRM reports to work:

    Add these Service Principal Name using SetSPN (on a DC):

    HTTP/{CRM server NETBIOS name}  domain\crm_svc

    HTTP/{CRM server FQDN} domain\crm_svc

    The above SPNs allowed the out-of-the-box CRM reports to run properly, but did not take care of the problem with the custom reports.

    To get Custom Report Wizard reports to run, I had to add this SPN as well:

    HTTP/{FQDN of SQL Server running Reporting Services} domain\sql_svc

    By making these three SPN additions, I got all reporting features of CRM 2011 working properly.

    My big "beef" in all of this is that the documentation Microsoft provides for planning and deploying on-premise CRM 2011 makes it sound like the Reporting Extensions are all you need.  I don't believe that is the case for many customers.  Maybe my findings here will help someone else avoid sinking days into troubleshooting this issue.  Maybe Microsoft will update their documentation to make it more accurate.


    Jeff Cate, Founder and President, SharePoint Solutions

    • Marked as answer by Jeff_Cate Wednesday, March 7, 2012 7:57 PM
    Wednesday, March 7, 2012 7:56 PM
  • I am receiving the same error messages in production.

    But my concern is that I am running under network service(CRM app pool).

    If i change to some sa admin account , everythng works fine.(I dont have to look at SPN even)

    We have three different servers(CRM,Report,SQL) altogether.

    I am guessing this way, CRM will connect to Report using 'NTLM'. Report is connecting to SQL as annonymous and reverting back Unauthorised error.

    Please help...

    Thursday, September 13, 2012 11:01 AM