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

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

"Usenet User" <no.spam@no.way wrote in message
news:re5dd4lc8m qvqske7fddgjhjo ff9qh9pp0@4ax.c om...

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]

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

On Sun, 21 Sep 2008 20:18:41 +0100, "Mark Rae [MVP]"
<mark@markNOSPA Mrae.netwrote:

>"Usenet User" <no.spam@no.way wrote in message
>news:re5dd4lc8 mqvqske7fddgjhj off9qh9pp0@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]]

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

"Usenet User" <no.spam@no.way wrote in message
news:tjndd49jlp sd04gpvh9bu3tse p3l4k4hhi@4ax.c om...

On Sun, 21 Sep 2008 20:18:41 +0100, "Mark Rae [MVP]"
<mark@markNOSPA Mrae.netwrote:
>

>>"Usenet User" <no.spam@no.way wrote in message
>>news:re5dd4lc 8mqvqske7fddgjh joff9qh9pp0@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/library/aa292118(VS.71).aspx

--
Mark Rae
ASP.NET MVP

http://www.markrae.net

[George]

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

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.spam@no.way wrote in message
news:tjndd49jlp sd04gpvh9bu3tse p3l4k4hhi@4ax.c om...

On Sun, 21 Sep 2008 20:18:41 +0100, "Mark Rae [MVP]"
<mark@markNOSPA Mrae.netwrote:
>

>>"Usenet User" <no.spam@no.way wrote in message
>>news:re5dd4lc 8mqvqske7fddgjh joff9qh9pp0@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]

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

On Mon, 22 Sep 2008 01:13:53 +0100, "Mark Rae [MVP]"
<mark@markNOSPA Mrae.netwrote:

>"Usenet User" <no.spam@no.way wrote in message
>news:tjndd49jl psd04gpvh9bu3ts ep3l4k4hhi@4ax. com...
>

>On Sun, 21 Sep 2008 20:18:41 +0100, "Mark Rae [MVP]"
><mark@markNOSP AMrae.netwrote:
>>

>>>"Usenet User" <no.spam@no.way wrote in message
>>>news:re5dd4l c8mqvqske7fddgj hjoff9qh9pp0@4a x.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]

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

On Mon, 22 Sep 2008 00:20:02 -0400, "George" <noemail@comcas t.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.spam@no.way wrote in message
>news:tjndd49jl psd04gpvh9bu3ts ep3l4k4hhi@4ax. com...

>On Sun, 21 Sep 2008 20:18:41 +0100, "Mark Rae [MVP]"
><mark@markNOSP AMrae.netwrote:
>>

>>>"Usenet User" <no.spam@no.way wrote in message
>>>news:re5dd4l c8mqvqske7fddgj hjoff9qh9pp0@4a x.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!