Re: why not SQL Authentication?

From: gabe garza (gbgarza_at_yahoo.com)
Date: 03/31/05 

Date: Thu, 31 Mar 2005 00:04:20 GMT

Brock,
If the connection string was

1st Connection String
   Provider=SQLOLEDB.1;server=sqlmachine;uid=user1;pwd=pass1;database=mydb

2nd Connection String
   Provider=SQLOLEDB.1;server=sqlmachine;uid=user2;pwd=pass2;database=mydb

3rd Connection String
   Provider=SQLOLEDB.1;server=sqlmachine;uid=user1;pwd=pass1;database=mydb

Then 1 and 3 are pooled because of the following statement.
  "a connection pool is created based on an exact matching algorithm that
associates the pool with the connection string"

This is for SQL Server
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.asp

--- start msdn ---
Pool Creation and Assignment
When a connection is opened, a connection pool is created based on an exact
matching algorithm that associates the pool with the connection string in
the connection. Each connection pool is associated with a distinct
connection string. When a new connection is opened, if the connection string
is not an exact match to an existing pool, a new pool is created.

In the following example, three new SqlConnection objects are created, but
only two connection pools are required to manage them. Note that the first
and second connection strings differ by the value assigned for Initial
Catalog.

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Integrated Security=SSPI;Initial
Catalog=northwind";
conn.Open();
// Pool A is created.

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=pubs";
conn.Open();
// Pool B is created because the connection strings differ.

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Integrated Security=SSPI;Initial
Catalog=northwind";
conn.Open();
// The connection string matches pool A.Once created, connection pools are
not destroyed until the active process ends. Maintenance of inactive or
empty pools involves minimal system overhead.

--- end msdn ---

So on a web based application, connections can and are pooled based on the
Connection String. So SQLAuthentication logins can be pooled.

I would read the following URL for how other providers work as well.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconadonetconnections.asp

I think reading about OLE-DB Providers would be helpful as well.

Of course with all of this, in my opinion pooling a connection is just the
first step in having a performance enhancement system, the next step is
caching your command objects. The command objects are the objects doing the
actual work, over and over again.

"Brock Allen" <ballen@NOSPAMdevelop.com> wrote in message
news:283855632475988240591776@msnews.microsoft.com...
> The main drawback of SqlAuthentication (authing from browser thru website
> thru database) is that connections can't be pooled. For some websites this
> is not a concern, but for others where you have huge volume (and/or you're
> not doing windows auth against the clients) if you use the client's creds
> for SqlAuth then that's an independant connection. So 1000 users on your
> site, that's 1000 distinct connections. If you use the same credentials
> (like a "SqlUser" account) then those connections get pooled and thus
> shared. It's a performance enhancement.
>
> -Brock
> DevelopMentor
> http://staff.develop.com/ballen
>
>
>
>> With Forms authentication and SQL Server, MS recommends creating a
>> User
>> table and storing user names and password hashes to that table.
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnne
>> tsec/html/SecNetHT03.aspThey go on proposing a Roles table and so on.I
>> wonder why not just use SQL Server authentication and just try to
>> loginwith the user supplied credentials?
>
>
>

Relevant Pages

  • ASPNet in VS.NET 2003 - a bug?
    ... I made a base class for data that retrieved the connect string. ... a new SQLConnection with the connection string. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Developing asp.net with VS.NET 2003
    ... I made a base class for data that retrieved the connect string. ... a new SQLConnection with the connection string. ...
    (microsoft.public.vsnet.general)
  • Re: Max pool size reached exception when trying to open a new connection
    ... Hi i am wrapping the sqlconnection in using statement.so there is no ... doubt that dispose is not getting called on sqlconnection. ... prior to obtaining a connection from the pool. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Timeout Expired on first System.Data.Common.DbDataAdapter.Fill
    ... You could also try without the min pool size to ... > prior to obtaining a connection from the pool. ... > the same connection string, ... > startRecord, Int32 maxRecords, String srcTable, IDbCommand command, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: [SOLVED] Cannot display provider-specific login prompt
    ... scenario where the program wouldn't have to know about the database, ... ConnectionStringBuilder up to a PropertyGrid for the end user to populate), ... details of the connection, for the sake of the program being able to connect ... information about the connection string that I will need. ...
    (microsoft.public.dotnet.framework.adonet)