locked
CRM 4 deployment problem with reports - not one of the common issues addressed by existing KB's RRS feed

  • Question

  • My CRM 4 deployment is as follows:
    1.) CRM Server - Server 2008 x86
    ----Service account used for initial install was Network Service, changed after to domain\CrmService (web app account)
    ----Installation tips for installing on server 08 followed from here: http://support.microsoft.com/kb/950100
    2.) SQL Server (database) - Server 2003 Enterprise R2 x64
    ----Existing installation of SQL server, sharing default instance with a couple other DB's
    3.) Reporting Services Server - Server 2003 Standard R2 x86
    ----CRM 4 Data Connection Extension installed on this machine
    ----Reporting Services running as Network Service
    ----This instance used for several other reports (and is already properly linked to the database server above - meaning other reports from other db's work fine across this link)

    Getting the "Report Cannot Be Displayed" error in CRM.

    From SSRS directly, my datasource settings after installing the connector were:
    Connection Type: Microsoft SQL Server
    Connection String: Data Source={server};Initial Catalog={org}_MSCRM
    Connect Using: Windows Integrated

    I changed these to
    Connection Type: Microsoft CRM Data Extension
    Connection String: Data Source={server};Initial Catalog={org}_MSCRM
    Connect Using: Credentials supplied by user running report

    Still no dice.  With remote errors enabled, the message received running reports in SSRS is:
    • An error has occurred during report processing. (rsProcessingAborted)
      • Cannot create a connection to data source 'CRM'. (rsErrorOpeningConnection)
        • The type initializer for 'Microsoft.Crm.LocatorService' threw an exception.
          • The type initializer for 'Microsoft.Crm.ServerLocatorService' threw an exception.
            • Access to the registry key 'Global' is denied.


    Oddly, if I change a specific reports data source to
    Connection Type: Microsoft SQL Server
    Connection String: Data Source={server};Initial Catalog={org)_MSCRM
    Connect Using: Credentials supplied by user running report (use as windows auth selected)

    it runs, but only directly in SSRS (that same report obviously doesn't run in CRM)

    Any ideas?
    jon
    Tuesday, July 21, 2009 2:43 PM

Answers

  • Hi Jon,

    At this point, I am going to recommend that you open a ticket with Microsoft Support.  I think resolution of your particular issue is going to require some hand-on analysis and troubleshooting.  I am sure Microsoft will be able to resolve this issue quickly for you.  Please post back the results of what Microsoft finds and the solution.  Look forward to reading the results.

    As a side note and in response to:

    *It's unclear to me whether I "should" be able to run reports directly from SSRS using a Domain User account (who is a user of CRM and is in the appropriate security groups listed above).  The report initially opens with a bar at the top stating "Enter a user name and password to access the data source" - Log In Name: Password:  (I'm entering {domain}\user and the password to run.)

    I think you would have to add the Domain User to Security in your Report Server settings and give them the appropriate permission to read reports.  When I add a user in this manner, the user is not prompted for credentials when running a report directly from the Report Server. You can easily try this and see if it works.


    Best Regards, Donna
    Wednesday, July 29, 2009 6:54 PM

All replies

  • HI
    install the SSRS data connector on the CRM machine.
    Logon with the same accou nt thet you used to instralled crm initialy.
    Tiaan van Niekerk http://crmdelacreme.blogspot.com Skype:tiaan.van.niekerk1
    • Proposed as answer by Tiaan van Niekerk Tuesday, July 21, 2009 5:08 PM
    • Unproposed as answer by jon4567 Tuesday, July 21, 2009 9:37 PM
    Tuesday, July 21, 2009 5:08 PM
  • The data connector is installed on the server hosting reporting services...SSRS is not installed on the CRM machine so the connector can't be installed there (can it?).

    Quick check - doesn't pass the environment checks since SSRS isn't installed.

    Am I misreading your advice?

    It's:
    1 CRM Server
    2 SQL Server
    3 SSRS Server

    Connector is installed on #3 (SSRS Server)
    jon
    Tuesday, July 21, 2009 9:36 PM
  • Somewhere along the way, the shared datasource that installs with the CRM installation was changed.  This should have not been altered as it is configured to work with CRM and the data connector. 

    Since this datasource was changed, I don't expect that your CRM reports will work when launched from the CRM server.

    I don't think we are going to have success in trying to fix this issue by going through the settings individually and making changes.  There are too many hooks and the chance of missing something is highly likely.  Trying to work through the settings you have now will require a hands on approach similar to what Microsoft Support provides.

    You can try the publishreports.exe and see if it will restore your CRM reports and datasource to their original state.  Once your reports are in their original state, then we could try to work through the connectivity issue and see if we can help you correct it.  You should find the exe in the following folder C:\Program Files\Microsoft Dynamics CRM\Tools\  
    Best Regards, Donna
    Thursday, July 23, 2009 1:31 PM
  • Sorry for the delay in response, and thanks for taking the time.

    So, I ran publishreports.exe, and verified on the report server all reports were republished properly.

    Then, just for fun I re-installed the CRM Connector for SSRS on the SSRS box.

    My Report Server Datasource for CRM is setup as follows (after publishreports and reinstalling the data connector)
    Connection type: Microsoft SQL Server
    Connection String: Data Source={server};Initial Catalog={org}_MSCRM;Integrated Security=SSPI
    Connect Using: Windows Integrated

    I believe the settings should be:
    Connection type: Microsoft CRM Data Extension
    Connection String: (same as above)
    Connect Using: Credentials Supplied by the user running the report

    -I get the same error regardless how I configure this portion though...seems like it has to be SQL permissions somehow on this one but I'm having a heck of a time running it down....

    Any other ideas?
    jon
    Tuesday, July 28, 2009 9:27 PM
  • hi
    if you can succesfully run reports from SSRS (http://servername/reports) and not in CRM then there is a sql permission issue.
    Look at the OU groups created when CRM got installed,there should be a reportprivillage group and see if the user/server is added.
    Tiaan van Niekerk http://crmdelacreme.blogspot.com Skype:tiaan.van.niekerk1
    Wednesday, July 29, 2009 3:32 AM
  • The error above is when running reports from the SSRS server, running from CRM just gives me the generic report error.

    When I attempt to run these in SSRS, I'm prompted at the top of the report for a username and password...entering a CRM user (and SQL admin) password gives the same error almost immediately.
    jon
    Wednesday, July 29, 2009 3:51 AM
  • do you have the CRMDIAGTOOL installed to get better eroor descriptions from CRM?
    Tiaan van Niekerk http://crmdelacreme.blogspot.com Skype:tiaan.van.niekerk1
    Wednesday, July 29, 2009 3:54 AM
  • Hi Jon,

    You're right, this is most likely a security issue.  Let's check a few things:

    First, my Datasource shows the following:

    Name = MSCRM_DataSource
    Hide in List View and Enable Data Source are both checked
    Connection Type = Microsoft CRM Data Extension
    Connection String = MSCRM Data Connector Connection String
    Connect Using = Credentials Supplied by the User Running the Report

    Let's check Security Settings

    Step 1

    Go to your Report Directory that contains the list of CRM reports

    Select the Properties tab
    Select Security from the left navigation menu
    You should see something like the following:

    Group or User                                  Role

    BUILTIN\Administrators                     Browser, Browser for Microsoft CRM, Content Manager, My Reports, Publisher, Publisher for Microsoft CRM, Report Builder

    DomainName\CRMDomainAdmin*        Browser, Browser for Microsoft CRM, Content Manager, My Reports, Publisher, Publisher for Microsoft CRM, Report Builder

    DomainName\Domain Users                              Browser, Browser for Microsoft CRM, My Reports, Report Builder
    DomainName\ReportingGroup {UniqueGuid}                 Browser for Microsoft CRM
    DomainName\SQLAccessGroup {UniqueGuid}             Publisher for Microsoft CRM

    *The DomainName\CRMDomainAdmin may not be present and may not be needed. 

    Step 2

    Go to Site Settings
    Click through each of the following and ensure they have the appropriate Security Rights allowed (look through checkboxes)
    Configure site-wide security
    Configure item-level role definitions
    Configure system-level role definitions

    As you look through the privileges, I think you will be able to identify what is needed for each

    Ensure your users in Active Directory are associated with the DomainName\ReportingGroup and DomainName\SQLAccessGroup

    Step 3

    Open SQL Management Studio
    Connect to your Database Engine
    Expand Security
    Ensure you have the following three Security Logins

    DomainName\PrivReportingGroup {GUID}
    DomainName\ReportingGroup {GUID}
    DomainName\SQLAccessGroup {GUID}

    Expand your CRM database

    Expand Security

    Expand Users

     

    You should see the same three security groups listed here

     

    DomainName\PrivReportingGroup {GUID}
    DomainName\ReportingGroup {GUID}
    DomainName\SQLAccessGroup {GUID}


    Best Regards, Donna
    Wednesday, July 29, 2009 12:56 PM
  • Tiaan, I haven't bothered with MSCRM diagnostics only because the reports aren't working in SSRS yet which would tell me the problem is likely elsewhere.

    So, after doing publishreports and reinstalling the data connector on my SSRS machine, the following was found after going through the settings Donna provided:

    My CRM datasource was setup as if it were a SQL datasource, the connection string was changed to "MSCRM Data Connector Connection String"

    Everything else in step 1 and 2 above matches exactly, in step 3, all groups existed, but I did note that the {Domain}\SQLAccessGroup and {Domain}\ReportingGroup were listed as users on my CRM database, but didn't have any permissions on the DB (under the properties of the user).  I know this was there before I started troubleshooting, so I added "CRMReaderRole" to both logins on the {ORG}_MSCRM database.

    Also, guessing it wasn't needed, but on my MSCRM_CONFIG database, the {Domain}\SQLAccessGroup already had adequate permissions, the {Domain}\ReportingGroup user wasn't listed...not sure if it's needed, but I added it with CRMReaderRole.

    *It's unclear to me whether I "should" be able to run reports directly from SSRS using a Domain User account (who is a user of CRM and is in the appropriate security groups listed above).  The report initially opens with a bar at the top stating "Enter a user name and password to access the data source" - Log In Name: Password:  (I'm entering {domain}\user and the password to run.)

    Getting the same error:
    • An error has occurred during report processing. (rsProcessingAborted)
      • Cannot create a connection to data source 'CRM'. (rsErrorOpeningConnection)
        • The type initializer for 'Microsoft.Crm.LocatorService' threw an exception.
          • The type initializer for 'Microsoft.Crm.ServerLocatorService' threw an exception.
            • Access to the registry key 'Global' is denied.
    Odd that googling around this error (Access to the registry key 'Global' is denied) seems to indicate issues with remote performance management, and nothing related to CRM.  This SSRS install is used with SEVERAL other databases on SEVERAL other SQL servers, most using Kerberos double-hop authentication, some using integrated windows...

    jon
    Wednesday, July 29, 2009 6:35 PM
  • Hi Jon,

    At this point, I am going to recommend that you open a ticket with Microsoft Support.  I think resolution of your particular issue is going to require some hand-on analysis and troubleshooting.  I am sure Microsoft will be able to resolve this issue quickly for you.  Please post back the results of what Microsoft finds and the solution.  Look forward to reading the results.

    As a side note and in response to:

    *It's unclear to me whether I "should" be able to run reports directly from SSRS using a Domain User account (who is a user of CRM and is in the appropriate security groups listed above).  The report initially opens with a bar at the top stating "Enter a user name and password to access the data source" - Log In Name: Password:  (I'm entering {domain}\user and the password to run.)

    I think you would have to add the Domain User to Security in your Report Server settings and give them the appropriate permission to read reports.  When I add a user in this manner, the user is not prompted for credentials when running a report directly from the Report Server. You can easily try this and see if it works.


    Best Regards, Donna
    Wednesday, July 29, 2009 6:54 PM
  • Hey All,

    I am having a very similar issue with the CRM data connector and SSRS. Our environment is setup exactly as Jon explained his is setup and the reports do not work from crm when the connection settings are set to the mscrm data extension settings, I have to manually change the connection settings in the 4.0 folder to SQL Server and supply the connection string then enter a windows user/password for credentials stored securely in server. This only allows me to view the reports for a little while because whenever IIS gets reset the connection string changes back to the mscrm data extension and the reports are broken again. If it's not IIS getting reset there is something that is happening daily that does change the settings back. We've tried reinstalling the data connector with no luck and are running out of ideas...

    Kyle
    Wednesday, July 29, 2009 6:59 PM
  • I've stumbled upon this problem myself, now.  Except I got there by moving SSRS away from the SQL/CRM Platform server and onto a separate system.  Reports work perfectly from the other database that's tied in with an ODBC connector, and I followed the MSDN article about performing the move to the "T".  However, I did also move the name and the IP the service was using on the previous machine to the new one.  Whether or not that has anything to do with it, I'm uncertain, but I couldn't process any form of "Update" to the SSRS settings via the CRM Deployment Manager.  I've racked my brain on this one, and reach nothing but dead-ends in searches around the interwebs.  I've found a handful of CRM-related posts, but no resolutions, even though there was some vague allusions in one thread to a "Security" setting that was amiss.

    I've validated and revalidated all of the memberships, security roles, and accounts used across IIS, SQL, and Report Services; everything appears fine.  Though, I did read and apply an article that suggested manually adding the CrmReader SQL role to the SQLAccessGroup and making sure the new report server was listed as a member.  Several reboots later, and I'm still no closer to a resolution.  It seemed pointless, since the role was applied to the PrivReportingGroup and the server was already a member of that--which I figure was done by the SSRS Data Connector installation.  I've tried reinstalling .Net 3.0 on a wild hair , but I have SP2 of that installed on the server, and I cannot locate an installation from Microsoft that won't throw an error about the installed version being newer.

    My project involves transferring the CRM Server to this new box as well, though I thought I could stage the services.  I'm hopeful that this will correct the problem, but I'm concerned that either it won't, or there will be a problem in the CRM migration that may exhibit additional problems.

    If anybody who's had this problem has insight that would be helpful, I'd sure appreciate a hand in this.
    Dave Berry
    Wednesday, March 17, 2010 8:16 AM
    Moderator
  • Hi.... I've this kind of error. About your step 1, where is this folder report, in c:/rm4.0/reports or you mean in the crm website? Sorry for this question, I'm new on crm.
    Monday, July 26, 2010 1:30 PM