I imagine security changed in some way for Windows 2016, but I'm unable to determine what exactly.
For context: We have a variety of remote clients that handle data processing within a (non-SQL) data source as well as ETL to SQL instances. All of those processes are managed by services that may be running as LocalSystem or an AD user. The remotes are running a mix of Windows 2008 and 2012.
What we're finding is that after the remotes migrate to Windows 2016, the services running as system accounts no longer can connect to SQL Server 2016; logging errors that a login could not be found for "DOMAIN\MACHINE$", even though the login was present beforehand and still is. The new machine takes on the name of the old one, and the old-server services' registry keys are copied to the new remote to make sure nothing gets changed inadvertently.
This has been resolved by changing the service to run as the AD user; what I currently consider ideal seems to be required. In order to best push for the process changes to use the AD user all the time I'm trying to understand what about Windows 2016 System Accounts would "trick" SQL Server into thinking there's no login.
Best Answer
SQL Server uses SIDs (Security IDs) behind the scenes https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-principals-transact-sql?view=sql-server-ver15
The
namecolumn is a label, and doesn't have to match what is in AD as long as the SIDs match. For example, I have seen a user who was able to connect to SQL Server after their AD username changed (married surname), but the old username still showed in SQL Server.I suspect that this might be the other way around, and that the
nameis the same, but thesidwas regenerated when the OS was upgraded. One way to test that would be tocreate login ... from windowsfor the computer account on a different, test SQL Server instance, and compare thesidcolumn values for old and new logins.HTH