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

[David Thielen]

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@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

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

 

[Joe Kaplan]

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.

 

[David Thielen]

Thank you very much. And yes, we're going from 6 to 7 - I keep getting
that wrong for some reason.

thanks - dave

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.

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

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

 

[Steven Cheng [MSFT]]

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/library/ms998258.aspx#pagguidelines0001_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:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#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/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------