TFSWITDDLADMIN – consequences of removing this user from the collection databases - TF400703 error

  • Article

 

Introduction

[This is not an official MS Support statement, but a caveat (cautionary advise) to anyone thinking of directly working with the TFS Databases]

This applies to TFS 2013. It may apply to other versions of TFS also, but I have not verified it. In some environments DBAs operate a tightly controlled Database support environment (not blaming them here) and may remove user accounts in SQL Server, which they think does not deserve to exist. In some cases this can lead to weird results and maybe severe effects. In this article we are discussing the consequences of removing an undocumented user “TFSWITDDLADMIN” from a TFS Database server.

Details of TFSWITDDLADMIN

The TFSWITDDLADMIN user is a user that is created by TFS, when you create a new collection. See below on this User Account in SQL Server Management Studio underneath a collection database.

clip_image002

This is almost sort of a hidden account, which does not get mentioned in any of the MSDN documentation of TFS. However even though it is undocumented, it is important to the operation of TFS, and this account should not be deleted. The Workitem tracking Service relies on this account to exist on the Collection Databases for the service to function properly. Otherwise you will see errors such as the following and the Workitem related features for the collection will not function.

ERRORs due to deleting this account:

The following are error messages one will see in the Event Logs for the server on the Application Tier.

  1. Detailed Message: TF400703: Unable to initialize the specified service Microsoft.TeamFoundation.WorkItemTracking.Server.RegistrationInfo.
  2. Exception Message: Specified cast is not valid. (type InvalidCastException)

Exception Stack Trace: at Microsoft.TeamFoundation.WorkItemTracking.Server.RegistrationInfo.

DetectorFactory.OnPremDatabasePropertyDetector.GetDatabaseProperties(TeamFoundationRequestContext systemRequestContext)

at Microsoft.TeamFoundation.WorkItemTracking.Server.RegistrationInfo.ServiceStart(TeamFoundationRequestContext systemRequestContext)

at Microsoft.TeamFoundation.Framework.Server.ServiceProvider.GetServiceImpl[T](TeamFoundationRequestContext requestContext)

What does this account do?

The TFSWITDDLADMIN account is used as the security context for about 8 Stored Procedures and this account is given DBO privileges to execute them. Besides it is quite likely that it is not as simple as restoring the user itself and granting it the privileges to execute those stored Procedures. There could be other places in which this account is required to exist, and the underlying operations requires it. The bottom line (in a complex product like TFS) - NEVER EVER delete any TFS created artifact such as a security role in SQL, without properly verifying with MS and understanding the implications. These accounts exist for a reason and they are NOT left in there without any purpose.

See below for this account usage on one such Stored Procedure, namely ExecDDLCommands:-

clip_image004

The following are the sets of Stored Procedures that need this account to be executed with the correct set of privileges.

clip_image005

Additionally, there are also other SQL calls where this account is need for authorization. Basically this user with DBO privilege is required for the Workitem tracking system to function correctly. If you removed this account or disabled for some reason, your Workitem tracking feature will not work and will be rendered completely non-usable by your end users. And WIT being a major feature and critical to operating TFS in production environments, it basically renders the rest of the TFS not as capable as it would otherwise be with this feature working.

Solution/Suggestion to TFS Admins and DBAs

Never ever touch the TFS databases, even though you might be thinking you know about them. TFS uses an extremely complex schema with multiple tiers of security and permission model implemented inside it, and the integrity of the database is extremely essential to the healthy operation of the server. Do not delete, disable any such accounts thereby putting your TFS in a non-supported state. If you feel the urge to do it, consult with MS Support first, and only after getting an answer from them (whether it safe to do or not) carry out such operations. Otherwise you might have inadvertently put your TFS in a non-serviceable state, and you will end up with a lot of pain and spending countless hours with MS support to fix the broken TFS environment.

LiveJournal Tags: TFSWITDDLADMIN TF400703 TFS 2013