2

Good day all,

I am studing the following case:

Scenario: An application connects to the production database(SQL server 2008) using a generic "SA" user instead of the domain user. This is making traces\logs\organization harder, because everything is flagged as done by SA user!

NOTE: In the application the domain user/password is used, the generic account is only regarding to the database.

Questions: What would be the best pratice in this case? every user should have an account to log in the database? (sql using windows authentication) there are +- 500 users is that an issue regarding to database performance? or a generic account is indicated?

Many thanks!

  • You can use one generic sql account but store a UserName / UserID and DateModified field in any or all tables. Store the user who logged or made the transaction in this field when it is updated, in addition, a log file... – JonH Oct 31 '12 at 16:38
  • This is probably better suited for the DBA site, but if you have the option to use Windows authentication then a common approach is to put all the users in an AD group and give permissions to that group instead of to each individual user. – Pondlife Oct 31 '12 at 17:51
  • Thanks for the suggestion @JonH I agree with you in the case of some tables! if this is done in all tables would be huge log and some performace lose, nice to know that in this case a generic account would be no issue thanks for the suggestion! – Willy Krawczyk Nov 05 '12 at 19:27
  • @Pondlife Thanks for the suggestion! I will check this with the network admin... =) – Willy Krawczyk Nov 05 '12 at 19:29

1 Answers1

0

As others have mentioned, Active Directory and Windows Authentication might be more appropriate if that's an option. But if not...

If the application has a central place that creates the connection & transaction prior to update, you may be able to use SET CONTEXT_INFO to pass along the "real" application user while still using a shared SQL account for the login.

Then in your auditing triggers you can pull the information back out again using the CONTEXT_INFO() function

This is the approach used by at least one commercial auditing tool

See also similar SO questions here and here which reference context_info and a blog post Exploiting Context_Info for Fun and Audit which gives an NHibernate example.

Nitpick on something else in your question: you said it's using sa user. Maybe that was just an example, but probably the application should not have so many rights on the server. Create a user with only the rights needed for the particular database(s) that application uses. This limits the impact of any future security vulnerability (e.g. SQL Injection) in your application. And to take it one step further, you might have one connection string with a read-only user account, and then at the point where you create a transaction to update data, switch to a connection string with the read/write user account. You still get most of the benefits of connection pooling, but you limit even further the impact of any application-tier bugs.

Community
  • 1
  • 1
explunit
  • 18,967
  • 6
  • 69
  • 94
  • Hello Steve, Sorry on the delay in the reply, many tks for the points mentioned they were really useful! About the sa, the application really uses sa user in some cases, however people decided not to change this because it would lead to troubles for the develop team, unfortunately this study was postponed... many tks for the assistance. – Willy Krawczyk Nov 22 '12 at 12:42