Why does error message include my LAN information, when logging in with SQL authentication?

Question

Hi Everybody,

I'm working on an application that uses SQL Server security.  The users log on to the production server with a User ID and Password, pretend it's UID="u" and Password="p".  I do all my work on a test server without issue.  Now I am trying to test something on the production server.  When I run the application against the production server, and enter "u" and "p", I get a "Login failed for user 'my domain\my windows username" error back from the server.  (The question is *not* how to fix the error.)

The question is this, from my boss, which I couldn't answer:

• If I'm using SQL Server authentication, and I provide a SQL username and password, why does the error message include my LAN information? 

My boss thinks that if I use SQL authentication, the server should not know who I really am, but I think that's a pretty big security hole.  I mean, I know the DBAs get alerts that tell them when a login fails, and the alert contains the LAN ID of the individual user, even if they tried to login from an application that uses a LAN group.  That makes sense to me as a good thing.

Can anyone point me to some documentation I can use to explain why this makes sense?  That the server will always know what individual user is trying to access it, even if they are trying to access it using a SQL login or a LAN group login?

Thank you!

Answer 1

"Login failed for user 'my domain\my windows username'"

Looks like your application is passing your domain account while trying to connect to SQL Server. If possible check the connection string used in the application to connect to SQL.

Answer 2

So how does the full connection string look like? From the error message it sounds like you are trying to log in with Windows authentication. Which would be the case if the connection string includes "Integrated Security=SSPI".

In general, if you log in with SQL Server authentication, SQL Server cannot know your Windows account. For one thing: you may not have one. You could be logging in from a Unix box.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

Please, post your connection string. There's quite probably a mistyping there.

Replace any confidential information such as user and passwords by dummy entries.

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 4

As the computer to computer connection is being made, the computers exchange information such as IP Address and your Windows login. This is used to determine if you should even be on the network asking for a connection. Once the server computer knows who is trying to connect, they pass the SQL Server login information to SQL Server. The client passes additional info such as the client program name (for instance SELECT program_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID; returns Management Studio). So SQL Server can report out the IP Address and Windows credentials of the client even if you are connecting with SQL Server authentication. Some of this extra information is just a property of the connection and can be changed (spoofed) by a skill user.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty