Broken Lync Monitoring Server Reports

This afternoon I ran into an issue with deploying the Lync Server Monitoring Reports. The scenario was a 2-node SQL 2008 Back-End cluster and one of the SQL nodes had Reporting Services installed along with the Lync Monitoring role. I assume the problem has something to do with the topology in place because I haven't seen this happen in other deployments. The reports were deployed successfully through the installation wizard, but I was unable to actually run any of the them. I couldn't even open the Monitoring Server Dashboard and I would get an error like this each time:

Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'CDRDB'. ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

After searching high and low for a reason why I noticed the data sources for the reports were configured incorrectly by the deployment wizard. Even though the node was part of a SQL cluster the data source was configured as if the SQL server was a standalone local instance. You can see this in the connection string:

To resolve this use the following steps:

  1. Browse to the SQL Server Reports home page: http://<Reporting Services Server Name>/Reports.
  2. Click on the LyncServerReports folder.
  3. Click the Reports_Content folder.
  4. You'll see the CDRDB and QMSDB data sources. Click the CDRDB data source to start.
  5. In the connection string field, edit the data source to be the SQL server name (and instance if applicable) instead of the text that says "(local)".

Repeat these steps for the QMSDB data source. After that, the Monitoring reports dashboard and reports should run. As an example, my CDRDB data source connection string now reads:

data source=LYNCSQL;initial catalog=LcsCDR

When updating the QMSDB the initial catalog parameter is going to be slightly different (QoEMetrics) so be careful to only change the data source if you're leveraging copy/paste.