locked
best practice RRS feed

  • Question

  • We have a MOSS 2007 intranet site and are building out a bunch of webparts and such that connect to a sql server 2005 database.

    What is the best practice way of connecting to the database from a security standpoint?

    Isn't using sql authentication frowned upon and integrated security is better, but will I have to set entire groups up with permissions on the database for them to be able to use the program?

    Thursday, July 24, 2008 2:27 PM

Answers

  • You are absolutely correct.  Reommended practice is to only utilize integrated security.  There are obviously situations that exist that require the use of mixed mode security, both sql authentication and integrated, but it is recommend to limit the sql server logins as needed. 

     

    If your active directory environment is already structured in a manner that reflects group membership having like responsibilities then you will be able to utilize already existing groups.  If not then you can create groups to contain your users who need specific rights to your database(s).

     

    Hope this was helpful

     

    Thursday, July 24, 2008 4:04 PM

All replies

  • You are absolutely correct.  Reommended practice is to only utilize integrated security.  There are obviously situations that exist that require the use of mixed mode security, both sql authentication and integrated, but it is recommend to limit the sql server logins as needed. 

     

    If your active directory environment is already structured in a manner that reflects group membership having like responsibilities then you will be able to utilize already existing groups.  If not then you can create groups to contain your users who need specific rights to your database(s).

     

    Hope this was helpful

     

    Thursday, July 24, 2008 4:04 PM
  • Note that the recommendation of using Integrated Authentication instead of Sql Authentication is rather old (pre-SQL Server 2005). These days it is harder to put an argument against sql authentication, because of new features that enforce password strength as well as the ability to encrypt login packets.

     

    I recommend having a look at Al Comeau's presentation (SQL Server Security Best Practices) from http://cmcgc.com/Media/WMP/261115/, for some discussion of this topic.

     

    SQL Authentication does provide some advantages over Windows Authentication:

     

     - it makes it easier to port applications from a legacy system to SQL Server or to write cross-platform applications

     - it is contained within SQL Server, so it makes the job of moving SQL Server installations across machines or domains easier

     

    The drawback of SQL Authentication is that it involves another layer of password management. Integrated Authentication can leverage the Windows OS authentication, but it then places a dependency on that OS security layer (domain or local according to the account types), so it can be hard to reconfigure when moving databases around:

     

     - If using Windows Authentication, then domain accounts should be used rather than local accounts, to enable moving a SQL Server instance across machines in the same domain

     - If moves across domains are planned, you should take care of scripting all login setup actions so that you can restore that setup on a new machine

     

    Windows Authentication also means that you may need to manage some security details outside of the database engine, at the domain administrator level.

     

    In the end, you should just look at what works better for you - both these authentication systems are reliable and secure if used properly.

     

    Thanks

    Laurentiu

    Thursday, July 24, 2008 9:58 PM