Cannot create a connection to data source ‘CRM’.

June 9, 2009 Marco Amoedo 0 Comments

The title of this post is a funny error message that you can get when trying to run a report. Initially you might see “Reporting Error, the report cannot be displayed”, but if you activate the show developer errors option you would see an ASP.Net error screen with some Exception traces containing the message “Cannot create a connection to data source ‘CRM’”.

 

image

Server Error in '/' Application.
--------------------------------------------------------------------------------

Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Microsoft.Reporting.WebForms.ReportServerException: Object reference not set to an instance of an object.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[ReportServerException: Object reference not set to an instance of an object.]

[ReportServerException: Cannot create a connection to data source 'CRM'. (rsErrorOpeningConnection)]

[ReportServerException: An error has occurred during report processing. (rsProcessingAborted)]
Microsoft.Reporting.WebForms.ServerReport.SetDataSourceCredentials(DataSourceCredentials[] credentials) +216
Microsoft.Crm.Web.Reporting.SrsReportViewer.SetExecutionCredentials(ServerReport report) +341

[CrmReportingException: An error has occurred during report processing. (rsProcessingAborted)]
Microsoft.Crm.Web.Reporting.SrsReportViewer.SetExecutionCredentials(ServerReport report) +379
Microsoft.Crm.Web.Reporting.SrsReportViewer.ConfigurePage() +735
Microsoft.Crm.Application.Controls.AppUIPage.OnPreRender(EventArgs e) +19
System.Web.UI.Control.PreRenderRecursiveInternal() +86
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041


This error drove me crazy during hours, there are various typical causes why this error can appear and all of them are related with security. In short, this error means that the Dynamics CRM SRS Data Connector is not able to connect to the CRM data base to run the reports.

There is a Knowledge Base article discussing the most common causes for this error KB 946585 related with security settings on the CRM App Pool and similar. However, in my case this was a completely fresh setup using the default setting for the Services Accounts so this KB article didn’t solve the problem.

Solution: Check that the Anonymous Access is not enabled on the Report Server virtual directory

After some more digging I found that the Reporting Services Server that I was using ( a previous installed one on another server) was configured to enable Anonymous Access. I don’t know why this was activated, but after checking with the customer’s internal IT staff we determined that it was not necessary to have this anonymous access enable, so we disabled it and voila the Reports were working again.

The explanations is simple, the SRS Data Connector impersonates the user to connect to the CRM Database and apply the adequate permissions on the Filtered Views and so on. Therefore, if you enable anonymous access on the Report Server virtual directory the SRS Data Connector will try to connect to the CRM Database using the Anonymous login, since the impersonation will not happen with anonymous access enabled. Eventually, this will cause the SRS Data Connector to be unable to connect to the CRM Database because the Anonymous login has no permission to connect to a CRM Organisation Database, and it obviously should not have.

So, if you ever get this error check whether you have the anonymous access disabled like in the picture below.

image

 

Originally published by  Marco Amoedo