Re: SQLOLEDB provider internally creates multiple connections to SQL server when commands executed asynchronous - can I control this?
- From: "Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx>
- Date: Wed, 13 Aug 2008 06:59:50 -0400
alex_wh@xxxxxxx wrote:
So the problem.
Server logic uses its own implementation of secured access to data, so
login and password used for ADO Connection object to its success
connectiong to the server (via TCP/IP).
Later user logins via customized server login procedure and server
stores user ID with SET CONTEXT_INFO feature of MS SQL server.
So any server procedures that wants to control user access, reads
"context_info" field of the "sysprocesses" system server table.
The need of using of CONTEXT_INFO feature is so: there is no need to
pass user id into every call of the protected server procedure. It is
fine (security, traffic, etc.)
I'm not sure I completely understand your description, but it seems that you
are misusing CONTEXT_INFO, which is intended to allow multiple batches to be
run on the same connection in order to facilitate the new MARS
functionality, not to allow multiple users to use the same user info. It
sounds as if you should be using the new "EXECUTE AS user" functionality to
accomplish your goal.
But on the client side, when application tryes to perform some
activity on the background using Command object with adAsyncExecute
flag, if Connection object is busy (executing or fetching), ADO
internally... creates additional TCP connection to the SQL server, and
performs nedded operation (I can see this with netstat utility). But
on the server side @@SPID is other (this is another session for SQL
server!), and context_info occures empty.
But the alternative would be that the asynchronous process would have to
wait for the ongoing process to complete. Is that what you really want? If
so, why are you calling it asynchronously?
I doubt it, but I don't know for sure. I've never tried to do something like
Moreover, ADO drops unused connection "older-first", and other command
objects in application accorded to execute normal, synchronous
commands, becoms out of session context info on the server side.
Is there any way to control this automatic creation of connections to
the SQL server?
this. Look into context-switching using EXECUTE AS.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
.
- Follow-Ups:
- References:
- Prev by Date: SQLOLEDB provider internally creates multiple connections to SQL server when commands executed asynchronous — can I control this?
- Next by Date: Re: SQLOLEDB provider internally creates multiple connections to SQL server when commands executed asynchronous - can I control this?
- Previous by thread: SQLOLEDB provider internally creates multiple connections to SQL server when commands executed asynchronous — can I control this?
- Next by thread: Re: SQLOLEDB provider internally creates multiple connections to SQL server when commands executed asynchronous - can I control this?
- Index(es):
Relevant Pages
|
