Re: Database Auditing and connection strings

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Paul Clement (UseAdddressAtEndofMessage_at_swspectrum.com)
Date: 12/23/04 

Date: Thu, 23 Dec 2004 07:51:19 -0600

On 22 Dec 2004 09:55:58 -0800, GCeaser@aol.com wrote:

¤ All,
¤
¤ We have a need to be able to perform auditing on the database side
¤ of our application. As part of the requirement we must know the userid
¤ of the person performing the database modification.
¤
¤ All of the approaches that have been implemented at our organization so
¤ far, result in connection pooling not being taken advantage of. They
¤ are: 1) Using a standard ID and password in SQL Server but providing
¤ the userid in the app= connection string parameter 2) Using windows
¤ integrated authentication.
¤
¤ The goal is that we want the userid performing the transaction
¤ regardless of whether or not the modification is performed via the
¤ application of a direct database connection through some other tool.
¤ This indicates the need to use a trigger for the auditing.
¤
¤ One of the ideas I had was if the Stored Procedure that the application
¤ calls could set some SQL Server variable to the value of the userid,
¤ then the userid could be passed into the stored procedure as a
¤ parameter and each stored procedure would simply sent the userid
¤ variable and the trigger would check that variable first. If the
¤ variable was not populated, it would use the normal user id variable in
¤ SQL Server.
¤
¤ Any ideas or suggestions on this? (Note: We are using SQL Server
¤ 2000)

It might help us to understand your application configuration a bit better, such as the type of
application (ASP.NET? VB/C#.NET?). This might at least give us an idea as to why connection pooling
does not appear to be working for you.

Since the database can be accessed through different clients it would appear that you would likely
need to implement integrated authentication if you want to identify the user (via a trigger) who is
making the change.

Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

Relevant Pages

  • Re: Login Tracking Table
    ... If you are planning to upsize to SQL server, I assume this is going to be a ... > I am designing an Access database that will eventually be moved to a SQL ... > table and have created a login tracking table for this purpose. ... > The primary key for the users table is called userid. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Login Tracking Table
    ... If you are planning to upsize to SQL server, I assume this is going to be a ... > I am designing an Access database that will eventually be moved to a SQL ... > table and have created a login tracking table for this purpose. ... > The primary key for the users table is called userid. ...
    (microsoft.public.access.gettingstarted)
  • Re: Get .NET user ID from SQL Server trigger
    ... userid by spid, or a conntection setting. ... Principal in the trigger (the trigger stores audit information and ... I'm using the same SQL Server user/pass on every connection to ... facilitate connection pooling, so it's not available that way. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: High Throughput Database
    ... I think 3 tables will provide the same performance as one set per user as long as UserID is the the high-order column of each index. ... I was considering even making a new database per user. ... Perhaps go a different route and learn about load balancing, and how the sql server pooling will work. ...
    (microsoft.public.sqlserver.programming)
  • Hard Code Native Error code in program?
    ... On sql server 2000 ... When I try to establish a connection to the database ... if the UserID does not exist or if the UserID does exist ... NativeError - 18456 The UserID does not exist ...
    (microsoft.public.sqlserver.connect)