TFS Reports - Query execution failed for dataset … (rsErrorExecutingCommand)

 

I have encountered the error Query execution failed for dataset … (rsErrorExecutingCommand) a few times now when creating a new TFS environment for customers and would like to detail the troubleshooting and resolution to the issue. For this situation, we were building a single server TFS install with TFS, SQL, SSAS, and SSRS all on the same server and network service was the account used for TFS.

Issue:

When running certain SSRS reports after the installation, the error "Query execution failed for dataset … (rsErrorExecutingCommand)

Steps Taken:

Followed the steps outlined in this article to ensure the TFS service account has the correct permissions on the data sources:

https://msdn.microsoft.com/en-us/library/dd631908.aspx

If the report server and Team Foundation Server are not on the same server, you must add the name of the service account that you will use for Team Foundation Server (TFSSERVICE) to the Content Manager group on the report server. This step is done automatically during Team Foundation Server setup if the report server is installed on Team Foundation Server.

If you are using Network Service for TFSSERVICE, you must add the machine name of the server that is running Team Foundation Server instead of Network Service. The machine name is the server name followed by the $ symbol. For example, Domain\ServerName$.

Open the Report Manager administration web site https://localhost/reports

Select the Folder Settings option

Add the TFS service account to Team Foundation Content Manager role

Select New Role Assignment

Add the account name (may have to prepend with domain name) and select the Team Foundation Content Manager role.

After adding the TFS Service account to the role, the error was still appearing when we ran the report.

Decided to investigate the error in the SSRS log files located here: C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles and found execute permission denied for the service account when attempting to run the report.

Next step was to verify the cube job was processing. Followed the steps outlined in this article to check the cube status https://msdn.microsoft.com/en-US/library/ff400237(v=vs.120).aspx and found it was failing with a permission error on the cube.

Verified the service account used in TFS (this was a single server install and was using network service) and added the TFS service account to the TFSWareshouseDataReader and TFSWarehouseAdministrator role in the Analysis Service.

Ran the reports again and everything ran successfully