How to get SQL Profiler record the user which login to your web application but the SQL username and password is specify in the web.config file?

Question

I have a web application that will retrieve data from a database. I have specify in the web application web.config file to have the username sa and password password.

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=sa;Password=password;

However, I notice that SQL Profiler will display the LoginName column as sa and not the actual user if a actual user log into the web application.

Example: Suppose a user WATSON\User1 come to the web application. The SQL Profiler will not record WATSON\User1 under the LoginName column. Then I will not know who have done something.

So, what should I do in order to have the SQL Profiler to record these user who login to the application from other domain?

Answer 1

As a good practice, do not use sa as the log on from you application to the database. Create another user in the database and use that user to interact with the database. That user should have restricted permissions to only be able to access the database objects it needs. I also hope that the password you use is not password. These steps will make your database a little bit more secure from attack/accidental/malicious activity issues.

Profiler is showing the connections to the database based on the user account connecting to the database - that is the user account in the connection string. You could create database accounts for each user, and alter the connection string to use that user account to connect with. This may be a workable solution if you do not have many users. [It is also useful to do use separate users in conjunction with database permissions as a security/data integrity stratergy (ie user 'boss' has read/write privileges, user 'anonymous' has only read access to the data).]

If you can set up a cross-domain trust that would allow you to use Windows Authentication. The web app would then need to be configured to impersonate the user that's using it otherwise the web service account will be displayed.

Another option if you are using stored procedures is to add the user ID as an argument to the call to the stored procedures which should show up in the trace.

If you have a data access layer, you could add a routine that logs the user, action and time stamp of each database action. The timestamps should correlate with the profile if you still need to profile information.