Connection Pooling

Discussion in 'ASP .Net' started by Not Me, Jan 24, 2006.

  1. Not Me

    Not Me Guest

    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
    Not Me, Jan 24, 2006
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:dr51jt$1l7$...
    > 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, Jan 24, 2006
    #2
    1. Advertising

  3. 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" <> wrote in message
    news:dr51jt$1l7$...
    > 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
    >
    Karl Seguin [MVP], Jan 24, 2006
    #3
  4. Not Me

    Not Me Guest

    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
    Not Me, Jan 24, 2006
    #4
  5. 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" <> wrote in message
    news:dr5eo5$qe8$...
    > 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
    Karl Seguin [MVP], Jan 24, 2006
    #5
  6. 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:]
    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
    Daniel Fisher\(lennybacon\), Jan 24, 2006
    #6
  7. Not Me

    Not Me Guest

    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
    Not Me, Jan 24, 2006
    #7
  8. Looks like you have it right :)

    Karl

    --
    MY ASP.Net tutorials
    http://www.openmymind.net/


    "Not Me" <> wrote in message
    news:dr5i34$s1h$...
    > 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
    Karl Seguin [MVP], Jan 24, 2006
    #8
  9. Not Me

    Phani Guest

    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" <> wrote in message
    > news:dr5i34$s1h$...
    > > 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
    Phani, Jan 25, 2006
    #9
  10. 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" <> wrote in message
    news:...
    > 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" <> wrote in message
    >> news:dr5i34$s1h$...
    >> > 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

    >
    Karl Seguin [MVP], Jan 26, 2006
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Trevor Hartman

    connection pooling

    Trevor Hartman, Jul 28, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    547
    Mark Heimonen
    Jul 28, 2003
  2. Chris Szabo

    connection pooling error

    Chris Szabo, Aug 19, 2003, in forum: ASP .Net
    Replies:
    6
    Views:
    1,861
    Chris Szabo
    Aug 19, 2003
  3. William \(Bill\) Vaughn

    Re: SqlConnection and connection pooling

    William \(Bill\) Vaughn, Nov 14, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    486
    William \(Bill\) Vaughn
    Nov 14, 2003
  4. =?Utf-8?B?UGllcnNvbiBD?=

    Connection Pooling, Dispose/Close/Using

    =?Utf-8?B?UGllcnNvbiBD?=, Oct 14, 2004, in forum: ASP .Net
    Replies:
    9
    Views:
    4,584
    speedy
    Nov 26, 2008
  5. =?Utf-8?B?VmFtJHk=?=

    Connection pooling

    =?Utf-8?B?VmFtJHk=?=, Nov 24, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    478
    Patrice
    Nov 24, 2004
Loading...

Share This Page