locked
Access external datasource from CRM report RRS feed

  • Question

  • Hi,

    I am making a complicated SSRS report having many datasets, tables and charts.  Most of the datasets use MS CRM as the datasource.  Others use external SQL Server database as datasource.  When I ran the report in SSRS, it does not have problem.  However, when I deploy the report to CRM 2011, it does not work.  No error. It just loads then stop loading in one second.  Since I have so many datasets, many tables, and charts in the report, I pinpointed and found that as long as I remove the datasets connecting external database, the report will work in CRM 2011.  So, I concluded that CRM report must need a special do in order to use the external datasource.  Does anyone have an experience and could advise?  Thanks.

    Friday, May 2, 2014 8:10 PM

Answers

All replies

  • The accounts that you were using to run the report in SSRS and CRM are different:

    How to Create Dynamics CRM Reports Accessing Outside Data


    Ronald

    • Marked as answer by chicago bq Sunday, May 4, 2014 1:43 AM
    Friday, May 2, 2014 9:52 PM
  • Hi, Ron-

    Thanks for your help.  The accounts(windows, or sa) I ran the report in BIDs are already mapped to the external database and have all permissions.  Do I have to create another account like '"SERVER\sqldevmscrmsvc" in the article to run the report?  May you be  more specific which account CRM 2011 uses to run report in its boundary?  Any AD groups created by CRM installation will work?

    Saturday, May 3, 2014 1:02 AM
  • The account you used in BID is not what CRM 2011 uses to access the database. You need to find out what that account is by either following the instruction from that link, or go through SSRS logs to locate the corresponding error message. An example of the SSRS logs location is [your drive]:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles.

    Ronald

    Saturday, May 3, 2014 1:49 AM
  • Hi Chicago,

    For error check the reporting service error log.

    You can refer below blog.

    http://www.crmsoftwareblog.com/2011/12/how-to-create-dynamics-crm-reports-accessing-outside-data/

    Thanks!
    Saturday, May 3, 2014 5:52 AM
  • Hi, Ron and someone from Kalimk-

    Thanks for your help.  It took me hours to figure it out and I want to share with it people who has the similar issue.  Originally, I thought it is Report Services account, which CRM 2011 uses to run report.  It is not.  I went to SQL Server Management Studio -> Management -> SQL Server Logs -> View SQL Server Logs, and found out it is 'NT AUTHORITY\NETWORK SERVICE' account, which does not have access to the external database. So, I went to SQL Server, Security -> Logins, and map 'NT AUTHORITY/NETWORK SERVICE' to the database I need.  The SSRS report then runs in CRM 2011 beautifully.  The problem is solved.  Thanks everyone!

    Sunday, May 4, 2014 1:42 AM