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, CONTEXT_INFO could just be
a special case... I'd test it just to be certain. Note that the article specifies 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/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?
>|||> 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 CONTEXT_INFO. Seems like MS
missed that (I'd guess). Possibly because CONTEXT_INFO doesn't seem to be used 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'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.
>