How do I capture the Windows Account associated with a SQL Server Login?
Greetings, We have an issue with our Web application's SQL Server login and Windows account auditing. A user logs on with somedomain\someaccount (Windows account) to run the application and then a SQL Server login is used to access the database. I need to return the Windows account that is using the SQL Server login. Ideally, I want to leverage SQL Server Audit to capture database insert/delete/update processes by Windows account and SQL Server login. Is there any way to capture what Windows account is using a SQL Server login? I’d like to run a nightly SQL Server Agent job to load an audit table based on what is captured using SQL Server Audit and relate this data to the two accounts. Thanks!
It sounds like you need auditing at the web application level. Since the connection to SQL Server is using a SQL Server login, that's what it knows. I briefly considered whether or not the host machine would help, but it won't because this is a web application. SQL Server will see everything as the same login coming from same web server(s).