locked
Windows authentication for web applications? RRS feed

  • Question

  • Hi,

     

    We are currently upgrading an old JET-based intranet application to SQL Server 2005 and stumbled upon the question whether we should choose SQL authentication or Windows authentication for web applications we are going to develop in the near future.

     

    It's pretty much straightforward for intranet applications - let's use Windows authentication. Even though, I'd debate the security argument: having an additional layer of password protection that SQL authentication provides is handy, especially if someone snoops around the computer when nobody works with it. It also more naturally fits the current security model we are using (JET workgroup schema).

     

    However, my key question is: can Windows Authentication be implemented for web applications (e.g. ASP.NET), accessed from the external web world? We are an academic institution and we would have two cases:

     

    a) users that have Windows accounts (e.g. staff, faculty, existing students)

    b) users that don't have Windows accounts (e.g. prospective students).

     

    Would Windows authentication work for option A?

     

    As for option B, I feel it wouldn't make sense to stress Active Directory with creating thousands of Windows accounts where only a small part of them would turn into permanent ones. If so, SQL authentication is the option to go and to be implemented as the system standard.

     

    What do you think?

    Wednesday, September 3, 2008 10:48 AM

Answers

  •  Ancient wrote:

    Hi,

     

    We are currently upgrading an old JET-based intranet application to SQL Server 2005 and stumbled upon the question whether we should choose SQL authentication or Windows authentication for web applications we are going to develop in the near future.

     

    It's pretty much straightforward for intranet applications - let's use Windows authentication. Even though, I'd debate the security argument: having an additional layer of password protection that SQL authentication provides is handy, especially if someone snoops around the computer when nobody works with it. It also more naturally fits the current security model we are using (JET workgroup schema).

     

    Yes this is possible provided that the web server and SQL Server are within the same domain or there is a trust between the two.  Otherwise, you can impersonate a Windows Account in the connection string.

     

    a) users that have Windows accounts (e.g. staff, faculty, existing students)

     

    If they already have Active Directory accounts, then leverage role based security where you create roles within SQL Server and Windows and then add these already created accounts to the groups. Assign the groups the appropriate permission within AD and SQL. 

    b) users that don't have Windows accounts (e.g. prospective students).

     

    How do you handle proposed students?  Do they get AD accounts like individuals within Group A?  If so then follow the same model. 

    Would Windows authentication work for option A?

     

    Sure - provided these user accounts and SQL Servers are within the same domain or a trust exists. 

    As for option B, I feel it wouldn't make sense to stress Active Directory with creating thousands of Windows accounts where only a small part of them would turn into permanent ones. If so, SQL authentication is the option to go and to be implemented as the system standard.

     

    Agreed - because these same users will have Active Directory privileges which can be a security concern.  Leverage SQL Server accounts and ensure you enforce a password and lockout policy with for the SQL accounts. 

     

    What do you think?

    Wednesday, September 3, 2008 5:29 PM