Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   ASP .Net Security (http://www.velocityreviews.com/forums/f62-asp-net-security.html)
-   -   What's best practice for connecting to a Sql Server database (http://www.velocityreviews.com/forums/t769944-whats-best-practice-for-connecting-to-a-sql-server-database.html)

David Thielen 08-29-2008 04:35 PM

What's best practice for connecting to a Sql Server database
 
Hi;

Back in the old old days of .NET 2.0 on IIS 7 the best practice was
that the web app ran under a user that had very weak rights and the
connection string had the uname/pw to connect to the database.

We are now moving up to Windows 2008 and IIS 8 and I have a developer
here telling me that best practives now are to get the web app the
rights needed to connect to the database and use integrated security
in the connection string. Is this the case?

And if so:

1) What username should the web app run under?

2) Do we assign that user rights to access the database or do we
create a group that can do so and assign that group across?

thanks - dave

david@at-at-at@windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

Joe Kaplan 08-29-2008 06:05 PM

Re: What's best practice for connecting to a Sql Server database
 
I doubt you are moving to IIS 8 yet since IIS 7 is the version shipping in
2008 server and Vista. :)

That said, I generally prefer using Windows auth over SQL auth when possible
as it makes it possible to centrally manage accounts in AD. However, some
customers may prefer to use SQL auth. Providing an option is probably a
good idea.

Which account to use should also be something the customer can choose, but
when using Windows auth in an architecture like yours (which looks like it
uses a fixed service account), using the IIS process identity to access SQL
is usually the easiest thing. The customer can configure whatever app pool
identity they want to use to access SQL that way.

As to whether they use groups to grant access to SQL or grant access
directly to specific security principles should be their decision as well.
I do recommend you use roles in SQL to abstract your permissions at the
database level so they can assign whatever principle they want to your roles
in order to grant the correct set of privileges at the SQL to the app.

--
Joe Kaplan-MS MVP Directory Services Programming
Co-author of "The .NET Developer's Guide to Directory Services Programming"
http://www.directoryprogramming.net
--
"David Thielen" <thielen@nospam.nospam> wrote in message
news:749gb4pvsluef788rk7hieut23uavpq72d@4ax.com...
> Hi;
>
> Back in the old old days of .NET 2.0 on IIS 7 the best practice was
> that the web app ran under a user that had very weak rights and the
> connection string had the uname/pw to connect to the database.
>
> We are now moving up to Windows 2008 and IIS 8 and I have a developer
> here telling me that best practives now are to get the web app the
> rights needed to connect to the database and use integrated security
> in the connection string. Is this the case?
>
> And if so:
>
> 1) What username should the web app run under?
>
> 2) Do we assign that user rights to access the database or do we
> create a group that can do so and assign that group across?
>
> thanks - dave
>
> david@at-at-at@windward.dot.dot.net
> Windward Reports -- http://www.WindwardReports.com
> me -- http://dave.thielen.com
>
> Cubicle Wars - http://www.windwardreports.com/film.htm




David Thielen 08-31-2008 01:23 AM

Re: What's best practice for connecting to a Sql Server database
 
Thank you very much. And yes, we're going from 6 to 7 - I keep getting
that wrong for some reason.

thanks - dave


On Fri, 29 Aug 2008 13:05:02 -0500, "Joe Kaplan"
<joseph.e.kaplan@removethis.accenture.com> wrote:

>I doubt you are moving to IIS 8 yet since IIS 7 is the version shipping in
>2008 server and Vista. :)
>
>That said, I generally prefer using Windows auth over SQL auth when possible
>as it makes it possible to centrally manage accounts in AD. However, some
>customers may prefer to use SQL auth. Providing an option is probably a
>good idea.
>
>Which account to use should also be something the customer can choose, but
>when using Windows auth in an architecture like yours (which looks like it
>uses a fixed service account), using the IIS process identity to access SQL
>is usually the easiest thing. The customer can configure whatever app pool
>identity they want to use to access SQL that way.
>
>As to whether they use groups to grant access to SQL or grant access
>directly to specific security principles should be their decision as well.
>I do recommend you use roles in SQL to abstract your permissions at the
>database level so they can assign whatever principle they want to your roles
>in order to grant the correct set of privileges at the SQL to the app.
>
>--
>Joe Kaplan-MS MVP Directory Services Programming
>Co-author of "The .NET Developer's Guide to Directory Services Programming"
>http://www.directoryprogramming.net



david@at-at-at@windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

Steven Cheng [MSFT] 09-01-2008 03:01 AM

Re: What's best practice for connecting to a Sql Server database
 
Hi Dave,

As Joe has suggested, using windows authentication is always preferred(if
possible) since it provide more security. SQL authentication is convenient
since it require less security related configuration among service and
target resource machines.

For more info on ASP.NET 2.0 security strategy, you can have a look at the
following article

#Security Guidelines: ASP.NET 2.0
http://msdn.microsoft.com/en-us/libr...lines0001_data
access

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@microsoft.com.

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subs...#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/...tance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
>From: David Thielen <thielen@nospam.nospam>
>Subject: Re: What's best practice for connecting to a Sql Server database
>Date: Sat, 30 Aug 2008 19:23:53 -0600


>
>Thank you very much. And yes, we're going from 6 to 7 - I keep getting
>that wrong for some reason.
>
>thanks - dave
>
>
>On Fri, 29 Aug 2008 13:05:02 -0500, "Joe Kaplan"
><joseph.e.kaplan@removethis.accenture.com> wrote:
>
>>I doubt you are moving to IIS 8 yet since IIS 7 is the version shipping

in
>>2008 server and Vista. :)
>>
>>That said, I generally prefer using Windows auth over SQL auth when

possible
>>as it makes it possible to centrally manage accounts in AD. However,

some
>>customers may prefer to use SQL auth. Providing an option is probably a
>>good idea.
>>
>>Which account to use should also be something the customer can choose,

but
>>when using Windows auth in an architecture like yours (which looks like

it
>>uses a fixed service account), using the IIS process identity to access

SQL
>>is usually the easiest thing. The customer can configure whatever app

pool
>>identity they want to use to access SQL that way.
>>
>>As to whether they use groups to grant access to SQL or grant access
>>directly to specific security principles should be their decision as

well.
>>I do recommend you use roles in SQL to abstract your permissions at the
>>database level so they can assign whatever principle they want to your

roles
>>in order to grant the correct set of privileges at the SQL to the app.
>>
>>--
>>Joe Kaplan-MS MVP Directory Services Programming
>>Co-author of "The .NET Developer's Guide to Directory Services

Programming"
>>http://www.directoryprogramming.net

>
>
>david@at-at-at@windward.dot.dot.net
>Windward Reports -- http://www.WindwardReports.com
>me -- http://dave.thielen.com
>
>Cubicle Wars - http://www.windwardreports.com/film.htm
>




All times are GMT. The time now is 05:05 PM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.