Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
3  
Wow, is your web app really using sa? Why? –  Aaron Bertrand Oct 4 '12 at 13:05
1  
The web app in currently in the development server. When the web app is put into the production server, the User Id will not be sa anymore. :) –  Jack Oct 5 '12 at 2:56
add comment

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.