473,320 Members | 2,052 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Connection Pooling

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

Jan 24 '06 #1
9 5676
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.

"Not Me" <no****@zxy.blah.org> wrote in message
news:dr**********@ucsnew1.ncl.ac.uk...
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

Jan 24 '06 #2
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

--
http://www.openmymind.net/

"Not Me" <no****@zxy.blah.org> wrote in message
news:dr**********@ucsnew1.ncl.ac.uk...
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

Jan 24 '06 #3
Karl Seguin [MVP] wrote:
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
Jan 24 '06 #4
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

--
MY ASP.Net tutorials
http://www.openmymind.net/
"Not Me" <no****@zxy.blah.org> wrote in message
news:dr**********@ucsnew1.ncl.ac.uk...
Karl Seguin [MVP] wrote:
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

Jan 24 '06 #5
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:no****@zxy.blah.org]
Posted At: Tuesday, January 24, 2006 3:49 PM
Posted To: microsoft.public.dotnet.framework.aspnet
Conversation: Connection Pooling
Subject: Re: Connection Pooling

Karl Seguin [MVP] wrote:
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

Jan 24 '06 #6
Karl Seguin [MVP] wrote:
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
Jan 24 '06 #7
Looks like you have it right :)

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/
"Not Me" <no****@zxy.blah.org> wrote in message
news:dr**********@ucsnew1.ncl.ac.uk...
Karl Seguin [MVP] wrote:
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

Jan 24 '06 #8
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] wrote:
Looks like you have it right :)

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/
"Not Me" <no****@zxy.blah.org> wrote in message
news:dr**********@ucsnew1.ncl.ac.uk...
Karl Seguin [MVP] wrote:
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


Jan 25 '06 #9
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

--
MY ASP.Net tutorials
http://www.openmymind.net/
"Phani" <ph***********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
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] wrote:
Looks like you have it right :)

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/
"Not Me" <no****@zxy.blah.org> wrote in message
news:dr**********@ucsnew1.ncl.ac.uk...
> Karl Seguin [MVP] wrote:
>> 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

Jan 26 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Mithun Verma | last post by:
Hello All, I am working on a windows application which will talk to the database through the Web services. So i need to enhaance the performance, for which i m using connection
5
by: John | last post by:
Does COM.ibm.db2.jdbc.DB2DataSource, (which supports connection pooling) need to be run within a J2EE container environment before the connection pooling facility is actually available to a user? ...
18
by: Rob Nicholson | last post by:
We're getting an occasional occurrence of the following error when two users try and open the same record in our ASP.NET app: "There is already an open DataReader associated with this Connection...
1
by: Lenny Shprekher | last post by:
Hi, I am getting issues that Oracle collecting opened sessions (connections) from my webservice using regular System.Data.OleDb.OleDbConnection object. I am guessing that this is connection...
2
by: JimLad | last post by:
Hi, In an existing ASP/ASP.NET 1.1 app running on IIS 6, I need to RELIABLY pass the logged in username through to the SQL Server 2000 database for auditing purposes. The current method is...
16
by: crbd98 | last post by:
Hello All, Some time ago, I implemented a data access layer that included a simple connectin pool. At the time, I did it all by myself: I created N connections, each connection associated with...
20
by: fniles | last post by:
I am using VS2003 and connecting to MS Access database. When using a connection pooling (every time I open the OLEDBCONNECTION I use the exact matching connection string), 1. how can I know how...
3
by: fniles | last post by:
In the Windows application (using VB.NET 2005) I use connection pooling like the following: In the main form load I open a connection using a connection string that I stored in a global variable...
0
viswarajan
by: viswarajan | last post by:
Introduction This article is to go in deep in dome key features in the ADO.NET 2 which was shipped with VS 2005. In this article I will go trough one of the key features which is the Connection...
15
by: Sylvie | last post by:
I have a static function in a class, everytime I call this function, I am creating a SQLconnection, open it, use it, and null it, All my functions and application logic is like this, Every...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.