Getting the Windows login name from server

Question

Hello,

I've been stuck on this issue for several days - any help is much appreciated.

I have an SQL trigger that's calling a DLL written in C#. In this function, I require the windows login name of the person that originated the SQL command that fired the trigger. It doesn't matter whether I get this info from C# or SQL.

Unfortunately most of the normal routes have failed on me.

Built in SQL function NT_CLIENT() only works if the SQL login is using Windows Authentication.

The DLL is running on the server, and therefore Environment.UserName isn't relevant and WindowsIdentity.GetCurrent().Name returns an empty string unless the database user (thread originator) was using Windows Authentication.

I know the domain name, the ip address, and the workstation name. From this I've figured out how to get the SID in C# - is there anyway I can get the Windows login name from those bits of information using C# or is there an SQL built in function that I missed?

EDIT: Thanks for letting me know SQL was a dud for this problem. What we have is a client that logs everyone into the DB as one user. What I ended up having to do was make an unsafe assembly that calls a command line function WMIC. Yucky solution, but it got the job done. Thanks again for the advice. :)

Answer 1

are you using SQL Authentication? if yes i think it is not possible, because windows login is not associated with server login.

Answer 2

You have to pass in the user name from client code as a parameter. We do that is every call from our client code (web sites and services).

However, even then it's not available in a trigger, so I use CONTEXT_INFO.

No magic, no silver bullet I'm sorry.

Your one other option is to look at Security Account Delegation/Passthrough. Google search... there is a lot of good stuff, probably better than one specific link.

If this is not possible, and you say you can't change client code, then it can't be done. The data is simply not available: and deliberately so by the application architecture.