Moderator: “Login number one, what is your identity?”

Number one: “I am known as jsmith”

Moderator: “Login number two, what is your identity?”

Number two: “I am known as jsmith”

… and after all the contestants would make the same claim, the moderator on “To Tell the Truth” would then give some background on the contestants, and iterate “one of these login’s is the real jsmith”.

Evidently, not in SQL Server 2005 Express.  It just answers every contestant with, “Of course you are.  Come on in.”

This behavior occurs under the following set of circumstances.

  • On the SQL server and the client machine connecting to it, you have a local NT account which has the same name on both machines.
  • Both accounts use the same password (so it’s not really any serious security threat in most cases).
  • Your connection is using integrated security.

Apparently, SQL Server will compare the login as “.\jsmith” and not as “CLIENT\jsmith”.  Unexpected behavior, but it has proven useful if it was intended.  I’m only seeing the behavior in local NT access: I don’t see Domain NT access having this problem.

I haven’t seen any hits when doing a search on Google about this.  I’m not sure if this is by design or an oversight. In my case, I’m am number one and number two.. so it has its advantages.

Follow-up: I found out (thanks Don Cross) that this is the behavior of Windows Workgroup mode, and is expected.  It’s a neat little mode that I didn’t know about, and very useful for some things that I want to do.

No Comments
SQL Server, Tips and Tricks