Install TFS 2005 SP1 with SQL Service running under LocalSystem account
One may encounter this problem when applying Team Foundation Server 2005 Service Pack 1, in a deployment where the SQL service runs under LocalSystem account instead of a domain account.
Symptom:
=====================
l The TFS2005 SP1 setup program disappears mystically and quietly. It does not raise any error message.
l After the setup program disappears, the SP1 is not installed. The clients cannot connect to the TFS server.
l The installation log resembles this:
08/29/07 17:06:22 DDSet_Status: Commandline: "C:\Program Files\Microsoft Visual Studio 2005 Team Foundation Server\TF Setup\TFSQuiesce.exe" /n:"Quiesce ATDT"
……
Blocking service account from accessing database TFSActivityLogging
Could not obtain information about Windows NT group/user 'KF\tfsservice', error code 0x5.
……
Workflow 'Quiesce ATDT' failed! ExitCode = 9000.
……
Action ended 17:06:50: InstallFinalize. Return value 3.
----------------------------------------------------
--- Failure on trying to access TFSActivityLogging database
Existing Workaround:
====================
KB948778 https://support.microsoft.com/kb/948778 gives a workaround to this problem that one can change the SQL service to run under a domain administrative account.
Yet people may want least use of such sensitive credentials—sometime this is prohibited by enterprise security policies. Well, stick to LocalSystem account for SQL service, and do some additional check/reconfiguration, we can still apply TFS2005 SP1 successfully. This is what we did in a recent case.
Root cause:
====================
Knowing TFSQuiesce.exe failed when operating the databases, we captured SQL Server profiler log (what is it?). It showed that the break happened when executing this SQL Statement against TFSWorkItemTracking database:
alter login [domain\TFS_Service_Account] disable
Error: Could not obtain information about Windows NT group/user “domain\account”, error code 0x5 (0x5 means Access Denied--author)
Joint effort from SQL Server, AD and TFS support team digged out the entire chain:
1. “alter login” statement is special. When executing this statement SQL server uses its own service account which is LocalSystem.
2. In underlying process of “alter login” statement, SQL Server queries domain group membership data from the Domain Controller. DC regards this query as coming from the DT box machine account.
3. In a Windows 2003 Active Directory, the default security policy is that all authenticated users (including machine accounts) belong to group “pre-windows 2000 compatible access” hence are allowed to read all AD object info. However in the customer’s domain, this was changed due to security considerations. The DT machine account was no longer a member of that group, and had no permission to query the group membership data.
Solution:
==============
Run these command on the domain controller to restore the default AD group membership:
net localgroup "pre-windows 2000 compatible access" "nt authority\authenticated users” /add
net localgroup “Pre-Windows 2000 Compatible Access” Domain\DTBoxName$ /Add
After applying the solution we were able to install TFS2005 SP1 with SQL service running under LocalSystem account.
So TFS 2005 SP1 does not really need the SQL service to run with a domain administrative account. As long as that account can read the domain object data it would be ok.