469,609 Members | 1,168 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

Connection pooling: Saving username against a @@spid

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

Sep 21 '06 #1
2 2106
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)
"JimLad" <ja*********@yahoo.co.ukwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
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

Sep 21 '06 #2
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

"bruce barker (sqlwork.com)" <b_*************************@sqlwork.comwrote
in message news:OY**************@TK2MSFTNGP04.phx.gbl...
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)
"JimLad" <ja*********@yahoo.co.ukwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
>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

Sep 21 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by JWM | last post: by
18 posts views Thread by Rob Nicholson | last post: by
5 posts views Thread by Zlatko Matić | last post: by
5 posts views Thread by Terry Holland | last post: by
16 posts views Thread by crbd98 | last post: by
3 posts views Thread by fniles | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.