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)

clip_image001

Steps Taken:

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

http://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 http://localhost/reports

Select the Folder Settings option

clip_image002

Add the TFS service account to Team Foundation Content Manager role

Select New Role Assignment

clip_image003

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 http://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.

clip_image004

Ran the reports again and everything ran successfully