How can I entrer the current IIS/Active Directory user into SQL?

Question

I'm in the process of creating a simple ASP.NET Details and Grid View in two separate pages for simple record keeping. The Details View will be leveraged to enter records into the database and the Grid View will be leveraged for any updating of these records. I should note that I have only 'Windows Authentication' enabled on my IIS server.

One of the features I'm adding is audting when records are inserted or updated into the database. I'm using the CURRENT_TIMESTAMP command in SQL Server 2012 to enter when records are inserted and/or updated. However, I'm attempting to write which active directory user updated or inserted a given record. I first attempted to leverage the SYSTEM_USER command but all that writes to the database is the SQL server service acccount or dbo account which is unacceptable. I understand why the SQL Server service account is being written but what I need is to have the current Active Directory user's name be written to the 'username' field as noted below.

Here's the commands I'm using right now for the timestamp and username (this isn't the exact SQL command I'm using):

INSERT INTO <table-name> ([timestamp], [username]...) VALUES (CURRENT_TIMESTAMP, SYSTEM_USER...)
UPDATE <table-name> SET [timestamp] = CURRENT_TIMESTAMP, [username]) = SYSTEM_USER WHERE.....

What is the best way to get the current Active Directory user's name written to the 'username' field whenever they insert and/or update a given record and not the SQL Server Service account's credentials?

Thanks in advance!

Answer 1

How are you connecting to SQL Server? Are you connecting using SQL Authentication or using Windows Authentication?

If using sql auth, then there isn't a way without passing that information along with the database command (query).

If you are using Windows Authentication, are you impersonating the user in ASP.NET? If not, then it's the same problem as sql authentication.

If you are using impersonation in ASP.NET, then SYSTEM_USER should get you what you need.

If your website is using forms authentication a user store other than AD, then you'll have the same problem as sql auth.

Think of it this way: Is SQL Server aware of the user of the website? In most cases, no, unless you are using impersonation with windows auth or you are telling SQL Server the user name (i.e. passing it in with your command statement).

Answer 2

You could store the application username on the context_info variable. And afterwards retrieve it in your trigger or anywhere you need it.

You should do this before each connection to sql is made; should be easy to do, since usually you have the code for the connection in one place only.