Connection Pooling

I am currently building an OLAP application in AS2005 which is going to
integrate with an existing web application that uses Connection Pooling.
This will mean that there is only a few distinct accounts which access the
AS database (although there will be thousands of users), and I would like to
utilise the new CustomData connection string parameter to feed account ID's
through to AS2005 as a source for security.
Has anyone out there used AS2005 with connection pooling in this way?, and
secondly has anyone used the new CustomData parameter, and can this be
altered in the connection string without forcing a new connection to be
generated; which would obviously negate the reason for Connection Pooling...
Thanks.From my experience, if you alter the connection string, it creates a NEW
connection, and also creates a new Connection Pool for that new connection.
If your users are domain authenticated users, then even if they are using a
common connection account, their domain identity can still be known by SQL
Server.
You may want to read up on SESSION_USER() in books on Line.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"SimonE" <SimonE@.discussions.microsoft.com> wrote in message
news:9B9C0356-8E73-4930-A5CA-AFC3E5E25224@.microsoft.com...
>I am currently building an OLAP application in AS2005 which is going to
> integrate with an existing web application that uses Connection Pooling.
> This will mean that there is only a few distinct accounts which access the
> AS database (although there will be thousands of users), and I would like
> to
> utilise the new CustomData connection string parameter to feed account
> ID's
> through to AS2005 as a source for security.
> Has anyone out there used AS2005 with connection pooling in this way?, and
> secondly has anyone used the new CustomData parameter, and can this be
> altered in the connection string without forcing a new connection to be
> generated; which would obviously negate the reason for Connection
> Pooling...
> Thanks.
>|||Thanks for this Arnie.
The issue I have is that I want to feed in a user ID from the web
application session, which will not be the ID used to access AS2005, but is
needed to base the security. The users domain login is unfortuantely no use
here as the IDs are completely seperate.
Is it feasible to think that I could set a system parameter (which wouldn't
effect other sessions) for the current session, and then access this
parameter from within AS2005, or perhaps by executing a stored procedure from
within AS?
"Arnie Rowland" wrote:
> From my experience, if you alter the connection string, it creates a NEW
> connection, and also creates a new Connection Pool for that new connection.
> If your users are domain authenticated users, then even if they are using a
> common connection account, their domain identity can still be known by SQL
> Server.
> You may want to read up on SESSION_USER() in books on Line.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "SimonE" <SimonE@.discussions.microsoft.com> wrote in message
> news:9B9C0356-8E73-4930-A5CA-AFC3E5E25224@.microsoft.com...
> >I am currently building an OLAP application in AS2005 which is going to
> > integrate with an existing web application that uses Connection Pooling.
> >
> > This will mean that there is only a few distinct accounts which access the
> > AS database (although there will be thousands of users), and I would like
> > to
> > utilise the new CustomData connection string parameter to feed account
> > ID's
> > through to AS2005 as a source for security.
> >
> > Has anyone out there used AS2005 with connection pooling in this way?, and
> > secondly has anyone used the new CustomData parameter, and can this be
> > altered in the connection string without forcing a new connection to be
> > generated; which would obviously negate the reason for Connection
> > Pooling...
> >
> > Thanks.
> >
>
>|||You could just add the UsedID as an INPUT parameter to the stored procedures
that are called to provide the users the information they seek. Of course,
if your users are running dynamic queries from QA or a similar tool, that
wouldn't work. If you have a base of stored procedures in place and don't
want to add the UserID input param, you could create a wrapper stored
procedure with the UserID input param that then verifies (or logs, etc.)
user information and then executes the current stored procedures.
As far as I know, there is no 'simple' way to pass in the UserID identity as
an element of the connection string. Using a common connection string will,
as you know, have the effect that every request from the web server will be
seen as coming from the same users.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"SimonE" <SimonE@.discussions.microsoft.com> wrote in message
news:E591F675-72DC-4DC8-98CB-596C3EEC8E49@.microsoft.com...
> Thanks for this Arnie.
> The issue I have is that I want to feed in a user ID from the web
> application session, which will not be the ID used to access AS2005, but
> is
> needed to base the security. The users domain login is unfortuantely no
> use
> here as the IDs are completely seperate.
> Is it feasible to think that I could set a system parameter (which
> wouldn't
> effect other sessions) for the current session, and then access this
> parameter from within AS2005, or perhaps by executing a stored procedure
> from
> within AS?
>
> "Arnie Rowland" wrote:
>> From my experience, if you alter the connection string, it creates a NEW
>> connection, and also creates a new Connection Pool for that new
>> connection.
>> If your users are domain authenticated users, then even if they are using
>> a
>> common connection account, their domain identity can still be known by
>> SQL
>> Server.
>> You may want to read up on SESSION_USER() in books on Line.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "SimonE" <SimonE@.discussions.microsoft.com> wrote in message
>> news:9B9C0356-8E73-4930-A5CA-AFC3E5E25224@.microsoft.com...
>> >I am currently building an OLAP application in AS2005 which is going to
>> > integrate with an existing web application that uses Connection
>> > Pooling.
>> >
>> > This will mean that there is only a few distinct accounts which access
>> > the
>> > AS database (although there will be thousands of users), and I would
>> > like
>> > to
>> > utilise the new CustomData connection string parameter to feed account
>> > ID's
>> > through to AS2005 as a source for security.
>> >
>> > Has anyone out there used AS2005 with connection pooling in this way?,
>> > and
>> > secondly has anyone used the new CustomData parameter, and can this be
>> > altered in the connection string without forcing a new connection to be
>> > generated; which would obviously negate the reason for Connection
>> > Pooling...
>> >
>> > Thanks.
>> >
>>