Recently, we had an interesting issue with an internal CRM report. While trying to set up a subscription in SSRS (SQL Server Reporting Services), we got the following error:
What the heck does that even mean?
Although there are plenty of reasons that this error can be thrown, the most common is simply that yes, the credentials to run the report are incorrect.
And these are the credentials in SSRS, not in CRM. Oftentimes you will be able to run the report fine from CRM – but when you try to run the report in SSRS, you’ll get prompted for credentials (although this is not always the case).
However, when you attempt to enter valid Windows or CRM credentials, you’ll get this 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).
All of which serves to muddy the issue further.
The easy answer is that it does, indeed, want the GUID format of the username and password.
1.) First, navigate to your report in SSRS. Click the drop-down arrow next to the report name, then click Manage. (If you click Subscribe, you’re going to get the original error I inserted above as a screenshot. Although that is the goal of this exercise, we have to take a different route to get there.)
2.) Go to Data Sources. Click on the Custom Data Source option button, then choose the Microsoft Dynamics CRM Fetch type from the drop-down, and add CRM in the Connection String box, as shown below.
3.) Click the option button for Credentials stored securely in the report server, as shown in the above screenshot. The username and password field will be blank at this point; we need to retrieve those.
4.) Open up your SQL Server Management Console from your SQL box and create a query to show the GUID information you need; Microsoft has a Knowledge Base Article kb2006869 that outlines the process. Just follow the steps 1-7.
5.) The user you choose doesn’t really matter here, as long as they are a user in CRM and SSRS. This name will not show anywhere on the report. In the example above, I used myself. So, I copy and pasted the field values as follows:
|From SQL Server Query||To SSRS|
6.) Click Test Connection to make sure it connects successfully, then Apply.
7.) Click on the Subscriptions tab of SSRS, and create your Subscription!