Re-Using Same TFS Application Tier Name breaks Network Service account access to SQL.

  • Article

TFS Disaster Recovery is always a frustrating and harried time, particularly when an unforeseen problem arises.  We hope that the information here will help you through one of these issues.  

We found an issue after rebuilding a failed Application Tier while keeping the same computer name. We received a Logon Failure on SQL Server  for the Network Service account associated with the computer name of the Application Tier.  An error occurs like “Login failed for user 'Domain Name\Application Tier Machine Name$”  ($ sign indicates that its Network Account for Application Tier)  A typical scenario would be Reports failing with this error as Reports or Default App Pool on the Application Tier would be using the Network Service Account. 

When you look at the account on the Data Tier, everything looks OK as the account would appear to have the right name.  However, the rebuilt Application Tier would have a new SID despite having the same Network Service account name.

To resolve this error we had to delete the old account in SQL’s master, msdb & tempdb System databases, from the application specific databases( i.e.: ReportServer, ReportServerTempDB) and from logins, and recreate the correct permissions.

The correct permissions to recreate depend on what services were running as the Network Service account on the Application Tier. 

For Reporting Services this can be done by stepping through the Reporting Services Configuration Tool. 

For TFS, use the TfsAdminUtil changeaccount option:

TfsAdminUtil changeaccount “NT Authority\Network Service”  “NT Authority\Network Service”

For SharePoint depending on the application pool identity:

#1. If the app pool identities for the Central Admin Site & the Web Application(typically default web site) are both running the as Network Service, then the “new” machine account needs to be dbowner on the WSS_Config, WSS_Content and WSS_AdminContent databases.

#2. If the app pool identity for only the Central Admin Site is running as Network Service, the login needs to a dbowner on only the WSS_Config & WSS_AdminContent databases.

#3. If the app pool identity for only the Web Application is running  as Network Service, the login needs to a dbowner on only the WSS_Content database.

 

Thank you to Romit Gulati and Lakhminder Singh.