SQL Server 2012 AlwaysOn failover configurations for Microsoft Dynamics CRM 2013 may cause CRM Reports to fail with error message

  • Article

This article will help resolve an issue with an error message that may be received with Microsoft Dynamics CRM reports after SQL Server 2012 AlwaysOn failover has been configured.

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

Symptoms

After configuring SQL Server 2012 AlwaysOn failover for Microsoft Dynamics CRM 2013, reports may fail with the following error message:

An error has occurred during report processing. (rsProcessingAborted)

The following errors may also be observed in the Event Viewer Application logs on the Microsoft Dynamics CRM Server and the SQL Server Reporting Services Server where CRM Reporting Extensions are installed:

Web service request SetDataSourceCredentials to Report Server <ReportServerURL> failed with SoapException. Error: An error has occurred during report processing. (rsProcessingAborted)

Unable to open connection to database. Error: Connecting to a named SQL Server instance using the MultiSubnetFailover connection option is not supported.

Cause

During the configuration of SQL Server 2012 AlwaysOn failover for Microsoft Dynamics CRM, the MultiSubnetFailover attribute for the ConfigDB and MSCRM_CONFIG connection strings was set to True. In addition, a Named Instance was defined in the Data Source attribute of the connection string.

Example Connection String:

Data Source=MSCRMAG\NamedInstance;Initial Catalog= MSCRM_CONFIG;IntegratedSecurity=SSPI;multisubnetfailover=true

Resolution

To resolve this issue, configure the Named Instance to use a specific TCP port, then update the ConfigDB and MSCRM_CONFIG connection strings to include the port number for the named instance.

  1. Configure SQL 2012 Server to listen on a specific TCP Port for the Named Instance. Instructions to do this action can be found in Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager).

  2. Per Set configuration and organization databases for SQL Server AlwaysOn failover, update the ConfigDB registry key and MSCRM_CONFIG database to include the port number for the named instance.

    Example for the ConfigDB registry key:

    Data Source=AG_Listener_Name,Port_Number;Initial Catalog= MSCRM_CONFIG;Integrated Security=SSPI;multisubnetfailover=true

    Example for the MSCRM_Config ConnectionString attribute:

    'Provider=SQLOLEDB;Data Source=AG_Listener_Name,Port_Number;Initial Catalog=OrganizationName_MSCRM;Integrated Security=SSPI;multisubnetfailover=true' where DatabaseName = 'OrganizationName_MSCRM'