why not SQL Authentication?

[Pavlos Kariotellis]

With Forms authentication and SQL Server, MS recommends creating a User
table and storing user names and password hashes to that table.
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetHT03.aspThey[/url] 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?

[Brock Allen]

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
[url]http://staff.develop.com/ballen[/url]

> With Forms authentication and SQL Server, MS recommends creating a
> User
> table and storing user names and password hashes to that table.
> [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnne[/url]
> 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?

[WJ]

Also it may not be safe to transfer SQL PW over the line because SQL doesn
ot encrypt your PW. You also may have some issues with fire wall. Some donot
let it thru, especially the NTLM authentication packet unless you are
sitting inside your FW.

John

[Pavlos Kariotellis]

My application is serving small businesses. Each one has its own DB. Most of
the time there is one user per DB. This user my be connected all day long.
To use connection pooling I'l have to log all the users to one DB and the
switch them to appropriate DB. I think this creates a security risk.

"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
> [url]http://staff.develop.com/ballen[/url]
>
>
>

>> With Forms authentication and SQL Server, MS recommends creating a
>> User
>> table and storing user names and password hashes to that table.
>> [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnne[/url]
>> 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?

>
>
>

[Brock Allen]

Absolutely. That's why I said "for some websites it's not a problem" and
in fact for your situation it wouldn't help since you have more than one
database. Connection pooling with a single user for the database doesn't
really buy you anything since in general you're only ever using one conenction
to communicate to the DB.

-Brock
DevelopMentor
[url]http://staff.develop.com/ballen[/url]

> My application is serving small businesses. Each one has its own DB.
> Most of the time there is one user per DB. This user my be connected
> all day long. To use connection pooling I'l have to log all the users
> to one DB and the switch them to appropriate DB. I think this creates
> a security risk.
>
> "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
>> [url]http://staff.develop.com/ballen[/url]

>>> With Forms authentication and SQL Server, MS recommends creating a
>>> User
>>> table and storing user names and password hashes to that table.
>>> [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnn[/url]
>>> e
>>> 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?

[gabe garza]

Brock,
If the connection string was

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

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

3rd Connection String
Provider=SQLOLEDB.1;server=sqlmachine;uid=user1;pw d=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
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider. asp[/url]

--- 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.

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

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
> [url]http://staff.develop.com/ballen[/url]
>
>
>

>> With Forms authentication and SQL Server, MS recommends creating a
>> User
>> table and storing user names and password hashes to that table.
>> [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnne[/url]
>> 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?

>
>
>

[Brock Allen]

> Brock,

> If the connection string was

<snip>

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

I don't think anything you said there is inconsistent with what I said.

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

OLEDB connection pooling is dog slow compared to the managed provider connection
pooling.

> 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.

This is specious and in fact won't buy you much (if anything). The types
of performance problems we're indirectly speaking about are caused by latency.
The trick to avoid latency is to not make the trip to the database, so in
essence you need to cache data (if possible). Connection pooling is a form
of caching, as you're avoiding the latency in establishing the connection.
Another way to avoid latency is caching data returned from the database (typically
in the HttpContext.Cache) so that the next time the data's needed you don't
need to hit the network. I don't see the benefit of caching command objects,
as they simply represent what command you'd like to issue to the database.
If you cache the command, to get the actual data you still have to call ExecuteReader()
or ExecuteNonQuery() on the command, which induces a trip to the database.
So sorry that I disagree with you, but I don't buy it.

-Brock
DevelopMentor
[url]http://staff.develop.com/ballen[/url]

[gabe garza]

>The main drawback of SqlAuthentication (authing from browser thru website
>thru database) is that connections can't be pooled.

This is what you posted in an earlier post.
From the MSDN connections are pooled based on the connection string, not
whether your an SQL Login or the ASPNET User.

> The trick to avoid latency is to not make the trip to the database, so in
> essence you need to cache data (if possible).

It's not possible. parameters change based on users requesting information.
If all parameters are the same then static pages would do. So regardless,
you're going to have to go to the database to get your data. I'm not taking
about retrieving commonly used data either, as in state codes, etc... those
can be cached.

So what's the thing that is semi-static if you will. Your query. the only
difference would be the parameters.

If I had a stored procedure call GetEmployeeName(?) that has one parameter
for an employee id, then I'd create a command object and pass that id to
GetEmployeeName() to get my resultset, which should be the employee
information. First Name, Last Name, etc...

Now what doesn't make sense is for me is to close the command object,
dispose it, then null it. Why wouldn't I just cache that command object so
that I could just reuse it again without having to set a connection object
to it, add all the parameter descriptions again (only one in this example),
then open the command object again.
If I put in in cache then all I have to do is get it from cache, set my one
parameter employee id and execute it.
Once I'm done with it I don't have to put it back into cache, it's there for
anyone else to use it.

Of course its just not that simple, in a web based application you're
dealing with multiple users at any given time. So locking your command
object would be one thing you'd have to look at as well as building multiple
command objects of the same query command, based on system usage. System
design, very important here.

That's why caching the command objects is better. The only thing that's
changing with the command object is the paramters being passed, it's the
same connection, same stored procedure, same number of parameters. I just
don't see why programmers close and dispose of it when they're just going to
use it again.



"Brock Allen" <ballen@NOSPAMdevelop.com> wrote in message
news:310438632478083570347712@msnews.microsoft.com ...

>> Brock,
>> If the connection string was

> <snip>

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

>
> I don't think anything you said there is inconsistent with what I said.
>

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

>
> OLEDB connection pooling is dog slow compared to the managed provider
> connection pooling.
>

>> 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.

>
> This is specious and in fact won't buy you much (if anything). The types
> of performance problems we're indirectly speaking about are caused by
> latency. The trick to avoid latency is to not make the trip to the
> database, so in essence you need to cache data (if possible). Connection
> pooling is a form of caching, as you're avoiding the latency in
> establishing the connection. Another way to avoid latency is caching data
> returned from the database (typically in the HttpContext.Cache) so that
> the next time the data's needed you don't need to hit the network. I don't
> see the benefit of caching command objects, as they simply represent what
> command you'd like to issue to the database. If you cache the command, to
> get the actual data you still have to call ExecuteReader() or
> ExecuteNonQuery() on the command, which induces a trip to the database. So
> sorry that I disagree with you, but I don't buy it.
>
> -Brock
> DevelopMentor
> [url]http://staff.develop.com/ballen[/url]
>
>
>

[Brock Allen]

>> The main drawback of SqlAuthentication (authing from browser thru

>> website thru database) is that connections can't be pooled.
>>

> This is what you posted in an earlier post.
> From the MSDN connections are pooled based on the connection string,
> not
> whether your an SQL Login or the ASPNET User.

Ah, I see your point. My use of "SqlAuthentication" was misunderstood. The
qualifier I provided after "authing from browser thru website thru database"
was the important piece which is consonnant with your post. So yes, I apologise
for the misleading terminology. But my point is still valid -- using the
credentials of the user in the browser all the way to the database means
you can't share/pool that connection.

>> The trick to avoid latency is to not make the trip to the database,
>> so in essence you need to cache data (if possible).
>>

> It's not possible. parameters change based on users requesting
> information. If all parameters are the same then static pages would
> do. So regardless, you're going to have to go to the database to get
> your data. I'm not taking about retrieving commonly used data either,
> as in state codes, etc... those can be cached.

It always depends upon what your data is. And you can cache larger result
sets and then show subsets based upon individual requests. But what you're
trying to say is that's not always possible. I don't disagree. But I maintain
that it is sometimes possible.

> Now what doesn't make sense is for me is to close the command object,
> dispose it, then null it. Why wouldn't I just cache that command
> object so that I could just reuse it again without having to set a connection
> object to it, add all the parameter descriptions again (only one in this
> example), then open the command object again.
> If I put in in cache then all I have to do is get it from cache, set
> my one parameter employee id and execute it.
> Once I'm done with it I don't have to put it back into cache, it's
> there for anyone else to use it.

I understand the sentiment here, but I still think it's specious. If you
cache the command (and I presume you're implying that you leave the connection
open), then you've just guaranteed the connection will remain open for the
duration of your cache. If there are no other users in your application then
you've guaranteed the resource is not released when it otherwise could have
been. The important resource here is the connection not the command. Caching
the command object itself does save you the creation time, but that's negligible
in the greater scheme of things. Leaving the connection open when you're
not using it is wasting the resource.

Now you could implement a timeout for the cache. Once the cache times out
(presumably because there are no users in your application) then you could
close the connection. But this is what connection pooling already does for
you. Also, the connection pool is dynamic, meaning it grows and shrinks based
upon demand. In your example, if you only cache a single command then that
becomes your bottleneck, meaning only one thread can ever access the database
at any one time. I assume you wouldn't cache only a single command, but then
you're building your own pooling mechanism. And given that the command itself
is a negligible resource you've just reinvented the wheel that the connection
pool already has available.

Now that we're discussing multiple pages sharing your command object, the
docs specifically state for the SqlCommand, OleDbCommand, ODBCCommand and
OracleCommand that these objects are not thread safe. This is, of course,
another consideration in caching. Perhaps they're not thread safe to be used
at the same time across two threads, or perhaps this means they're simply
not safe to be accessed by any other thread than the one that created them.
If you were building your own pooling mechanism then I assume you'd not hand
out the same command to two different clients (and then perhaps threads)
at the same time. So as for the ability to create the command on one thread
and use it on another, I'm sure you could do some testing to become reasonably
comfortable if they're only used one at a time that it would work. But it's
not outside the realm of possibility that these objects have some sort of
thread affinity and wouldn't be so happy working on a separate thread. This
has happened to me in the past (specifically with Oracle, FWIW).

> That's why caching the command objects is better. The only thing
> that's changing with the command object is the paramters being passed,
> it's the same connection, same stored procedure, same number of
> parameters. I just don't see why programmers close and dispose of it
> when they're just going to use it again.

Again, I know what you're getting at. I'm curious what your performance tests
have shown with this strategy. How much faster is it than simply relying
upon the built in connection pool? I'm curious now... :)

-Brock
DevelopMentor
[url]http://staff.develop.com/ballen[/url]