Connection Pooling

[Not Me]

Hi,

I'm using asp.net 2 with an sql server 2000 database. What steps if
any, must I take to ensure connection pooling efficiently?

for any 'manual' connections made, I open the sqlconnection obtaining
the connection string from configurationmanager.connectionstrings, and
close the connection as soon as it's been finished with.

I haven't run any tests yet but just wondered if there's something I
should be doing at this stage to help in the future.

Cheers,
Chris

 

[Kevin Spencer]

Hi Chris,

Sounds like you're in good shape. The .Net platform will handle the pooling,
and as long as you close your connections immediately, as you're doing now,
you will get the best performance out of it.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Who is Mighty Abbott?
A twin turret scalawag.

 

[Karl Seguin [MVP]]

Are you using SQL Authentication in your connection string? if so, ur good
to go as-is. If you are using windows imporsonation then each user will have
his/her own connection string which won't allow it to pool.

Karl

 

[Not Me]

Are you using SQL Authentication in your connection string? if so, ur good
to go as-is. If you are using windows imporsonation then each user will have
his/her own connection string which won't allow it to pool.

ooh, this bit I'm not so sure on as I don't manage the servers. Without
using the proper terminology what I think is happening is that the IIS
server is running the page as a specific windows user, so that when
using integrated security, it checks the sql server credentials of that
(windows) user, which of course holds valid permissions.

When you say SQL authentication do you mean having a user/login
hard-coded into the SQL server? I understood the windows authentication
is more secure?

Ta for the help,
Chris

 

[Karl Seguin [MVP]]

Yes, that's what I mean when I say SQL authentication (SQL Server has it's
own authentication capabilities as well as using windows username/password).
I'm not sure which is more secure.

What I can tell you is:

If you are using Windows Authentication you may or not has a problem.

If you are using Windows Authentication with ASP.NET impersonation turned
on, then I don't believe pooling will happen. you can find out by going into
the web.config. If this is a public internet site, then you don't have
impersonation turne don. If it's a private intranet site where all users are
on the same domain, it's possible that you are using impersonation.

If you are using Windows Authentication with ASP.NTE impersonation turned
OFF, then pooling will happen. This simply means that you've granted the
ASP.NET account access to your sql server and that each connection will be
made by the ASP.NET account (thus enabling pooling). In the case mentioned
above, the creditials for each request is based on the server, so if 10
different people access the site, then you'll have 10 different pools - no
ideal.

Karl

 

[Daniel Fisher\(lennybacon\)]

It is more secure.

What Karl meant (I guess) was that if you use Impersonation (your
ASP.NET code then is executed under the authorized user) the connection
to the database is made using the authorized user (same as ASP.NET code
is executed).

--Daniel
http://staff.newtelligence.com/danielf/



-----Original Message-----
From: Not Me [mailto:[email protected]]
Posted At: Tuesday, January 24, 2006 3:49 PM
Posted To: microsoft.public.dotnet.framework.aspnet
Conversation: Connection Pooling
Subject: Re: Connection Pooling

Are you using SQL Authentication in your connection string? if so, ur good
to go as-is. If you are using windows imporsonation then each user will have
his/her own connection string which won't allow it to pool.

ooh, this bit I'm not so sure on as I don't manage the servers. Without

using the proper terminology what I think is happening is that the IIS
server is running the page as a specific windows user, so that when
using integrated security, it checks the sql server credentials of that
(windows) user, which of course holds valid permissions.

When you say SQL authentication do you mean having a user/login
hard-coded into the SQL server? I understood the windows authentication

is more secure?

Ta for the help,
Chris

 

[Not Me]

Yes, that's what I mean when I say SQL authentication (SQL Server has it's
own authentication capabilities as well as using windows username/password).
I'm not sure which is more secure.

What I can tell you is:

If you are using Windows Authentication you may or not has a problem.

If you are using Windows Authentication with ASP.NET impersonation turned
on, then I don't believe pooling will happen. you can find out by going into
the web.config. If this is a public internet site, then you don't have
impersonation turne don. If it's a private intranet site where all users are
on the same domain, it's possible that you are using impersonation.

If you are using Windows Authentication with ASP.NTE impersonation turned
OFF, then pooling will happen. This simply means that you've granted the
ASP.NET account access to your sql server and that each connection will be
made by the ASP.NET account (thus enabling pooling). In the case mentioned
above, the creditials for each request is based on the server, so if 10
different people access the site, then you'll have 10 different pools - no
ideal.

Thanks for trying to clear this up.. so is ASP.net impersonation where
it takes the credentials of the machine that is trying to access the
page, and uses those details to authenticate against the sql server?
This will be a public site so I'm not interested in that.. so hopefully
there shouldn't be any issues with the pooling.

cheers,
Chris

 

[Karl Seguin [MVP]]

Looks like you have it right

Karl

 

[Phani]

Any idea where and how we can limit the number of connections to SQL
Server ? Lets say a request comes for accessing the database and all
the connections are in use. The request needs to wait until a
connection is dropped so it can use it. The idea is to limit the number
of connections to the database to some value for example 5 or 10 etc.
at any point.
Phani

 

[Karl Seguin [MVP]]

There is a Max Pool Size property to the connection string, but I
reallywouldn't recommend you play with it unless you really know what youa
re doing.

Karl