Lync 2013 Monitoring Reports Fail to Load with “Query execution failed for dataset” Error

I was deploying Lync Server 2013 Monitoring Reports last week in a 2x Standard Edition deployment (pool paired – my architecture of choice for organisations under 2000 users) with a single SQL 2012 backend server. I deployed the reports like normal, went to access the URL and got this:

report server 1

I was thinking “this doesn’t usually happen, I am perplexed. Monitoring reports are usually bulletproof!”.

The error read (for those that enter it into Google/Bing when this happens to them):

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset ‘CDR_SyncTimeZone’. (rsErrorExecutingCommand)
The EXECUTE permission was denied on the object ‘RtcSyncTimeZoneInfo’, database ‘LcsCDR’, schema ‘dbo’.

Weird huh? We’d just installed the latest server cumulative update, so I tried reinstalling Monitoring Reports after I’d applied the CU database update also, but no dice. I started digging through stored procedures and permissions, but nothing was really jumping out at me. I thought this was something to do with SQL Server Reporting Services (SSRS) not being initialised properly or just not playing ball, so I reinstalled it and redeployed the reports, still nothing.

After some searching, I found a similar SSRS problem and worked out this was permissions related somehow.

In SQL Server Management Studio I went into the login properties for the account that has access on the Monitoring databases and gave it db_owner rights on both databases.

report server 2

I went back to my browser, hit refresh on the Report Server URL and bam, nice properly functioning Lync 2013 monitoring reports.

Why things didn’t work out of the box, I don’t know. I don’t think there’s any risk in this account having db_owner rights on the Monitoring databases as it is just a service account, but it’d be worth documenting in your environment all the same.

One thought on “Lync 2013 Monitoring Reports Fail to Load with “Query execution failed for dataset” Error

Leave a Reply

Your email address will not be published. Required fields are marked *