My Experience Setting Up TFSWindows SeverSQL Server 2008

Set up *another* VPC today. This is my first personal install of TFS 2008 ATDT on Windows Server 2008 with WSS 3.0 and Katmai (SQL Server 2008). I say personal because I’ve set one up for someone else before. Anyway, I did as I always recommend to my customers…

  • Start with CLEAN hardware (clean disk with no previous OS).
  • Get the latest TFS install guide and read it “front to back” for your type of install *before* starting.
  • Take it slow, follow the instructions to the letter and don’t skip anything.

…and I had no issues at all – the install completed without a hitch and I was up and running with a TFS 2008 SP1 / SQL Server 2008 / Windows Server 2008 ATDT install… or so I thought. Keep reading.

ISSUE 1 – RedX on Reports in Team Explorer

Following my successful setup I installed Team Explorer on the server and performed the TFS Litmus Test: Team Project Creation. It worked but there was something odd: even though the project was created successfully I had a Red-X on the REPORTS node in Team Explorer. Now if you’re like me, that makes very little sense to you. Nine times out of ten a Red-X on a node in Team Explorer means you have a permissions issue (in the case of Reports (SQL Server Reporting Services) and Documents (Windows SharePoint Services)), or TFS is completely hosed somehow (in the case of a Red-X on Work Items). Anyway, I came to discover that I had not installed Team Explorer 2008 SP1 on there and this Red-X on Reports when connecting to SSRS 2008 is a known issue with TE 2008 RTM. If you’re seeing this issue, update your Team Explorer to 2008 SP1.

 

ISSUE 2 – Constant Password Prompting For Local Web Sites

The second issue I encountered on this new VPC was constant password prompting whenever I’d hit localhost or folders under there,  even though I was logged into the PC as the ADMIN. I am still not 100% sure what was causing this, but I resolved it (with Jim’s (Saunders) help) by doing this:

  1. Open IE (7 in my case)
  2. Open Tools>Internet Options
  3. Go to the Security tab and click Local Intranet
  4. Click Sites, then Advanced
  5. Add your TFS AT to this list and OK your way back to the Options screen
  6. With Local Int**ra**net still selected, click "Custom level…”
  7. Scroll to the bottom and you should see a section titled “User Authentication” and a radio option under there titled “Automatic logon only in Intranet zone”. If this is not already selected, select it.

That should suppress the password prompts on this PC and you can probably use the same procedure for others as well.

 

ISSUE 3 – No Reports Working

So I ran through the nodes in Team Explorer after project creation (and after updating my TE to 2008 SP1, of course <g>) just to make sure everything was working. I created a few bugs, ran some queries, accessed some items under DOCUMENTS and tried to view some reports; the reports didn’t work. I was told that the cube had not been processed yet, which made sense since it was a recent install. I invoked the TFS warehouse service via the controller web service, but my reports *still* told me the cube was unprocessed. Checking my Application Event Log, I found a number of these errors:

Log Name: Application
Source: TFS Warehouse
Date: 10/16/2008 6:01:23 PM
Event ID: 3000
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: <BOX>
Description:
TF53010: The following error has occurred in a Team Foundation component or extension:
Date (UTC): 10/16/2008 10:01:23 PM
Machine: <BOX>
Application Domain: /LM/W3SVC/284281188/ROOT/Warehouse-5-128686630632089377
Assembly: Microsoft.TeamFoundation.Warehouse, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a; v2.0.50727
Process Details:
Process Name: w3wp
Process Id: 3412
Thread Id: 2424
Account name: <BOX>\<USER>
Detailed Message: Create OLAP failed
Exception Message: Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Cannot open database "TfsWarehouse" requested by the login. The login failed.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'TfsWarehouseDataSource', Name of 'TfsWarehouseDataSource'.

Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Run', Name of 'Run' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'Run ID' attribute of the 'Run' dimension from the 'TfsWarehouse' database was being processed.
Server: The operation has been cancelled.
OLE DB error: OLE DB or ODBC error: Cannot open database "TfsWarehouse" requested by the login. The login failed.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'TfsWarehouseDataSource', Name of 'TfsWarehouseDataSource'.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Run', Name of 'Run' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'Remote Run' attribute of the 'Run' dimension from the 'TfsWarehouse' database was being processed.
(type OperationException)

Processing the cube manually in SQL Server Analysis Services produced similar errors. Now I am not sure why this happened, and I’ve only heard of it once before, but the gist of the issue is this:

  • There’s a data source in Analysis Services named “TfsWarehouseDataSource”
  • This DS connects to the TfsWarehouse database in the Data Engine part of SQL
  • This DS is configured to impersonate the SQL Analysis Services service (MSSQLServerOLAPService) account.
  • My MSSQLServerOLAPService service was configure to run under the NETWORK SERVICE account.

To end the long story, the NETWORK SERVICE account had no permissions on the TfsWarehouse database in the Data Engine part of SQL. Looking closely at this DB in the SQL Data Engine I found that NETWORK SERVICE was not even listed as a user!

FIX: Add NT AUTHORITY\NETWORK SERVICE as a user to the TfsWarehouse database in the SQL Data Engine, and add it to the TfsWarehouseDataReader database role. You can do that manually, or through a SQL script like this (run it from a SQL Management Studio query when connected to the SQL Data Engine):

USE TfsWarehouse
CREATE USER [NT AUTHORITY\NETWORK SERVICE] FOR LOGIN [NT AUTHORITY\NETWORK SERVICE]
EXEC sp_addrolemember N'TfsWarehouseDataReader', N'NT AUTHORITY\NETWORK SERVICE'

Hope this helps!
Trevor Hancock

Technorati Tags: TFS,Team Foundation Server,OLAP,SQL Reporting Services,Team Explorer