SQL based reports fail to execute for Microsoft Dynamics CRM

This article provides a solution to an error that occurs after installing the Microsoft Dynamics CRM Reporting Extensions.

Applies to:   Microsoft Dynamics CRM 2011, Microsoft Dynamics CRM 2013
Original KB number:   2923525

Symptoms

After installing the Microsoft Dynamics CRM Reporting Extensions, SQL based reports, such as the out of the box reports, fail to run within Microsoft Dynamics CRM. The SQL Server Reporting Services (SSRS) log files show the following message:

processing!ReportServer_####!MM/DD/YYYY-HH:MM:SS:: e ERROR: Data source 'ORGANIZATIONUNIQUENAME': An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'ORGANIZATIONUNIQUENAME'. ---> Microsoft.ReportingServices.DataExtensions.ReportServerDataProvider.RSDPException: You have specified integrated security or credentials in the connection string for the data source, but the data source is configured to use a different credential type. To use the values in the connection string, you must configure the unattended report processing account for the report server.
at Microsoft.ReportingServices.DataExtensions.ConnectionExtension.FailIntegratedSecurity()
at Microsoft.ReportingServices.DataExtensions.ConnectionExtension.AppendCredentials()
at Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapperBase.get_ConnectionString()
at Microsoft.ReportingServices.DataExtensions.ConnectionExtension.Open()
at Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapperBase.Open()
at Microsoft.ReportingServices.Diagnostics.DataExtensionConnectionBase.OpenConnection(IProcessingDataSource dataSourceObj, DataSourceInfo dataSourceInfo, IDbConnection conn)
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.Diagnostics.DataExtensionConnectionBase.OpenConnection(IProcessingDataSource dataSourceObj, DataSourceInfo dataSourceInfo, IDbConnection conn)
at Microsoft.ReportingServices.Diagnostics.DataExtensionConnectionBase.HandleImpersonation(IProcessingDataSource dataSource, DataSourceInfo dataSourceInfo, String datasetName, IDbConnection connection, Action afterImpersonationAction)
at Microsoft.ReportingServices.Diagnostics.DataExtensionConnectionBase.OpenDataSourceExtensionConnection(IProcessingDataSource dataSourceObj, String connectString, DataSourceInfo dataSourceInfo, String datasetName)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSource.OpenConnection(DataSource dataSourceObj, DataSet dataSetObj, OnDemandProcessingContext pc, DataProcessingMetrics metrics)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSource.OpenInitialConnectionAndTransaction()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSource.InitializeDataSource()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSource.Process(Boolean fromOdp)

Cause

The account running the SSRS service is a virtual or local account such as LocalSystem. These virtual or local accounts do not have the necessary permissions to query the FilteredViews. The SQL permissions required to query the FilteredViews is a custom role called CRMReaderRole. This role is given to the Active Directory group named PrivReportingGroup. During installation of the Reporting Extensions, the SSRS service account is added to the PrivReportingAccount. Virtual or Local accounts cannot be added to an Active Directory group.

Resolution

To fix this issue, follow these steps:

1. Run the SSRS service under the context of Network Service. For increased security, run the SSRS service as a managed service account, a low privileged user in Active Directory. To change the service account, see Configure a Service Account (SSRS Configuration Manager).
2. Manually give the CRMReaderRole role to the account running the SSRS service on each of the CRM databases. See sp_addrolemember (Transact-SQL).