Database Auditing and connection strings

[GCeaser]

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)

thanks
George

 

[Patrice]

IMO you'll have to transmit the ID each time you call a SP. Than getting at
this ID by using a trigger (i.e. you'll need a field in each table to
receive this value just so that the trigger is able to capture its value).
If the trigger see no value then it could capture the login using the TSQL
functions.

Needs probably a bit more thinking - time to lunch here ;-). You could try
also a SQL group.



Patrice

 

[Paul Clement]

On 22 Dec 2004 09:55:58 -0800, (e-mail address removed) 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[GCeaser]

All,

I have found what I believe is a pretty effective solution to this
problem. With SQL Server 2000, Microsoft has implemented a
CONTEXT_INFO field on sysprocesses system table. The value of this
column can be set and read via provided system functions. Search for
CONEXT_INFO in the following link for more details.

http://msdn.microsoft.com/msdnmag/issues/0800/sql2000/default.aspx

So in the big picture - based on how the database is connected to -
auditing would work as follows:

1. There would be triggers on all the tables requiring auditing to
handle any Insert, Update or Delete to the table.

2. When an application connects to the database using a generic
connection string (in order to allow multiple users to reuse the same
connection in the connection pool), the SET function for the
CONTEXT_INFO should be called passing in the user id of the person
performing the transaction.

3. When the trigger fires on a table being audited, indicating an
Insert, Update or Delete have been performed, an IIF construct should
be used get the user id. This IIF construct would first look for the
UserId in the Context_Info field, if the context field does not contain
the user id (IE it is null), login name or one of the other fields in
the sysprocesses table can be used to pull the user id. (The
Context_Info would be null if someone logged directly into the database
and updated the data through a tool such as Visual Studio etc, or if
windows integrated authentication into SQL Server was used from the
individual desk tops in an AD environment.)

I hope this information helps others out there looking for a solution
for this problem.
Thanks
George

 

[Steve Troxell]

I have found what I believe is a pretty
effective solution to this problem.
With SQL Server 2000, Microsoft has
implemented a CONTEXT_INFO field on
sysprocesses system table.

This is exactly what our OmniAudit product does to audit proprietary
usernames on SQL Server databases.

Steve Troxell
http://www.krell-software.com