locked
how to debug broken Reporting Services Connector RRS feed

  • Question

  • hi all

    ------------------------------------------------------------------------------------------
    my 2 questions are:
     - what does the Reporting Services Connector do between machines? (settings, files, registry entries)
     - how do I debug/ review the current configuration?
    ------------------------------------------------------------------------------------------
    we're just feeling our way through our install of Dynamics CRM on one server and SQLServer reporting services (and the CRM database) on the other.

    the Reporting Services Connector between the two is well and truly broken. HOWEVER, the Reporting Services page (SQLServer machine) seems to work fine (although I haven't got the ReportBuilder.application to download and install yet)


    within DynamicsCRM the list of reports are on display but we get two errors, depending what you click on to access them: if on the list of reports:

    <error>
    "Reporting Error: Reports cannot be run because the Connector for Microsoft SQL Server Reporting Services, a required component for reporting, is not installed on the server that is running Microsoft SQL Server Reporting Services."
    </error>

    if trying to run a specific report (from http://crmserver/.../CRMReports/rsviewer/reportviewer.aspx)

    <error>
    Microsoft CRM Error Report:
    Error Description:
    The request failed with HTTP status 401: Unauthorized.

    Error Details:
    The request failed with HTTP status 401: Unauthorized.

    Full Stack:
    [WebException: The request failed with HTTP status 401: Unauthorized.]
       at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.GetSecureMethods()
    </error>
    Tuesday, July 1, 2008 4:57 AM

Answers

All replies

  • follow up:

    the DynamicsCRM deploytment manager has it's ReportServerCRM URL

    http://dbserver/ReportServerCRM

    set to the same as the db server's "<defaultWebSite>/ReportsCRM" RSWebApplication.config setting

            <ReportServerUrl>http://dbserver/reportservercrm</ReportServerUrl>
            <ReportServerVirtualDirectory></ReportServerVirtualDirectory>
            <ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>


    Tuesday, July 1, 2008 8:09 AM
  • Thankyou Imran, this tool is at least collecting for me all the configuration data in one place to compare (inc registry entries it seems)

    I now may have spotted something amis ...

    on the DynamicsCRM server:
    [SqlServerName....................]: SBCDEVDB1\CRMDATA
    [SrsUrl...........................]: http://sbcdevdb1.bnit.development/ReportServerCRM

    HKEY_LOCAL_MACHINE\Software\Microsoft\MSCRM[SQLRSServerURL]: http://sbcdevdb1/ReportServerCRM

    that registry entry: shouldn't that be the full domain name of the reporting server: ie the same as the (correct) value of
    SrsUrl?






    Wednesday, July 2, 2008 2:22 AM
  • nah, forget that. changing the registry enttry for the URL didn't work...


    I can't help thinking that the CRMDATA instance on the reporting server is not right ... (but what?)

    Reporting Server (on the SQLServer machine) has three instances:
     - "MSSQLSERVER"
     - "TFSDATA" (for the team foundation server we're also starting to set up)
     - "CRMDATA" (What I'm trying to get DynamicsCRM to talk to)

    the thick plottens...

    ================================================

    DynamicsCRM server (generated setting report excerpt)
    -------------------------------------------------------
    HKEY_LOCAL_MACHINE\Software\Microsoft\MSCRM[SQLRSServerURL]: http://sbcdevdb1/ReportServerCRM
    [SqlServerName....................]: SBCDEVDB1\CRMDATA
    [SrsUrl...........................]: http://sbcdevdb1.bnit.development/ReportServerCRM


    SQLServer: (generated setting report excerpt)
    -------------------------------------------------------
    ----------------ReportServer Configuration----------------
    [ApplicationPoolActual............]: DefaultAppPool
    [ApplicationPoolConfigured........]: DefaultAppPool
    [ConnectionPoolSize...............]: 100
    [DatabaseLogonAccount.............]:
    [DatabaseLogonTimeout.............]: 15
    [DatabaseLogonType................]: 2
    [DatabaseName.....................]: ReportServer
    [DatabaseQueryTimeout.............]: 120
    [DatabaseServerName...............]: SBCDEVDB1
    [InstallationID...................]: {b75fa509-d9f5-4421-80bc-51b68429b720}
    [InstanceName.....................]: MSSQLSERVER
    [IsInitialized....................]: True
    [IsSharePointExclusionConfigured..]: False
    [IsSharePointInstalled............]: False
    [IsSharePointIntegrated...........]: False
    [IsWebServiceEnabled..............]: True
    [IsWindowsServiceEnabled..........]: True
    [PathName.........................]: C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\RSReportServer.config
    [SecureConnectionLevel............]: 0
    [SenderEmailAddress...............]:
    [SendUsingSMTPServer..............]: True
    [ServerPort.......................]: -1
    [ServiceName......................]: ReportServer
    [SMTPServer.......................]:
    [UnattendedExecutionAccount.......]:
    [VirtualDirectory.................]: ReportServer
    [VirtualDirectoryHasSSLCertificate]: False
    [WebServiceIdentityActual.........]: NT Authority\NetworkService
    [WebServiceIdentityConfigured.....]: NT Authority\NetworkService
    [WebSite..........................]: 1
    [WindowsServiceIdentityActual.....]: NT AUTHORITY\NetworkService
    [WindowsServiceIdentityConfigured.]: NT AUTHORITY\NetworkService
    [Error............................]:    at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
       at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
       at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
       at CrmDiagTool4.WMIHelper.GetWMIPropCollection(String strWMINameSpace, String strWMIClass)


    Wednesday, July 2, 2008 4:06 AM
  • I'm getting this exact same error for users accessing the reports remotely:


    "Reports cannot be run because the Connector for Microsoft SQL Server Reporting Services, a required component for reporting, is not installed on the server that is running Microsoft SQL Server Reporting Services"

     

    A corresponding error appears in the server log:

     

    Web service request SetParameters to Report Server http://SQLserver/reportserver failed. Error:

    The request failed with HTTP status 401: Unauthorized.

     

    However...

    All the reports are accessable by users via direct links to the reporting services server.

    All the reports are accessable via the crm interface when remote desktop logged in to the server.

     

     

    So... as a first step, what I wanted to do was to reinstall the just Reporting Services Connector. How ??

     

    This post offers what appears to be the download links I'm looking for: http://support.microsoft.com/kb/948917/EN-US/

    linked from here: http://support.microsoft.com/kb/949256/en-us

     

     

     

     

    Thursday, July 24, 2008 2:31 PM
  • I'd be a touch careful there: the guys in the team earlier this year did exactly that and made a bigger mess of it.

    I'd like to hear from someone who *has* sucessfully re-applied the connector to fix this and them saying "no worrles"

    the "unautherised" bit: gut feeling says that the impersonation (the double hop business) from Dynamics server to SQLServer isn't doing it's job ... or something.

    that's why I was wanting to try and debug the current (broken) install first. if you think about it, the connector works just like an installer - gathers existing environmental settings, changes others, drops some files in spots, perhaps even changes registry entries. In theory it should be possible to manually do exactly the same thing, and if so, it should also be possible to debug the current setup.

    no wonder the *nix guys go for text config files: it's a pain, sure, but at least it's all out in the open...


    Sunday, July 27, 2008 6:29 AM
  • Hmmm.. just reccieved this feedback from the consultants who installed our CRM:

     

    If Reporting Services is running on another server than the CRM server, the following things should be checked:

    1.       Make sure that trust delegation over HTTP is set  between CRM server and Reporting Services server in Active Directory

    2.       Make sure that SSRS Data Connector is installed on server running Reporting Services (installation can be found in default installation folder)

    Sunday, July 27, 2008 2:34 PM
  • did they give you any more info? ... like how?

    very interested to see how you get on...
    Tuesday, July 29, 2008 9:16 AM
  • We still have a lot of ongoing issues, so still troubleshooting. This is our current line of investigation:

    Setting Kerberos Authentication between the CRM server & the Reporting Services Server. (this snippet taken from a whitepaper here: http://whitepapers.zdnet.co.uk/0,1000000651,260272786p-39000361q,00.htm)

     

    Set up the security account on the Microsoft Dynamics CRM server to be trusted for delegation.

    1.     On your domain controller, open Active Directory Users and Computers.

    If constrained delegation is available, use a server running Windows Server 2003 to open Active Directory Users and Computers. Otherwise, you will not see the options for setting constrained delegation.

    2.     In Computers, right-click the server name for the Microsoft Dynamics CRM server, and then click Properties.

    If constrained delegation is available:

    a.     Click the Delegation tab.

    b.    Select Trust this computer for delegation to specified services only.

    c.     Select Use Kerberos only.

    d.    Click Add.

    e.     Click Users or Computers, click Advanced, click Find Now, select the Reporting Services server, and then click OK two times.

    f.     Select the http service, and then click OK.

     

    If constrained delegation is not available, and the domain controller is running IIS 5.0:

    a.     On the General tab, select the Trust computer for delegation check box.

    b.    Click OK.

    3.   Stop and restart the server (CRM)

    Thursday, August 7, 2008 8:16 AM


  • Andy, thankyou for "sharing the lurve" ... it's starting to make a bit of sense now.



    "If constrained delegation is not available, and the domain controller is running IIS 5.0:"

    the checkbox was unchecked on the CRM server ... so I checked it.

    "
    and the domain controller is running IIS 5.0:"

    now this is got me confused. the DC isn't running IIS. Should it be? and what if it isn't?




    Friday, August 8, 2008 7:53 AM
  • Sorry, no idea. In our setup, constrained delegation was available, so that bottom section of step 2 didn't apply.

    I would assume that normally a domain controller doesn't run IIS, I know ours don't.

    Monday, August 11, 2008 9:04 AM
  • Kind of had the same problem and solved it by installing Microsoft Dynamics CRM 4.0 Connector for Microsoft SQL Server Reporting Services.
    See http://social.microsoft.com/Forums/en-US/partnerdynamicscrm/thread/7af96b37-c65c-44dc-bd75-0a803b608758?prof=required

    Friday, September 4, 2009 11:09 AM
  • Hi,

    I can not install the Connector for Microsoft SQL Reporting Services. I don't have an internet connection on the SQL server, therefore I can not update my installation files.

    15:04:13|  Error| Check ReportServerValidator : Failure: Unable to validate SQL Server Reporting Services Report Server installation. Please check that it is correctly installed on the local machine.
    15:04:13|   Info| Check ConfigDatabaseExistsValidator: Success
    15:04:16|Verbose| Retrieving config database version Provider=SQLOLEDB;Data Source=CRM02CJ001;Initial Catalog=MSCRM_CONFIG;Integrated Security=SSPI ...
    15:04:16|   Info| Config database version: 4.0.7333.3.
    15:04:16|   Info| Check ConfigDatabaseVersionValidator: Success
    15:04:17|Verbose| Exception caught while checking access to group <GUID=32a199dc-012f-4759-b73e-156edef3508e> ... System.DirectoryServices.DirectoryServicesCOMException (0x80072030): There is no such object on the server.



    15:04:17|Verbose| Exception caught while checking access to group <GUID=32a199dc-012f-4759-b73e-156edef3508e> ... System.DirectoryServices.DirectoryServicesCOMException (0x80072030): There is no such object on the server.

       at System.DirectoryServices.DirectoryEntry.Bind(Boolean throwIfFail)
       at System.DirectoryServices.DirectoryEntry.Bind()
       at System.DirectoryServices.DirectoryEntry.RefreshCache(String[] propertyNames)
       at Microsoft.Crm.Setup.Server.Utility.ADUtility.GroupAccessCheck(String group)
    15:04:17|  Error| Check PrivReportingGroupActiveDirectoryRightsValidator : Failure: The current user does not have required permissions (read/write) for the following Active Directory group: PrivReportingGroup
    15:04:17|Verbose| BaseGroup.Validate completed

     

    However, the GUID found in the log can not be found on my system.

    Any ideas?

    Thanks,

    Thursday, May 27, 2010 2:22 PM
  • has anyone been able to resolve this issue? all the above done absolutely nothing for me.
    Tuesday, October 12, 2010 8:54 AM