Migrations from Windows Server 2008/R2 must be in full swing as there seems to be quite the buzz that SQL Server uses the sIDHistory attribute in Active Directory and they can migrate away with naught another thought.
To set the background for this, Active Directory administrators have had an attribute that some applications have been programmed to look into and potentially use. One of the common uses was for Exchange and Active Directory when paired with Windows to migrate accounts across domains.
SQL Server, however, does not look at the sIDHistory attribute for the token, and thus attempting to migrate accounts and use this facility will not work. You’ll still receive the 18456 login failed generic error on the front end and you’ll see that there is no matching account or SID on the back end.
If you’re troubleshooting login failed issues with an account where the error is that no login exists or that you’re attempting to add a login where an error occurs that the account cannot be found, though other accounts in the same OU can be found then it may make sense to take a look at the SID and ensure that the forward and reverse lookup are working correctly. This can be completed through some common API calls LookupAccountName and LookupAccountSid but note that they will query the global catalog and thus if you’re not having that enabled, the lookup will fail and could be why your SQL Server instance is having issues.
Changing the name in Active Directory does not change the name in SQL Server, nor does SQL Server actively go out and look for updates to Windows Authenticated logins. Since the logins are tied back to Active Directory via the SID, the name or other attributes of the account can change so long as the SID does not change. This is a common issue when an account is accidentally deleted or removed from active directory and a new one created but having a new SID. this can easily be checked via the sys.server_principals catalog view.