locked
SSRS reports within CRM throws no data available RRS feed

  • Question

  • Hello Community,

    Not sure if any one has seen this before, but this one has put me in a twisted state of mind

    I have 2 organizations "org1" and "org2" on one CRM 2011 Instance and I have one SSRS installed and configured.

    org1:

    -SSRS Reports works fine when running in BIDS

    -SSRS Reports works fine when deployed to CRM and running from within CRM.

    org2:

    Same reports after changing the datasource to point to org2 database

    -SSRS Reports work fine when running in BIDS

    -SSRS Report does not work when deployed to CRM and running from within CRM. It comes as no data available.

    Any one got any clue?

    -I have tried reinstalling SSRS Reporting extensions.

    -I have republished reports to both orgs using PublishReports.exe tool.

    -I have even tried creating a brand new report from scratch and after deploying to CRM org2 doesn't come back with any data but it does display data when running from BIDS.

    Cheers,

    Kunal

    Tuesday, October 16, 2012 5:39 AM

Answers

  • When I revisited Report Manager I saw that lot of the reports are deployed and published by on machine name as the user like this: DOMAIN\MachineName$.

    I then went and added this guy (DOMAIN\MachineName$) as a user in CRM and volla! 

    Now I am not quite sure how is the machine name coming as a contextual user for reports. I don't have claims configured in the environment. I suspect it's the backup and restore of 
    database and also I think that the DB backup was post upgrade to Roll Up 10. Does it makes sense?

    Cheers,

    Kunal

    • Marked as answer by Kunal Kochhar Wednesday, October 17, 2012 1:07 AM
    Wednesday, October 17, 2012 1:07 AM

All replies

  • Hi Kunal,

    Do you try to do it reverse

    Same reports built with ORG2 and then changing the datasource to point to org1, this can narrow down the scope.

    If it fails to work either, it may mean that there something else you need to change when transfer the report to another org.

    If it works well, then go to server to gather more log with CRMDiagTool2011 to see more detail.

    Wilson

    Tuesday, October 16, 2012 7:34 AM
  • Thanks for the reply Wilson!

    It's a good point, I did tried redeploying the report back on Org1 after rechanging the datasource and it does work fine.

    PS: CRMDiagTool grrr...I did had it configured bugger looks like I removed the configuration in past as it was consuming lot of space. Will give that a look again. 

    • Proposed as answer by Phil98765 Wednesday, August 7, 2013 3:44 AM
    Wednesday, October 17, 2012 12:08 AM
  • When I revisited Report Manager I saw that lot of the reports are deployed and published by on machine name as the user like this: DOMAIN\MachineName$.

    I then went and added this guy (DOMAIN\MachineName$) as a user in CRM and volla! 

    Now I am not quite sure how is the machine name coming as a contextual user for reports. I don't have claims configured in the environment. I suspect it's the backup and restore of 
    database and also I think that the DB backup was post upgrade to Roll Up 10. Does it makes sense?

    Cheers,

    Kunal

    • Marked as answer by Kunal Kochhar Wednesday, October 17, 2012 1:07 AM
    Wednesday, October 17, 2012 1:07 AM
  • Glad you got his fixed

    DOMAIN\MachineName$ represents the built-in accounts on a machine - for example the NetworkService account. I expect CRM uses this account to publish the reports, hence it is the publisher.

    However, the normal setup for an SSRS Data Source is:

    • If not using the CRM RS Connector, it should be set to impersonate the user running the report, rather than the user who published the report
    • If using the CRM RS Connector, it should be set to prompt for a username and password, and CRM will pass this data (the password is not a real password, rather CRM passes the organizationid)

    It sounds to me like someone or something changed the data source properties


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Wednesday, October 17, 2012 7:55 AM
    Moderator
  • Hello Kunal/David,

    I was facing the same problem since last couple of days. Finally, In today's search I have reached to this post.

    After creating such user (DOMAIN\MachineName$) in CRM and assigning that user a role of system admin, now I can run the reports with data. Everything is running well.

    However, I would like to know how it happened? Could you please shed some light why this problem came in picture and why we needed to add such unknown users (DOMAIN\MachineName$) in CRM to get rid of it? What are the steps went wrong during ORG creation that provoked such problem?

    Please explain.


    Thanks, Ankit Shah


    Inkey Solutions, India.
    Microsoft Certified Business Management Solutions Professionals
    http://www.inkeysolutions.com/MicrosoftDynamicsCRM.html


    Monday, January 7, 2013 3:54 PM
  • Hi Ankit,

    It seems User does not have access to CRM Filtered views. User should be part of "SQLAccessGroup" to access the CRM Filtered views.

    "SQLAccessGroup" is one of 4 groups gets created with CRM installation.

    By adding the User to CRM and providing a role, he will be part of "SQLAccessGroup" access group and hence he gets access to filtered views and the report started to show the data.

    Thanks,

    Rajeev

    Sunday, January 20, 2013 11:08 AM
  • Rajeev makes an excellent point - your Active Directory groups control access to things, so adding the machine account to the SQLAccessGroup there is definitely the right way to go. You should then find you can disable this unneeded user in CRM and it should continue to work fine.

    Adding "service account" users, or even machine accounts as CRM users is very dangerous - if you add a user which is the account used to run the CRM Application Pool, CRM will break immediately, as you in effect downgrade the privileges that accounts needs to run.


    Hope this helps.
    Adam Vero, Microsoft Certified Trainer | Microsoft Community Contributor 2011
    Blog: Getting IT Right

    Sunday, January 20, 2013 7:57 PM