Cannot connect to remote MS Dynamics CRM Fetch XML datasource from SSRS

Question

I have Office 365 with Microsoft Dynamics CRM which contains business data and I have SQL Server Reporting Services on a stand-alone server that serves as a report container and my ASP.NET websites can execute these reports and people can deploy reports there.

In Visual Studio I've created Fetch XML datasource for my report supplying CRM server credentials and report runs smoothly, no problems at all. When I deploy it on SSRS server I can't execute it with the same credentials, it just throws following error:

Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).

Ok, no big deal. I googled it and found out I had to enter SystemUserId and OrganizationId in credentials, although I couldn't query them directly on SQL Server I managed to get these values through MS Dynamic CRM. Entered them in datasource's login and password inputs, clicked "Test connection" and I got this error:

System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'p_GetCrmUserId', database 'MSCRM_CONFIG', schema 'dbo'.

Ok, google it again and obviously I have to fix SQL Server user permissions and I don't have any access (and never will) to MS Dynamics CRM's SQL Server to fix permissions issue. Why does it work in Visual Studio, but fails on SSRS server? What do I do to fix this conenction issue? Thanks.

Answer 1

You will not be able to run Fetch XML reports locally against a Dynamics CRM Online instance. You would need to deploy those reports to the Dynamics CRM Online organization and run them from Dynamics CRM.

It works in Visual Studio because the CRM Report Development extension makes it work for development, but it cannot be deployed directly to an SSRS server - the Fetch XML reports can only be deployed through Dynamics CRM to the SSRS server configured in CRM.