Introduction
More often than not (at least in places I’ve worked at), the job of installing, configuring and subsequent administration of Team Foundation Server (TFS) is performed by different individuals to those administering enterprise applications such as Windows Server, SQL Server, SharePoint etc. This is because TFS, unlike other enterprise applications, often has to be administered from both the server side (i.e. TFS Administration Console) as well as the client side (i.e. using Team Explorer in Visual Studio) – it is thus not surprising that a TFS administrators may once had been a developer. The benefit of having a TFS administrator with a software development experience is that it may be easy to get developer-buy-in into the tool. However, the disadvantage to this is that developers usually get accustomed to doing things in a certain way – like connecting to SQL Server using (local) convenience names.
In this article we will take you through some of the lessons to look out for when configuring TFS as ignoring them would prove detrimental to the configuration of the tool. We also compare the configuration of SharePoint Server farm against that of TFS and illustrate why you have to pay a closer attention to TFS. We later conclude by recommending that the installation and configuration of TFS has to be a joint exercise between administrators of various applications and systems.
Lesson #1: Refrain from using convenience names as SQL Server identifier name
Although, you can get away with using convenience names (i.e. a dot, (local), or locahost) as SQL Server identifier name during the configuration of a SharePoint server farm as shown in Figures 2 & 3, such a practice is not allowed during configuration of TFS (as shown in Figure 1).
The reason for this is that a successful configuration of TFS leads to the creation of databases – if you hosting the databases in an environment that has other instances, you can make a mistake of creating databases in the wrong instance. Although you can later move databases around, I reckon it’s rather better to do things right in the first place and that’s include correctly supplying the full names of the instance in which databases should be created.
Lesson #2: SQL Server SID duplicate
In all fairness, the errors that lead to Lesson #2 are usually not caused by a SQL Server administrators or that you may have former developer masquerading as TFS administrators. Instead such errors are as a result to machine name changes possibly at the hands of Windows Server administrators. For instance, a change in the domain name in which SQL Server is installed (in our example, the domain name was renamed from computer generated WIN-QK43RU4ULNS to SELECTSIFISOLAB) often doesn’t break the functioning of SQL Server (and its related services) but the change does lead to a SQL Server login security identifier (SID) duplicate error that is depicted in Figure 4.
Although the highlighted error message shown in Figure 4 refers to “SQL Server login”, this error really relates to windows-level principals that are based off windows domain or local logins.
Anyway, what happens is that generally every principal is assigned a uniqueidentifier generated-value called the security identifier (SID). There is often a one to one relationship between a SID and a principal but in some circumstances – a single SID may *seem to* be shared amongst more than one principal. Notice the emphasis on “seem to”? This is because, although – the changes may have been made to the workgroup or domain, technically – the linking of SID principal hasn’t changed i.e. although I have logged into SQL Server Management Studio using SELECTSIFISOLAB\Administrator as shown in Figure 6, SQL Server still has the administrator principal linked to the WIN-QK43RU4ULNS domain – thus, the source our SID conflict.
This is in contrast to the configuration of SharePoint whereby we are still able to proceed with our configuration in spite of SID conflicts. For instance, having specified the principal to access the database server as SELECTSIFISOLAB\Administrator (as shown in Figure 7), following the configuration – the principal mapped to SharePoint databases is still WIN-QK43RU4ULNS\Administrator (shown in Figure 8).
There a several ways that we can address SID conflict errors – all the solutions require that you as a TFS administrator, step out of your “comfort zone” and request assistance from SQL Server and/or Windows administrators (after all, they are the cause of your misfortunes, right? ).
SQL Server side
From the SQL Server side, you can replace the old principal with the newer one using one of the following methods:
Rename using SQLCMD
Follow the suggestion provided in the error message (in Figure 4) and use SQLCMD to alter the login as shown in Figure 9.
Figure 9: SQLCMD command Rename using SSMS
In management studio, you can run the below script:
Figure 10: TSQL Login script-change Remove and Add New Login
Another alternative to renaming conflicting principals would be to delete the old principal (WIN-QK43RU4ULNS\Administrator) and add a new principal (SELECTSIFISOLAB\Administrator). However, in my experience, a DELETE operation should always be the last resort. Thus, I recommend that if you were to fix the SID conflict from SQL Server side, rather try renaming conflicting principals.
Windows Server side
The rate at which you will encounter this error is often rare because it’s not very often that organisational domain names or server machine names get renamed – but it happens, otherwise we wouldn’t have had this issue since early versions of TFS. Some of the changes to machine names may be unavoidable (i.e. server migration, company buy-outs, corrupt domain controllers etc.) but as an administrator, you would surely appreciate being informed in advance of any planned changes. So speak to Windows Server administrators about this – also get the SQL Server administrator involved as server changes will ultimately affect SQL Server too.
Conclusion
Like so many installations of enterprise applications, often you run into installation/configuration interruptions because of failed validations that you were unaware of – a TFS configuration is no different. Thus, you should keep the channels of communications between server administrators as you possibly can.
See more
Consider these free tools for SQL Server that improve database developer productivity.
References
Principals (Database Engine)
Configure Team Foundation Server using the advanced configuration
Overview of SharePoint 2013 installation and configuration
Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu