Public ASP.NET app and SQL Server security - best practices?

[Usenet User]

Platform: Win 2003 Server, ASP.NET 1.1, SQL Server 2000 or 2005

I need to create an ASP.NET app that is going to be published on an
external web site. User authentication is Windows Integrated (no
forms authentication or anonymous access).

The app will utilize a SQL database on the same server, and I was
wondering how I should handle SQL security.

For internal apps I always use a single unprivileged SQL account per
app: that simplifies the configuration and uses SQL connection pooling
effectively. But I would not want to have SQL authentication enabled
on this server and want to stick to Windows trusted authentication.

I would rather avoid enabling identity impersonation in my app. But
how would I then connect to the database? One way is to give to
application thread account (local ASPNET, Network Identity) desired
permission on the database level. Is this really the way?

Thanks!

[Mark Rae [MVP]]

"Usenet User" <no*****@no.waywrote in message
news:re********************************@4ax.com...

I would rather avoid enabling identity impersonation in my app.

Why? Seems to be a perfect solution...
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Usenet User]

On Sun, 21 Sep 2008 20:18:41 +0100, "Mark Rae [MVP]"
<ma**@markNOSPAMrae.netwrote:

>"Usenet User" <no*****@no.waywrote in message
news:re********************************@4ax.com.. .

>I would rather avoid enabling identity impersonation in my app.

Why? Seems to be a perfect solution...

Well, aside from extra maintenance effort (every user needs to have
access to the database), this would also, I believe, scale poorly due
to ineffective SQL connection pooling, because every app instance will
use different credentials when connecting to the DB.

Also, identity impersonation was the default model in classic ASP,
which is considered one of the significant drawbacks of that
technology.

Or am I missing something?

Thanks!

[Mark Rae [MVP]]

"Usenet User" <no*****@no.waywrote in message
news:tj********************************@4ax.com...

On Sun, 21 Sep 2008 20:18:41 +0100, "Mark Rae [MVP]"
<ma**@markNOSPAMrae.netwrote:

>>"Usenet User" <no*****@no.waywrote in message
news:re********************************@4ax.com. ..

>>I would rather avoid enabling identity impersonation in my app.

Why? Seems to be a perfect solution...

Well, aside from extra maintenance effort (every user needs to have
access to the database), this would also, I believe, scale poorly due
to ineffective SQL connection pooling, because every app instance will
use different credentials when connecting to the DB.

Also, identity impersonation was the default model in classic ASP,
which is considered one of the significant drawbacks of that
technology.

Or am I missing something?

You most certainly are.

With identity impersonation, your ASP.NET app runs in the context of a
single user whose privileges you assign - nothing whatsoever to do with the
individual users who use the app...
http://msdn.microsoft.com/en-us/libr...18(VS.71).aspx
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[George]

Well, I am a bit confused...
If you do not want to mess with every user having an account on SQL server
then why not create single account and allow application work through that.
Why do you want to use Integrated Security for your connection strings.
If you are afraid that someone will connect to SQL server directly then
check if you have a firewall and use it.
----------------------------------------------------------------------
Also I am not clear why would you want to use NT authentication and disable
anonymous access.
I belive only IE supports NT athentication. So people who is using Firefox
will not be able to access.

George.

"Usenet User" <no*****@no.waywrote in message
news:tj********************************@4ax.com...

On Sun, 21 Sep 2008 20:18:41 +0100, "Mark Rae [MVP]"
<ma**@markNOSPAMrae.netwrote:

>>"Usenet User" <no*****@no.waywrote in message
news:re********************************@4ax.com. ..

>>I would rather avoid enabling identity impersonation in my app.

Why? Seems to be a perfect solution...

Well, aside from extra maintenance effort (every user needs to have
access to the database), this would also, I believe, scale poorly due
to ineffective SQL connection pooling, because every app instance will
use different credentials when connecting to the DB.

Also, identity impersonation was the default model in classic ASP,
which is considered one of the significant drawbacks of that
technology.

Or am I missing something?

Thanks!

[Usenet User]

On Mon, 22 Sep 2008 01:13:53 +0100, "Mark Rae [MVP]"
<ma**@markNOSPAMrae.netwrote:

>"Usenet User" <no*****@no.waywrote in message
news:tj********************************@4ax.com.. .

>On Sun, 21 Sep 2008 20:18:41 +0100, "Mark Rae [MVP]"
<ma**@markNOSPAMrae.netwrote:

>>>"Usenet User" <no*****@no.waywrote in message
news:re********************************@4ax.com ...

I would rather avoid enabling identity impersonation in my app.

Why? Seems to be a perfect solution...

Well, aside from extra maintenance effort (every user needs to have
access to the database), this would also, I believe, scale poorly due
to ineffective SQL connection pooling, because every app instance will
use different credentials when connecting to the DB.

Also, identity impersonation was the default model in classic ASP,
which is considered one of the significant drawbacks of that
technology.

Or am I missing something?

You most certainly are.

With identity impersonation, your ASP.NET app runs in the context of a
single user whose privileges you assign - nothing whatsoever to do with the
individual users who use the app...
http://msdn.microsoft.com/en-us/libr...18(VS.71).aspx

It turns out, it can be both: either the user himself is impersonated,
or a single pre-configured user is impersonated. (I am dismissing
anonymous access here.) A single pre-configured user is, probably,
what I am looking for. Thanks for pointing this out.

[Usenet User]

On Mon, 22 Sep 2008 00:20:02 -0400, "George" <no*****@comcast.net>
wrote:

>Well, I am a bit confused...
If you do not want to mess with every user having an account on SQL server
then why not create single account and allow application work through that.

I forgot that it is possible: to impersonate just a single NT user.

>Why do you want to use Integrated Security for your connection strings.
If you are afraid that someone will connect to SQL server directly then
check if you have a firewall and use it.

Well, this goes beyond my involvement. I can suggest, but I cannot
control or guarantee that this will happen. Disabling SQL
authentication seems like an extra measure I can take, that's all.

>----------------------------------------------------------------------
Also I am not clear why would you want to use NT authentication and disable
anonymous access.
I belive only IE supports NT athentication. So people who is using Firefox
will not be able to access.

That is to pass on user management. Implementing forms authentication
along user management within the app is too expensive in terms of time
and money. As far as Windows already provides authentication, we can
utilize that. Other browsers are of no concern, IE is a requirement
for the end users.

Thanks!

>
George.

"Usenet User" <no*****@no.waywrote in message
news:tj********************************@4ax.com.. .

>On Sun, 21 Sep 2008 20:18:41 +0100, "Mark Rae [MVP]"
<ma**@markNOSPAMrae.netwrote:

>>>"Usenet User" <no*****@no.waywrote in message
news:re********************************@4ax.com ...

I would rather avoid enabling identity impersonation in my app.

Why? Seems to be a perfect solution...

Well, aside from extra maintenance effort (every user needs to have
access to the database), this would also, I believe, scale poorly due
to ineffective SQL connection pooling, because every app instance will
use different credentials when connecting to the DB.

Also, identity impersonation was the default model in classic ASP,
which is considered one of the significant drawbacks of that
technology.

Or am I missing something?

Thanks!