Publishing ERP (Dynamics GP) Reports using Microsoft CRM

We have a client who licenses both Dynamics GP and CRM through our NjevityToGo environment. They have CRM users who do not use Dynamics GP. They only have a CRM license. However, pledges and donations will be entered into Dynamics GP. The CRM users need to see the donations and pledges as entered into line items on a Sales Order.

So, I created a nifty view that pulls the information I need from GP and created an SRS Report to summarize the info. All’s well, except for when I went to deploy the reports. Now, how am I going to put this in CRM and connect it to GP?

Here is what I did:

1) Created the view and the report (using SRS). Note: the view is in the GP database.
2) Logged into CRM and uploaded the reports. Note, I did not tie the report to any entities like Invoices. All I did was expose them in the Sales Area.
3) Tested the report and it did not run. It gave me an unintelligible message about the SQL security.
4) Logged into the CRM — Report Manager. Found the customized report and set the SQL security for the report. The SQL security is for the GP catalog and for the GP server’s login.
5) Retested and it works!

Assumptions: Latest version of SQL, CRM and GP with service packs. All databases on servers that can be connected to.