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

Discussion in 'ASP .Net Security' started by David Thielen, Aug 29, 2008.

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

    Cubicle Wars - http://www.windwardreports.com/film.htm
    David Thielen, Aug 29, 2008
    #1
    1. Advertising

  2. David Thielen

    Joe Kaplan Guest

    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" <> wrote in message
    news:...
    > 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@
    > Windward Reports -- http://www.WindwardReports.com
    > me -- http://dave.thielen.com
    >
    > Cubicle Wars - http://www.windwardreports.com/film.htm
    Joe Kaplan, Aug 29, 2008
    #2
    1. Advertising

  3. 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"
    <> 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@
    Windward Reports -- http://www.WindwardReports.com
    me -- http://dave.thielen.com

    Cubicle Wars - http://www.windwardreports.com/film.htm
    David Thielen, Aug 31, 2008
    #3
  4. 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:
    .

    ==================================================
    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.

    --------------------
    >From: David Thielen <>
    >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"
    ><> 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@
    >Windward Reports -- http://www.WindwardReports.com
    >me -- http://dave.thielen.com
    >
    >Cubicle Wars - http://www.windwardreports.com/film.htm
    >
    Steven Cheng [MSFT], Sep 1, 2008
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Robert Rossney
    Replies:
    0
    Views:
    532
    Robert Rossney
    Feb 7, 2006
  2. Brian Muth
    Replies:
    1
    Views:
    526
    bruce barker
    Dec 17, 2004
  3. Ann Marinas
    Replies:
    12
    Views:
    733
    Guest
    May 5, 2005
  4. Mok
    Replies:
    1
    Views:
    3,154
    Gerhard Pretorius
    Sep 7, 2005
  5. =?Utf-8?B?TmVpbCBQYWRkb2Nr?=

    Connecting to SQL 2000 database with SQL 2005 tools installed

    =?Utf-8?B?TmVpbCBQYWRkb2Nr?=, May 2, 2007, in forum: ASP .Net
    Replies:
    1
    Views:
    394
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
    May 2, 2007
Loading...

Share This Page