SQL based reports fail to execute for Microsoft Dynamics CRM

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:

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)


NOTE: Please review the following article for the location of the SSRS log files:
http://technet.microsoft.com/en-us/library/ms156500.aspx

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

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, please refer to the following article:
http://technet.microsoft.com/en-us/library/bb522727.aspx

2. Manually give the CRMReaderRole role to the account running the SSRS service on each of the CRM databases. To accomplish this, please refer to the following article:
http://msdn.microsoft.com/en-US/library/ms187750.aspx

NOTE: If option 2 is chosen, then anytime a new Organization is created, the SSRS service account will need to be added to this new database
Properties

Article ID: 2923525 - Last Review: 11 Jun 2014 - Revision: 1

Feedback