Could CONTEXT_INFO persist across connections because of pooling?

i am issuing
SET CONTEXT_INFO @.BinarySoftwareUserIdentification
when i connect to the database so that later during my insert/update/delete
audit logging triggers i can get the user of the software that did the
modification.
Is it possible that a new connection could inherit the CONTEXT_INFO from an
old connection?
The situation is that the web-server itself is issuing all the connections,
so any connections made and dropped happen from the same machine. An
automated process on the server doesn't issue a call to SET CONTEXT_INFO
(for whatever reason). Could the pooled database connection that the process
gets have the same CONTEXT_INFO as the previous person who used that
connection?
Would SQL Server clear CONTEXT_INFO when a user using ADO closes their
connection? Or would it only get rid of it when the pool timeout finally
happens?
Put it another way, can one connection be impersonating the user who
previously owned that connection?Good question, Ian...
I never doubt the correctness of the author of this article:
http://www.sqldev.net/misc/sp_reset_connection.htm
It does say that sp_reset_connection does reset all SET options. However, CO
NTEXT_INFO could just be
a special case... I'd test it just to be certain. Note that the article spec
ifies that the reset is
performed when the connection is *re-used* (lazy...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%23KReMJceGHA.3388@.TK2MSFTNGP05.phx.gbl...
>i am issuing
> SET CONTEXT_INFO @.BinarySoftwareUserIdentification
> when i connect to the database so that later during my insert/update/delet
e audit logging triggers
> i can get the user of the software that did the modification.
> Is it possible that a new connection could inherit the CONTEXT_INFO from a
n old connection?
> The situation is that the web-server itself is issuing all the connections
, so any connections
> made and dropped happen from the same machine. An automated process on the
server doesn't issue a
> call to SET CONTEXT_INFO (for whatever reason). Could the pooled database
connection that the
> process gets have the same CONTEXT_INFO as the previous person who used th
at connection?
> Would SQL Server clear CONTEXT_INFO when a user using ADO closes their con
nection? Or would it
> only get rid of it when the pool timeout finally happens?
>
> Put it another way, can one connection be impersonating the user who previ
ously owned that
> connection?
>|||> I'd test it just to be certain.
i thought that trying to trick ADO into giving me the same pooled connection
would be like trying to get cats to walk in a line.
But my test program was
1. Open connection
2. Set Context info
3. Close connection
4. Open connection
5. Get Context info
i got the original context info back, and it turns out i (obviously) got the
same SPID both times. So, Microsoft, if you could update sp_reset_connection
in SQL Server X, that would be great.
And a service pack for SQL2000 would be nice too.|||> would be like trying to get cats to walk in a line.
I like the analogy... :-)
Thanks for letting us know that sp_reset_connection does not clear out CONTE
XT_INFO. Seems like MS
missed that (I'd guess). Possibly because CONTEXT_INFO doesn't seem to be us
ed very much.

> So, Microsoft, if you could update sp_reset_connection in SQL Server X, that would
be great.
Did you do the feedback?
http://lab.msdn.microsoft.com/productfeedback/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:ukyJH5deGHA.3556@.TK2MSFTNGP02.phx.gbl...
> i thought that trying to trick ADO into giving me the same pooled connecti
on would be like trying
> to get cats to walk in a line.
> But my test program was
> 1. Open connection
> 2. Set Context info
> 3. Close connection
> 4. Open connection
> 5. Get Context info
> i got the original context info back, and it turns out i (obviously) got t
he same SPID both times.
> So, Microsoft, if you could update sp_reset_connection in SQL Server X, th
at would be great.
> And a service pack for SQL2000 would be nice too.
>