Team Foundation Server - Application Tier to Data Tier: Authentication, Impersonation and Authorisation

Question

According to the Team Foundation Server Architecture document, in the Groups and Permissions section:

Team Foundation Server has its own set of default groups and permissions that you can set at the project, collection, or server level. You can create custom groups and customize permissions at group and individual levels. However, users or groups that you add to Team Foundation Server are not automatically added to two components on which Team Foundation Server can depend: SharePoint Products and Reporting Services. If your deployment uses these programs, you must add users and groups to them and grant the appropriate permissions before those users or groups will function correctly across all operations in Team Foundation Server.

Authentication and Impersonation:

Please support your answer with evidence by way of profiler traces, configuration snippets, or an authoritive description from Microsoft articles (personally, I couldn't find any).

1. Is integrated security enabled from the Application Tier through to the underlying Sql Server?
2. If integrated security is enabled, is impersonation enabled (assuming a standard configuration) to impersonate the identity of the user within the Application Tier?
3. If impersonation is enabled, is the Application Tier responsible for managing the security of the underlying databases?
4. If impersonation is not enabled in the Application Tier, is all interaction with the Data Tier done by the TFSService identity?

Authorisation:

1. To the best of available knowledge, is authorisation evaluated in the Data Tier or in the Application Tier (i.e. the value of Project.HasWorkItemReadRightsRecursive)?

Why:

I have programmed a solution in which I am passing integrated security from the client's process, through a WCF web service and into Sql Server using impersonation, from where I can evaluate object authorisation and role membership using Transact-Sql. We are discussing the advantages and disadvantages of this as an appropriate pattern and decided to investigate how TFS handles this.

If you have any broader comments on object level authorisation within a database driven application, please feel free to share them.

Answer 1

Rabid,

Most of what you're looking for can be found in the web.config for TFS web services and by looking at the security of your TFS databases.

1. Is integrated security enabled from the Application Tier through to the underlying Sql Server?

Yes. The web.config is located here on your application tier server: C:\Program Files\Microsoft Team Foundation Server 2010\Application Tier\Web Services

Within there you can find the connection string for the Tfs_Configuration database. This quite explicitly shows that it uses Integrated Security.

<add key="applicationDatabase" value="Data Source=YOURSQLSERVER\YOURSQLINSTANCE;Initial Catalog=Tfs_Configuration;Integrated Security=True;" />

2. If integrated security is enabled, is impersonation enabled (assuming a standard configuration) to impersonate the identity of the user within the Application Tier?

No. When TFS connects to the database it uses the credentials within the Microsoft Team Foundation Server Application Pool, not the credentials of the calling end-user. Again from the TFS services web.config...

    <!-- Disable Identity Impersonation -->
    <identity impersonate="false"/>

This is further evidenced by the fact that your end-users of TFS do not have any level of access whatsoever to the underlying Tfs_Configuration database. (or the project collection databases, for that matter)

If you open your Tfs_Configuration database in SQL Management Studio and look at the Security folder you will only see users listed who have been added as "Administration Console Users" in the TFS Administration Console.

3. If impersonation is enabled, is the Application Tier responsible for managing the security of the underlying databases?

N/A due to the answer to question #2. However, the answer is "yes." When you initially configure TFS (assuming you use the Microsoft installation guide, which you should be using: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=24337) you will setup your "TFSSERVICE" account with the sysadmin or *db_creator* security role on your SQL server. This allows the TFS application tier to manage the security of its own databases. When you add a new "Admin Console User" it will grant that user permissions on the TFS databases. You can see this yourself by looking at the Security folder for the Tfs_Configuration database before and after adding a user to the admin console.

Additionally, if you open the TFS Admin Console as a user who does NOT have access to the underlying databases the console will load with a bunch of friendly "User does not have database access" error messages instead of populating the data you would expect to see in the admin console.

4. If impersonation is not enabled in the Application Tier, is all interaction with the Data Tier done by the TFSService identity?

Yes. I think everything stated above pretty clearly demonstrates this. Even the application pool for TFS Web Access runs under the TFSSERVICE identity. (this is all out-of-the-box, of course... you can always explicitly grant access to a TFS database and start monkeying around with it yourself... you'll void your support contract with MS by doing so though :D)

Authorization Question: To the best of available knowledge, is authorisation evaluated in the Data Tier or in the Application Tier (i.e. the value of Project.HasWorkItemReadRightsRecursive)

This authorization (Project.HasWorkItemReadRightsRecursive) is evaluated within the Application Tier. This is true of all work items and version control items. Why? Because this is part of TFS' internal security model. There is an extensive set of permissions that TFS maintains for its version control and work item objects which are completely decoupled from the underlying data layer. Having access to read\write to a specific work item or file under version control does not mean that you have access to the SQL tables which contain the data for those version control or work item objects.

It's all more or less spelled out here. http://msdn.microsoft.com/en-us/library/ms252587(v=vs.100).aspx There are server level permissions, collection level permissions, team project level permissions, build level permissions, work item permissions, and version control permissions. This entire permissions system is orchestrated within the TFS application tier without any knowledge of the underlying database schema or database security.

To echo the point which you opened your question with

However, users or groups that you add to Team Foundation Server are not automatically added to two components on which Team Foundation Server can depend: SharePoint Products and Reporting Services.

Why is this? Because SharePoint and Reporting Services both use a similar pattern where there is an extensive security system which is managed at the application tier but only one (or maybe a few) accounts have actual access to the SQL databases. I.e. You can set Content Viewer, Content Manager, etc permissions in SSRS and you can set Contributor, Site Collection Admin, Farm Admin, etc permissions within SharePoint. Your SSRS service account or SharePoint farm admin account will be the only accounts which have SQL database access.

In summary

If the application you're developing is taking the actual client user's credentials and passing them all the way down to the database you could be opening yourself up to a major security problem. Since those users' accounts will have direct SQL access there's nothing stopping them from opening up SQL Management Studio, connecting to the database, and doing whatever they have access to. Maybe your users aren't savvy enough but why take the chance??