The number of accounts and components used in TFS reporting can makes its security design confusing. Let me clarify here. Hope it will help.
o There are two accounts involved:
o TFSService account (the one you use in running the TFS app pool on the AT)
o TFSReports account (will be explained later)
o There are 5 components involved:
a) Relational database TfsWarehouse (on DT)
§ It contains two security roles:
i. TfsWarehouseDataReader - The account TFSReports should be in this role. It allows READ access to the database
ii. TfsExecRole – The account TFSService should be in this role. It allows R/W access.
b) OLAP database TfsWarehouse (on DT, SQL AS2005 running as Network Service)
§ It contains:
i. One database role TfsWarehouseDataReader – The account TFSReports should be in this role. It allows READ access to the database.
ii. TFSService has server wide permission on the whole AS server.
c) TFS Warehouse IIS web application (on AT, running in Team Foundation AppPool, which runs under the TFSService account)
d) Reporting Services (on AT, running as Network Service, on AT/DT, running as Local System)
§ Inside RS, you will find two data sources:
i. TfsReportDS – Used to connect to (a) using the credential TFSReports
ii. TfsOlapReportDS – Used to connect to (b) using the credential TFSReports
e) TfsServerScheduler:
§ An NT service running on the AT.
o How they interact:
1. Every hour TfsServerScheduler (e) will call the Warehouse web application (c) to pump data into the relational warehouse (a)
· Note: (c) is running under TFSService, which is included in TfsExecRole (a)(ii).
2. Warehouse web application (c) will then build the Team System cube in the OLAP database TfsWarehouse (b) using the data stored in (a).
· Note: Again, TFSService has permission to write to (b) because of (b)(ii)
3. All the reports are stored in the Reporting Services (d). When a user view a report, the report will connect to (a) and (b) using the data sources (d)(i) and (d)(ii).
· Note: The data sources can connect because of (a)(i) and (b)(i)
o Other notes:
1. The account TFSReports will also be stored in a config table in (a). Use this SQL script to view it. You may need to fix it on your machine if we change account.
SELECT [ID], [Setting]
FROM [TfsWarehouse].[dbo].[_WarehouseConfig]
