TFS 2010 – Revoking database privileges to TFS accounts during normal operation

  • Article

 

This question has come up quite a few times and this is my take on this. During installation and configuration of TFS you give the TFSSetup and TFSService account certain DBO privileges on the SQL databases. Typically these are the two major accounts involved though you might have other accounts such as TFSReports, TFSBuild, TFSTest etc, depending on whether you followed the install guide to its letter.

Now SQL DBAs often ask the following question: Is it ok to revoke DBO privileges to these accounts after the initial setup when everything has been done, and the TFS Server is now in normal usage mode?. They ask what are the explicit database privileges and why these are needed.

In the following post, I break down this into two sets of situations, and point out that the distinction between the two modes (setup versus normal operation) are not clearly demarcated as everyone would have wished.

Default Set of Privileges:

The following (let us call it the Defaults) are required during installation, configuration and as well as during any operation that will be considered as a maintenance/setup/hot-fix/patch/reconfiguration of TFS.

  • Membership in the serveradmin server role. During TFS configuration we install new system messages. This requires membership in the serveradmin role, see: https://msdn.microsoft.com/en-us/library/ms178649.aspx.
  • ALTER ANY LOGIN, VIEW ANY DEFINITION server permission. During TFS configuration we enumerate SQL logins and create a SQL login for servicing account.
  • CONTROL on master. Here is a list of privileges that we need on master:
    • CREATE ROLE – We need this permission to TFSEXECROLE role.
    • VIEW DEFINITION – We need this permission to see extended properties on the database.
    • ALTER – To install messages, we drop and recreate prc_Install*Messages stored procedures, such as prc_InstallBuildMessages, prc_InstallVersionControlMessage, etc. We need ALTER permission to do this.
    • EXECUTE – We need this permission to execute prc_Install*Messages stored procedures.
    • CREATE DATABASE - We need this permission to be able to create configuration, collection, and warehouse database

The above are required for doing any setup/configuration/reconfiguration or just about running most of the command line Administration tools of TFS. In my opinion, it is best to leave TFS with the above set of privileges. Now let us see normal usage scenario.

Day-to-Day Usage of TFS:

There is no official document (as of this writing) that officially differentiates a normal usage mode of TFS, from setup phase so that DBA could then go and revoke certain privileges, for the day-to-day operations. Also there is a good reason for NOT doing that. Doing so can easily put you in an unsupported state (if you raise a support incident with MS). After a proper TFS installation, the configured database settings should work with all supported TFS operation and migration scenarios without requiring any manual touch of the databases (apart from backup/restores). 

Some of the day-to-day aspects of using TFS might involve making some changes to the Database which require DBO priviliges. That is why the TFSService needs to have this privilege. Now what are these:- (the following is not an exhaustive list)

  • There is a set of CORE Job (Refer: https://msdn.microsoft.com/en-us/library/ms252450(v=vs.100).aspx) that run on the server in the background and these have to be factored in. The Team Foundation Background Job Agent service uses the same service account as Team Foundation Server does, TFSService. To operate correctly, this account requires the following permissions:

    • Log on as a service

    • Farm Administrators group for any SharePoint Web applications that Team Foundation Server uses

    • TFSExecRole or both of the following for any databases that Team Foundation Server uses:

      • db_owner

      • db_create

  • Project Collection creation is another place where DDL/DML privileges are required as database(s ) get created when you create collections. You can overcome the requirement to have them created by the setup wizard , and instead have the  DBAs pre-create the databases and then using the Advanced wizard to use those pre-created databases for your project collections. There are some customers who do this, and in my experience by far the majority of the customers use the Admin Console to do this operation, and do not pre-create the databases.
  • There are others sets of operations too – most notably command line operations (see list below).

The distinction between “normal-usage” and “Default” in the deployment and real usage of TFS is really not something well defined, though normally there should be, and DBAs are usually concerned with this.

In a REAL normal usage scenario the typical operations will normally spill over and if you disallow DBO privileges to the TFSService account, the Best Practices Analyzer (BPA) tool will flag it as a failure. This violates one of our standard recommendations from the Health perspective of TFS.

As I said there is no official documentation that distinguishes from vanilla usage to TFS-setup/configuration. You should consider the above and the following set of operations, as falling somewhere between the pure setup stage and vanilla usage, and most of these will fail when you revoke DBO from TFSService. So I strongly suggest/recommend NOT revoking the DBO privilege from the TFSService account.

  1. Run the BPA tool periodically
  2. Using Command Line tools
    • TFS Power Tools ( TFS Admins frequently use this)
    • TFS Config tool (to do routine repairs/reconfig/Unconfig and more importantly to change accounts and passwords)
    • Project Collection creations (can be by-passed, as stated above)
    • TFSSecurity tool
    • TFSLabConfig

The above set of commands do certain operation on the database tables of TFS - as regards the specific set of Database changes these would do, they would collectively end up doing almost all of the operations listed in the “Default” set which I stated above.

Recommendation:

My suggestion/recommendation would be to allow the Defaults, as in my experience that is the one that is least likely to cause pain later down the line in the course of running TFS infrastructure. You can always disable the TFSSetup account (or the one you used to run the TFS Setup) after the initial install and configuration of TFS. Whenever you need to do a servicing operation you can re-enable the TFSSetup account, do your servicing operation (like a hotfix or patch installation) and then once again disable the TFSSetup account.

Technorati Tags: TFS 2010 DBO privilege revoke