question

aba avatar image
aba asked

Windows authentication + local users: Bug or feature?

I have a customer who configured two servers as follows:

  1. DB-Server: SQL Server 2016 installed, local user named "test", who is a member of the sysadmin server role and a member of the local Windows group "Administrators"
  2. App-Server: SSMS 2016 installed, local user, also named "test" with the same password as the user on the DB-server

Both servers reside in the same domain. User "test" logs on locally on the App-Server, starts SSMS and connects to the SQL Server on the DB-server using Windows authentication. This works (?) Why? Is this a bug or a feature? When one of the passwords are changed, it doesn't work anymore. When querying SUSER_SNAME(), the local windows user on the DB-server is returned.
If someone had asked me before if this would work, I would have lost every bet.
On the DB-server, SSIS is also installed. When trying to connect to SSIS from the App-server this does not work (??).
I would like the customer to use domain users when using Windows authentication but for some (non-technical) reason this cannot be realized.

Any help much appreciated that clears up the described behavior!

replicationsecurityauthentication
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

This is a feature - often called mirrored local accounts - where local accounts from 2 different machines are created with the exact same name and password. This is often used in the setup of log shipping or mirroring in domain-less environments, or non-trusted domains.

Not much documentation about these, but they are mentioned here: https://docs.microsoft.com/en-us/previous-versions/msp-n-p/ff647396(v=pandp.10)#mirrored-accounts

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.