Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP .Net Security > What's best practice for connecting to a Sql Server database

Reply
Thread Tools

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

 
 
David Thielen
Guest
Posts: n/a
 
      08-29-2008
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@(E-Mail Removed)
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Reply With Quote
 
 
 
 
Joe Kaplan
Guest
Posts: n/a
 
      08-29-2008
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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@(E-Mail Removed)
> Windward Reports -- http://www.WindwardReports.com
> me -- http://dave.thielen.com
>
> Cubicle Wars - http://www.windwardreports.com/film.htm



 
Reply With Quote
 
 
 
 
David Thielen
Guest
Posts: n/a
 
      08-31-2008
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"
<(E-Mail Removed)> 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@(E-Mail Removed)
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Reply With Quote
 
Steven Cheng [MSFT]
Guest
Posts: n/a
 
      09-01-2008
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:
http://www.velocityreviews.com/forums/(E-Mail Removed).

==================================================
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 <(E-Mail Removed)>
>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"
><(E-Mail Removed)> 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@(E-Mail Removed)
>Windward Reports -- http://www.WindwardReports.com
>me -- http://dave.thielen.com
>
>Cubicle Wars - http://www.windwardreports.com/film.htm
>


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with connecting to SQL Server 2005 Database with Ruby Arti Singh Ruby 2 05-06-2009 04:11 PM
Help. Getting a An error has occurred while establishing a connectionto the server. When connecting to SQL Server 2005, this failure may be causedby the fact that under the default settings SQL Server does not allow remote aboutjav.com@gmail.com ASP .Net 0 05-03-2008 12:43 PM
Connecting to SQL 2000 database with SQL 2005 tools installed =?Utf-8?B?TmVpbCBQYWRkb2Nr?= ASP .Net 1 05-02-2007 03:18 PM
Remember when your piano teacher taught you, "Practice, practice,practice ...?" Wayne Wastier Windows 64bit 3 06-10-2005 08:29 PM
Separate Webserver and SQL Server -- error when connecting asp.netapp to a database Ann Marinas ASP .Net 12 05-05-2005 12:44 PM



Advertisments