c# - Attach a user's identity to a SQL Server Connection -

We have an application that uses SQL Server login (AD No) to authenticate the database. This is a single, normal account, so if we have 100 users logged on, then the same username is associated with all connections created by them.

This is a challenge for some of the auditing tools that we want to use to monitor inappropriate or misleading behavior on the server (they can not resolve the user).

Is there a better way to manage this connection? These are our goals:

We want to authorize the application to access the SQL Server, not the user (so we do not have access to the user, or to be able to fire crystal reports and do not mess with it DB want, access should be through our code). For this reason, we have avoided using integrated security.

We want to associate an active process on a SQL server with the user who started it

We should be able to work with ORM (firmly stored on the CRP) Procs, so we can not specify only executed permission for AD accounts)

The only option I know about which I know about:

• Some types of connection Attach user-specific metadata
• Use SQL Server login per user
• Undo any user identity information on our connection

  The general approach is to attach a user name for all records ... either the FK audit / history As a column through the table or on the table. Your application is a conversation with the database - your users do not have "pass-through" which I know.

  Another option is to take the user's username when they use your application, and in place a connection string in the user's session / which uses the user's credentials integrated with the user name ... then the user uses their specific stored connection. The string should be used during the application's use, but you have to handle SQLExceptions for the permission in the code and so on ... which I think you have to handle.