Connection pooling: Saving username against a @@spid

[JimLad]

Hi,

In an existing ASP/ASP.NET 1.1 app running on IIS 6, I need to RELIABLY
pass the logged in username through to the SQL Server 2000 database for
auditing purposes. The current method is hideously unreliable. The app
includes updategrams, XML Templates and ADO connections.

I don't want to use impersonation because of the direct db access that
allows.

So, at the start of each connection I pass the username to a table and
save it against the @@spid (Primary Key). The audit triggers then look
up the username for that connection when they are triggered.

At the end of the connection I remove the entry from the table.

First of all, what do you think of the solution? Secondly, can I leave
connection pooling on when using this method? I think I can. To get the
wrong name passed through you'd have to forget to set the username on
that connection AND forget to remove it on another connection.

Cheers,

James

 

[bruce barker \(sqlwork.com\)]

you will have to turn pooling off. with pooling on, every query fetchs a
connection from the pool and returns it. therefore the same connection can
be used by tw page requests, and the same page may use serveral different
connections.

for example

......
cmd.ExecuteNonQuery()
......
cmd.ExecuteNonQuery()

are may not use the same connection.if connection pooling is on.


-- bruce (sqlwork.com)

 

[David Browne]

Not quite correct. You won't get a different connection for each query.
Connection pooling gives you an idle open connection instead of opening a
new one when you call SqlConnection.Open(). Then when you close (or
dispose) the connection it is returned to the pool instead of really being
closed.

The solution will work so long as the user name is passed after each time a
connection is opened.

David