Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Can't run any SQL-based report from within CRM 2011 (sql login with the wrong credentials?) RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I created a report whose data source points to a database (let's call it "ReportHelperDb") containing some views that reference the CRM org db ("Org_MSCRM").

    I've specified the credentials for my data source in SSRS (user "sa" and password).

    Result:

    1) It runs OK in the visual studio designer

    2) From Reporting Services direct url, it asks for the same credentials I told him to store (why!?)

    3) From the CRM, it fails with rsProcessingAborted. In the event viewer I find an information message from SQL server saying access was denied for user "f17e0343-450d-e211-9719-005056aa0001" (a guid).

    Please help me understand what's going on. I thought storing plain-text credentials was a matter that we left behind us 10-15 years ago. Isn't it?

    I suppose that the CRM must be modifying the RDL file when I upload a report. But does that mean that it is IMPOSSIBLE to connect to any other SQL data source besides the CRM org db?

    Do I have to get as far as to try using the OLE DB driver?

    thank you

    Friday, April 4, 2014 4:44 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    CRM changes the data source to point to the CRM DB of the environment you uploaded it to. This helps when we deploy reports across environments as the data source need not be modified every time. The user running the report & NT AUTHORITY\NETWORK SERVICE must have read access to the  ReportHelperDb.

    If you want to connect to the ReporthelperDB with sa user then, after uploading the report from CRM, set the external db data source in the report manager.

    If my response helps you in finding your answer then please click 'Mark as Answer' and 'Vote as Helpful'

    • Marked as answer by thenext1 Monday, April 7, 2014 8:23 AM
    Friday, April 4, 2014 6:29 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Yes - this will work fine.

    Scott

    Scott Durow
    Blog www.develop1.net    Follow Me
    Rockstar365
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    • Marked as answer by thenext1 Monday, April 7, 2014 8:22 AM
    Sunday, April 6, 2014 12:12 AM
    Answerer

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    CRM changes the data source to point to the CRM DB of the environment you uploaded it to. This helps when we deploy reports across environments as the data source need not be modified every time. The user running the report & NT AUTHORITY\NETWORK SERVICE must have read access to the  ReportHelperDb.

    If you want to connect to the ReporthelperDB with sa user then, after uploading the report from CRM, set the external db data source in the report manager.

    If my response helps you in finding your answer then please click 'Mark as Answer' and 'Vote as Helpful'

    • Marked as answer by thenext1 Monday, April 7, 2014 8:23 AM
    Friday, April 4, 2014 6:29 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you, I've verified your second solution and it works.

    But I have another question. To avoid having to modify the datasource everytime I upload the report, can't I just let it connect to the Org DB, and reference the views in my db with the full path?

    i.e. instead of "SELECT * FROM MyCustomerView", I just do "SELECT * FROM [ReportHelperDb].dbo.MyCustomerView".

    Do you think it will work?

    thanks a lot

    Saturday, April 5, 2014 11:24 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Yes - this will work fine.

    Scott

    Scott Durow
    Blog www.develop1.net    Follow Me
    Rockstar365
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    • Marked as answer by thenext1 Monday, April 7, 2014 8:22 AM
    Sunday, April 6, 2014 12:12 AM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you, both solutions worked... very helpful!

    Monday, April 7, 2014 8:23 AM