A Five Part Series: Troubleshooting TFS and SQL Reporting Analysis Services
Brett Keown brings us this series. We hope you find it helpful and contribute some suggestions for part 5.
-Trev
------------------------------------------------------
There are lots of moving parts to Team Foundation Server. With depth knowledge necessary in Active Directory, SharePoint, Analysis Services, SQL Server, SQL Server Reporting Services, Visual Studio, Networking, IIS, etc… no one person can understand every aspect of the product or the implications of how making changes in one product will affect the other areas. Supporting TFS is a team driven, multi-disciplined effort. In support we find that it is much more efficient (and increases our customers satisfaction) if we scope issues to a root cause and then collaborate with different types of depth experts to resolve increasingly complex issues. One product team we collaborate with frequently is SQL Server Reporting Services (SSRS). Issues with SSRS and Analysis Services are common as these products will touch most of the technology areas Team Foundation Server is dependent upon.
With this in mind, I felt that it would be nice to provide our readers with some of the more common issues we see in Team Foundation Configuration Support. The emphasis here is to address the more "common" issues we run into - things that typically can be resolved without finding a depth Subject Matter Expert (SME) to collaborate with. To that end we begin today a series of five BLOG posts. The structure of these posts will begin with some fairly basic and straight forward break / fix type problems and move on to issues reported in our MSDN forums or previous cases. In part five of this series I will take two topics that you our readers suggest and research them so that we may provide steps to guide you towards resolution. Please comment any of the first four parts to have your ideas considered for part five.
Over the course of a month or so I will post regular updates to our blog which will cover the following topics:
Part 1: An Introduction - Best Practices for rebuilding the TFSWarehouse
Rebuilding the TFSWarehouse is a task that every TFS Administrator will need to perform sooner or later. What are the errors you may receive when going through this process? What happens when you open Visual Studio in the morning and notice that the Reports node of Team Explorer has a "Red X" over its icon!? Hopefully this post will provide you with the most common issues we run into with the TFS warehouse and the steps we take to resolve these issues.
Part 2: Incorrect BASE URL Setting in TFS for SSRS
An error we have seen often in support and on the MSDN forums references the Base URL to the reporting server for TFS. What happens when this is wrong? What errors do we receive which point to the problem? Week two's post contains a very straight forward approach to resolving this type of issue. Here is an error straight from the forums:
The path of the item "//Sites/MyTracker/Remaining Work" is not valid. The path must be less than 260 characters long and must start with slash. Other restrictions apply. (rsInvalidItemPath)
The second issue we will cover in part two deals with data flow and the steps you should take when you suspect that indeed, the data has stopped flowing from the Operational Store into the Data Warehouse. There's a not so difficult process that can help you verify that you are running into this problem AND help you fix it.
Part 3: Splitting SSRS and Analysis Services off the TFS App Tier to their own cluster
A configuration question we are beginning to see more and more is... "How do I split my Reporting and Analysis Services Servers off of the Application Tier and into their own clustered environment?" While the issue itself is supported by our talented Installation and Setup team, I was fortunate enough to work with Arun Ramalingam recently who patiently and gracefully walked one of my customers through this process. This post will document how they got the job done.
Part 4: Troubleshooting permission errors in SSRS
Ok, so your boss was working late gathering reports for that next business meeting with the company bigwigs... s/he sits down to begin work and receives an error,"The permissions granted to user "<username>" are insufficient for performing this operation." when attempting to access the reporting site. The next call s/he makes is to you while you are at dinner with some friends. How do you troubleshoot and resolve this issue as quickly as possible, look like the hero and get back in time for coffee & desert? This post will contain the most common methods and resolutions for this error.
Part 5: Audience participation; what would YOU like to see addressed here?
This one is up to you! What two Reporting Services / Team Foundation Server issues would you like to see us address? Please comment any of the posts in this five part series to have your ideas considered. Reader support of your ideas will help us choose which two topics to cover. Please keep in mind that we are focusing on "common" TFS / RS issues and not "one off" type incidents, though if you come up with one unique and interesting enough, we might just do it.
An Introduction - Best Practices for rebuilding the TFSWarehouse:
Ok, now that you we've outlined the scope of this series let’s get down business. There are just a handful of issues we typically see with reports. This post will cover the most frequent offenders, what the error messages actually mean and how we resolve the issue. Most errors are self explanatory in nature (yes, believe it or not!) and most can be resolved fairly easily if you just know how. For this post I will walk you through the process I would actually use with a customer who has called in with the problem statement, "I have a red x on reports in the Team Explorer." Rarely do we see all of these issues in one call, but it has been known to happen. So you have a Red X on reports, what now? Here are the first things I will ask you when you call in:
What version of Team Foundation Server are you using including Service Packs?
What version of Team Explorer is being used on the client including hotfixes and power tools?
Which version of Visual Studio are you using... you guessed it, including Service Packs?
Which Operating System version are you using on each Server including Service Packs (are you beginning to see a theme here :))?
Which version of SQL Server are you using including Service Packs. Is your installation of SQL Server in a clustered environment?
Where do your Analysis Services and Reporting Services instances reside?
Many TFS administrators will know this information right away. The problem is that most of the time, it’s the clients that run into the issue. If you do not know the answer to any of these questions, or if you are unsure... PLEASE let us know. We can quickly assist you in answering each of these questions. If you give us the wrong answer we can spend many hours (and many dollars) troubleshooting an issue that we may have been able to resolve in five minutes. You will see this below. You can get this information quickly as well. Simply download the Microsoft Visual Studio Team Foundation Server Power Tools. In the Power Tools package you will have an opportunity to install the Best Practices Analyzer which will poll your installation for all of this data and put it into a handy tree structure which can be reviewed by you or uploaded to support. I don't want to ruin the surprise, so I won't get into EVERYTHING that the BPA scan will tell you, but it is the single handiest tool we have for troubleshooting configuration issues. Download your version here:
For TFS 2005
For TFS 2008
Figure 1: A broken TFS Installation I use for testing
This tree view contains data on your installation, versions, server locations, permissions, product conflicts, event logs, application pools, etc.
We ask these questions for a few reasons. We'd like to understand your infrastructure before we begin working and there is a common call we get for this "Red X" on reports issue. Essentially this error message means that Team Foundation has requested a list of children from Reporting Services. For whatever reason, we can not retrieve this list of children. If you have a mixed environment of 2005 and 2008 clients, things will be humming along just fine, and then one day you come into the office to find that all of your TFS 2005 clients have this issue. What gives? Chances are you're IT group updated to SQL 2008 last night (or more likely, over the weekend). Well, what do you do now? TFS 2005 clients are not supported with SQL Server 2008. This will not work and no amount of mucking around with it will get it to work. You have two options really. 1. You can update all your clients to the 2008 version of TFS, or; 2. You can give your clients a method for obtaining the data they need outside of TFS. The best way to do this is to just browse to the reporting site <servername>:80/reports. Once there, choose the report they would like to run.
When we are sure you have the correct product versions, the very next question will be "Has it ever worked?". The Best Practices Analyzer (BPA) will catch most issues with reports that have never worked. So let’s say you browse to the reports site but are having some very bad luck. The first issue you encounter is this error:
An error has occurred during report processing. (rsProcessingAborted) Cannot impersonate user for data source 'TfsOlapReportDS'. (rsErrorImpersonatingUser). Log on failed. (rsLogonFailed)
What now? Well most likely you are having a permissions issue (rsLogonFailed). You can verify this by checking the Security Event logs for issues of type "Audit Failure". Well how do you know what permission failed and where it failed from? Go back to the error message. It states that it ran into a problem while attempting to impersonate a user when connecting to the data source 'TfsOlapReportDS'. Let’s take a look at that data source and verify the credentials.
Figure 2: TFSOlapReportDS Properties Window
I am sure I have the right password in here (as we all are <g>) but I'll type in my existing credentials anyhow, then hit "apply". No need to reset IIS here, go ahead and try to run the report again. It seems that we've gotten through this error, because now... we have a new one. Ok, onto the next issue.
An error occurred during client rendering. An error has occurred during report processing. Cannot impersonate user for data source 'TfsReportDS'.
Log on failed.
Well, it makes sense that if changing the password for TFSOlapReportsDS fixed our previous problem, than we have the same issue occurring with TFSReportDS displayed with a slightly different message. This error references the same "Log on failed" message and also states "Cannot impersonate user for data source 'TFSReportDS'. I go through the same process of typing in my username and password on the data source properties page in <servername>/reports for TFSReportDS and voila, it works! Wonderful, the report frame has come up, but wait... There is no data. I look at the timestamp at the bottom of the report and see that the TFSWarehouse hasn't been processed in over a week! That makes sense as the account credentials were invalid. Well, we know it’s not a data source access issue now. Maybe we were having some more widespread errors that were masked by the first few problems.
When I browse to my Report Manager for TFS, I can see that the report was generated correctly. The timestamp for "Report Generated" reflects the time / date the report was run. But there is no data below. When I look just to the right of the first timestamp, I see that the "Last Warehouse Update" was over a week ago. At a minimum, we update the warehouse hourly using the default settings. Yours may be much different depending on how customized your implementation of TFS and report processing are. So if we aren't updating the reports, we could try to go into SQL Server Management Studio, connect to Analysis Services and process the warehouse, but if we have a problem in there already, most likely processing the warehouse isn't going to do much to help with our current situation. Instead, let’s rebuild the TFSWarehouse. Since its data is taken from other databases it can be rebuilt even if you don't have a backup; that is, assuming your other databases have good data. Take a backup anyhow to be safe, and then run "The Big One". If you have read our blog for any amount of time you will most likely be familiar with the term "The Big One". It’s our nickname for a rebuild of the TFSWarehouse. We call it The Big One due to the length of the command entered from the command prompt.
IMPORTANT NOTE: Before continuing with the rebuild of the warehouse, PLEASE read this post (it’s not as serious as it sounds, but you should be aware):
Use of “SetupWarehouse.exe –rebuild…” can cause data loss in Team Foundation Server 2008
“During warehouse sync, the Build adapter uploads the build information to the TfsWarehouse relational database. When the upload of data is completed the tbl_ServerStatus table is updated with a watermark of the last uploaded build. Each night there is a SQL job (TfsBuild Team Build Administration Job) which runs and based on the information from the tbl_ServerStatus table, removes all unnecessary nodes from builds that have already been uploaded to the TfsWarehouse relational database.
That means that if you rebuild the warehouse, this information will be missing from the build op store hence it will not be uploaded by the adapter in the TfsWarehouse relational database.
The obvious question is: Will this rebuilding the warehouse affect the data from the build reports?
The short answer is no. The build related reports that come with Team Foundation Server display compilation summary information at the root node level. As a result, even though rebuilding the warehouse removes compilation summaries for child nodes that have 0 errors/warnings and static analysis errors/warnings, it will not affect the data displayed in the reports.”
Once your backups are complete, go ahead and open a Visual Studio command prompt. Next, type in the following command replacing <bracketed> values with your own accounts and server names:
“C:\program files\microsoft visual studio 2008 team foundation server\tools\setupwarehouse.exe” /setup /install –n –s “<DATA TIER SERVER>” –d “TFSWarehouse” –c “C:\program files\Microsoft visual studio 2008 team foundation server\tools\warehouseschema.xml” –ra “<DOMAIN\REPORTING SERVICES ACCOUNT>” –a “<DOMAIN\TFS SERVICE ACCOUNT>” –v –l
Here's an example with real values:
“C:\program files\microsoft visual studio 2008 team foundation server\tools\setupwarehouse.exe” /setup /install –n –s “BRETTKETFSDT” –d “TFSWarehouse” –c “C:\program files\Microsoft visual studio 2008 team foundation server\tools\warehouseschema.xml” –ra “Redmond\bktrsreports” –a “Redmond\bktfsservice” –v –l
If you're lucky or just pay very close attention to the details, this will work great and you will receive a message indicating that the update has completed. If not (and most people fall into this category) you will receive one of a few errors. What errors can be returned when running this command? What do they mean? For this blog post I intentionally made some mistakes so that we can review some of the types of errors we may run into. Here's the first error returned:
An unexpected error occurred: Error encountered when creating connection to Analysis Services. Contact your Team Foundation Server administrator.
This could be a few things, but if you are looking for common issues, most likely you have a typo in your Data Tier server name. You can find the correct name by opening SQL Server Management Studio and connecting to Analysis Services. Once there, right click on the TFSWarehouse and view the connection properties. In the product properties window you will find the server name listed. So I correct the error (SHIFT + TAB in the command window will bring up your command again, then use the arrow keys to go back and correct the problem) and run the command again:
NOTE: A great way to avoid typo’s is to use a .CMD file which you can create in notepad. It’s much easier to edit a .cmd file in notepad, then to edit from the command prompt directly. Bing it if you would like to learn how; there are lots of examples available on the internet.
C:\Users\brettke>"c:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\tools\setupwarehouse.exe" /setup /install -n -s "BRETTKE082008" -d "TFSWarehouse" -c "c:\program files\Microsoft Visual Studio 2008 Team Foundation Server\tools\warehouseschema.xml" -ra "redmond\bktfsrept" -a "redmond\bktfserv" -v -l
Dangit, we have another error:
An unexpected error occurred when connecting to the SQL Server. Check that your SQL Server is up and you have sufficient privileges on it: Windows NT user or group 'redmond\bktfsrept' not found. Check the name again.
To test that the account is good, I go over to another machine and try to log in with the credentials I provided to TFS. This is the single most efficient way to test an account name, its password, domain authentication for a single account, etc. When attempting to log in I am denied access to the machine. Well, it must be a problem with the account. So I look at the account name again and realized that I had a typo there as well. If I am certain the account name is valid, I would begin checking for a valid password. Once I fix this typo I am able to successfully rebuild the warehouse and receive the message:
Setup warehouse Completed Successfully.
Great! Now I go back into the Report Manager and run my report. As my daughter would say (Check the Dora reference) ... "Awww Mannnnn! Another error!” This time right in the report window we see:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'IterationParam'. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
This error is telling us that we have rebuilt the warehouse, but we have not repopulated it with fresh data! Here are the steps to refresh the data in TFSWarehouse.
1. Browse to <servername>:8080/Warehouse/v1.0/warehousecontroller.asmx
2. Click on "GetWarehouseStatus", and then click "Invoke". It should return "Idle".
3. Now, let’s test it to make sure it’s really running. Go back to the ControllerService page and choose Run".
4. On the next window, click "invoke". This should return a value of "true".
5. This next step can take awhile depending on how much data you're processing. Now that the adaptor is running, click on "GetWarehouseStatus" every 10 minutes or so to check where it is. There are a few different states that can be returned:
- Running Adaptors means we are getting started on processing.
- Processing OLAP is just that. TFSWarehouse is now processing the OLAP database and repopulating the cube
- Idle lets you know we are done.
NOTE: if changes are made to the schemas during the "processing olap" phase, the status will return to running adaptors. Its ok, this is as it should be. Just keep letting it run until it returns idle. Now go into the Report Manager... and finally... it’s done.
Report Generated: 9/16/2009 4:04:05 PM by REDMOND\brettke; Last Warehouse Update: 9/16/2009 4:02:30 PM
In part 2 we will be discussing data flow problems and the Base URL!
Brett