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.

Improve this question
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? Commented Feb 24, 2020 at 17:02

1 Answer 1

Reset to default
0

SQL Server uses SIDs (Security IDs) behind the scenes https://learn.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

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.