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.
We actually ran into that problem when our IT team migrated to a new domain. We asked if the SID would change and were told there would be no SID changes. And by that they MEANT that the old SID would exist in the SID history. SQL was none to happy with it.
Long and short of it, did up a fun little dynamic SQL script that looked for logins using the old domain and created new ones with the same permissions then remapped all of the users over to the new logins. Thankfully in my case, the domain name changed so the logins were easy to recreate. We did not remove the old ones at first so we could catch anything the script missed. Or if someone said “I used to be able to delete from this table but it fails now”, we could validate that they were correct. The script ran successfully and nobody complained so I call that a good day.
Exactly, some products work without issue with SID history but most do not. I’m glad it wasn’t too rough for you!