Cannot create a connection to data source 'CRM'. ---> Microsoft.Crm.Reporting.DataExtensionShim.Common.ReportExecutionException: Immediate caller Domain\SqlService has insufficient privilege to run report as user S-1-5-21-1162093662-620106126-315576832-39399.
The CRM is installed on the application server and the SSRS is installed on different server. After futher investigation it turns out that it's caused by double hopping issue regarding the authentication and we just need to set the correct SPN on the machines.
setspn -a http/your-crm-server-name domain\crm-user
setspn -a http/your-crm-server-name(FQDN) domain\crm-user
Other things to check to make sure your reports work:
1. Make sure your SRS connector has the same RollUp version as the CRM Server RollUp
2. In Reporting Service Configuration Manager, uncheck the 'Specify an Execution Account'
3. Browse to both SharedReports\5.0.xxxx\ and
To run the report from the report server Url, you might find that if you are using normal domain account for username/password you will get the following error:
An error has occurred during report processing (rsProcessingAborted) Cannot create a connection to data source ‘CRM.’ (rsErrorOpeningConnection) Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).
This is because the Username should be 'systemuserid' and the Password should be 'organizationid'. You can get this by running the query against the CRM database:
SELECT fullname, systemuserid, organizationid FROM FilteredSystemUser
Hope this helps,