Unanswered: ODBC Trusted_Connection call to SQLGetInfo returns DBO as user name, not NT User
I am connecting to SQL Server 2000 with a trusted connection, and that is working fine. I then am issuing a SQLGetInfo call to find out the SQL_USER_NAME, which is always returning dbo instead of (my) Windows NT login name, which I expect to see. THe authentication is (apparently) confirmed by the SQL Server session monitor where it shows the right Windows NT user name logged in, over ODBC. With a standard SQL Login, untrusted, I definitely get the right user name back, not dbo.
Login Type / user id /returned value from SQLGetInfo
Windows Authentication / Windows login / dbo
SQL Authentication / Windows login (UID) / UID
The problem is that I am trying to confirm that the userid entered in a dialog or passed on the command line to my application matches the actual connected user name inside SQL Server....
Is there some pathology in Trusted_Connections that masks the Windows/NT login name and always returns UID 'dbo' from a call to SQLGetInfo? Is there a MSS (public) stored procedure that can robustly give back the logged in user name so I can bypass the SQLGetInfo call?
Solution, use SYSTEM_USER call to get Win NT login and parse it
I think I have finally wrestled this beast to the ground. Here's the story, in case anybody is interested:
1) When making an NTLM trusted connection to MSS, logging in using my NT login name e.g. as amarshall, the system was always saying the connected user is dbo, no matter what. This can be verified by using a SQL tool like SQL Query Analyzer, connecting over a trusted connection to MSS, and entering SELECT USER; or SELECT USER_NAME() or any variants. These will always return dbo, which is not the real 'user' of interest, but the schema owner. What we want is to confirm 'amarshall' is a (legitimate) user, meaning, SQL Server knows who this is..
2) So, use the SYSTEM_USER call, and the result is AMARSHALL-CAM\amarshall (Host\user).
3) Then, just perform substring match to see if what the user has typed into the login dialog (amarshall) matches what is in the call result. If so, good user, otherwise, reject (program decides).
4) I do NOT strip off the host name or anything - just substring match result of SYSTEM_USER call to logins userid.
5) Database connectstring for Powerbuilder must include SECURE=1. For ADO/VB/.NET, has to have something like TRUSTED_CONNECTION=yes (PB does this for me)
6) Have not tested this against the SQL Server Desktop Edition. Should be identical, but who knows?
7) If anybody has a superior resolution, or security concerns, please advise, but this suffices for now.