I have a customer who configured two servers as follows:
- 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"
- 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!