TFS 2017数据库恢复失败：域用户登录错误致恢复停滞

Hey Andy, let's work through this TFS disaster recovery login issue you're hitting. That Login Failed for user 'domain\machinename...' error is super common when restoring TFS databases to a new (or same) SQL instance, and it almost always ties back to missing SQL logins, orphaned database users, or permission gaps. Here's how to fix it step by step:

1. Verify the Machine Account is a Valid SQL Login #

First off, check if the domain\machinename account exists as a login in your SQL Server 2016 instance:

• Open SQL Server Management Studio (SSMS) and connect to your target SQL instance.
• Expand Security > Logins and look for the domain\machinename account. If it's missing:
  • Right-click Logins > New Login
  • Under the General tab, enter domain\machinename in the Login name field, select Windows authentication
  • Jump to the Server Roles tab and ensure public is checked (you can add sysadmin temporarily for recovery if needed—we can lock it down later)
  • Click OK to create the login.

2. Fix Orphaned Database Users in Stuck TFS Databases #

Even if the login exists, restored databases often have "orphaned" users—this means the database user's security identifier (SID) doesn't match the SQL login's SID. For each stuck database (like TFS_Warehouse):

• In SSMS, expand the problematic database > Security > Users
• Look for a user mapped to domain\machinename (it might have a red X next to it, flagging the orphaned status)
• Run this query directly against the database to remap the user:

  USE [TFS_Warehouse] -- Replace with your stuck TFS database name
  EXEC sp_change_users_login 'Auto_Fix', 'domain\machinename'
  

This command will either remap the existing orphaned user to the SQL login or create the user if it's missing and map it automatically.

3. Ensure TFS Service Accounts Have Correct Permissions #

Don't overlook the core TFS service account (usually something like domain\TFSService)—it needs full access to all TFS databases for recovery to succeed:

• For each TFS database, add the TFS service account as a user if it's missing.
• Assign the db_owner role to the account (this is required for recovery operations; you can adjust to least-privilege permissions post-recovery if needed).
• For TFS_Warehouse, additionally make sure the account has db_datareader and db_datawriter roles, especially if you're using TFS reporting features.

4. Double-Check SQL Server Service Account Permissions #

Since the error references the machine account, confirm the account running the SQL Server service has permission to authenticate domain accounts:

• If SQL Server is running under Local System, it should have built-in access to AD, so this is likely fine.
• If it's running under a domain service account, verify that account has permissions to read Active Directory user and computer objects.

5. Retry the TFS Recovery Process #

Once you've addressed the logins, orphaned users, and permissions, head back to the TFS Administration Console and retry the recovery step for the stuck databases. The login error should now be resolved because SQL Server can properly authenticate the machine account, and the account has the necessary access to the TFS databases.

A quick pro tip: When doing TFS disaster recovery, always make sure all service accounts (TFS, SQL, Reporting Services) have consistent permissions across your old and new environments—machine accounts are often used by TFS background services like the Job Agent, so their SQL access is easy to overlook.

内容的提问来源于stack exchange，提问作者AndyW