Retrieving the login name in SQL Server via an Asp.NET application using Windows Authentication

Here is the scenario. The database server DBServer (SQL Server 2008) is on box 1 and the web server running an ASP.NET application is on box 2 (IIS 7.5).

The ASP.NET is using windows authentication so that all domain users are able to log in to the application using their windows credentials.

I am trying to get the name of the user who logged on the ASP.NET in DBServer. There is a trigger on each of DBServer's tables to audit the user who updates or inserts data, and currently that user name is retrieve by this:

select SYSTEM_USER

However, this statement always returns the name of the DBServer rat开发者_运维知识库her than the logged on user.

I have also tried several other possible solutions:

1. Enable the Impersonate. However, "select SYSTEM_USER" always returns impersonated user's name.
2. Run the ASP.NET application as a specific identity in application pool. Still, "select SYSTEM_USER" returns the identity's name.

It seems to me there is no way to get the logged on user name for this situation. Any idea?

Thanks in advance!

---

As a workaround, perhaps you could pass the Windows username into your stored procedures (assuming you are controlling data access via stored procedures). Or you could pass it in via the connection string using a "thwartable" property like Application Name... when a user logs into the app, have their session build & use a connection string dedicated to them, e.g.

...;Initial Catalog=dbname;Application Name=<inject login name here>;...

Now you can pick this data up by session_id from sys.dm_exec_sessions.program_name.

As a caveat, this means you can no longer identify the application through this property. If you want to continue doing this, you could decide to stuff both the app name and the login name into this property and split it out when doing the auditing. This way you can still have traces etc. identify the web app by using like instead of =.

(Note that this column is limited to 128 characters.)

I am sure there is a way to set IIS to pass your credentials onto SQL Server, I just haven't done it. You may want to look into this article.