What allows a Windows authentication username to work (flow) between 2 servers?

Question

Typical ISP setup. One server is the web server, another is the DB SQL server. There is a local administrator account, let's say XYZ, created on both machines. So when I log in remotely, I am either WebServer\XYZ or DBServer\XYZ, depending where I log in.

Now, when I login to SQL Server SSMS on DBServer using Windows Authentication, and execute "SELECT SUSER_NAME()", I get DBServer\XYZ. That makes sense since it's picking up the fact that I logged in with those credentials.

Now, move over to the WebServer. I remotely login as WebServer\XYZ. I've installed the SQL client components there. When I launch SSMS, choose the DBServer, login with Windows Authentication, and execute "SELECT SUSER_NAME()", I somehow get DBSERVER\XYZ, instead of what I would assume should be WebServer\XYZ.

Somehow, the XYZ from the WebServer becomes the XYZ from the DBServer. Why is that? How does that happen? Surely, it can't just be because the names happen to be the same?

I've heard of trusted domains, but neither machine is a Domain Controller, so I don't have access to that info. How can I tell if it's trusted or not, without the GUI tools?

The reason I ask the question is because, I'm trying to implement the same thing on my XP laptop (using Virtual PC), so I can imitate the production environment, but I'm not having any luck.

Answer 1

You XYZ accounts seem to have same passwords on both machines, and they are not a part of a domain.

WebServer sends just XYZ as a username and answers all password challenges successfuly, as the passwords do match.

DbServer, of course, thinks of you as of DbServer/XYZ, as it knows of no others.

Exactly same thing happens when you try to access one standalone machine from another one over SMB. If your usernames and password match, you succeed.

Answer 2

The NTLM challenge between machines is a little more complex @Quassnoi indicates but it is similar. The machines may well be in the same domain or trusted domains, but the accounts you are using are local machine accounts, scoped only to the local machine's security access management.

Local SAM accounts patterned as machinename\userid are non-propagatable. You'd experience a series of negotiated fallbacks when you tried to authenticate against external resources using that account as follows:

1. Pass current domain/username/password hash token - it'll fail, the account is untrusted
2. Fallback - revert passing hash of UserID + Password
3. Fallback - revert to connecting as anonymous credentials.

The fallbacks can also be disabled through configuration, it is very common for anonymous authentication to be prevented.

As @Quassnoi indicates in this instance you managed to login using the #2 fallback.

To enable account credentials to propagate, you'd need the following to be true:

1. machines would need to be members of domains with at least one-way trust between each other (they don't necessarily have to be members of the same domain).
2. use domain accounts - not local machine accounts - would look something like domainname\userid. A special case is the Network Service account which has a proxy account in the domain scenario - domainname\machinename$.

How do you tell if your machine is a member of the domain? It's pretty easy if you've got interactive login to the machines. There are a few strategies

1. interactively the System control panel will show workgroup or domain membership. (Right-click properties on Computer in the start menu)
2. at the command-line, IPCONFIG /ALL will also show the default DNS prefix which is typically the same as your domain name.

I suspect your ISP would create a domain just to make it easy to manage and monitor their machines. Whether they'd let you create domain accounts is a different question.