Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. SQL based reports fail to execute for Microsoft Dynamics CRM

SQL based reports fail to execute for Microsoft Dynamics CRM

View products that this article applies to.

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

↑ Back to the top

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.

↑ Back to the top

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

↑ Back to the top

Keywords: kbmbspartner, kbmbsmigrate, kbsurveynew, kb

↑ Back to the top

Article Info
Article ID : 2923525
Revision : 1
Created on : 1/7/2017
Published on : 6/11/2014
Exists online : False
Views : 228