0

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.

1
  • You mention 'new' and 'old' machines, with the same names. So when upgrading to SQL2016 are you in fact replacing the whole server with a new (or completely wiped/reinstalled including the OS) machine? Feb 24 '20 at 17:02
0

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 name column 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 name is the same, but the sid was regenerated when the OS was upgraded. One way to test that would be to create login ... from windows for the computer account on a different, test SQL Server instance, and compare the sid column values for old and new logins.

HTH

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.