SQL logins are the only way to handle connections for web applications that connect directly from ASP code to the database with no intermediate components; otherwise, SQL logins have no purpose. SQL logins are still used for a multitude of web-based intranet applications but new ones will be using account delegation allowing us to use the same security model as we do for client-server apps.
I manage several SQL Servers with dozens of small application databases. Windows Authentication allows me to grant permission to a Domain Group and allow the application administrators to manage the users in the groups so I don't have to manage hundreds of SQL Server logins. It is not uncommon for a single database to have several groups with access. One group might have access to the usp_Employee_SetSalary stored procedure while the rest would not; thus, negating the problem with someone accessing the database through a means other than the intended application.
Connection information should not be compiled into an application (a deployment nightmare when anything changes in a large application) and passwords should certainly not be left lying around in the registry or files. MTS provides a security model for components that will allow them to connect to SQL Server using a domain account supplied during setup of the component package on the application server. The password for this domain account would only be known by the system administrators and, especially, not by developers.
This was definitely a good article as it really made me think about the security issues but I think that each point has a subtle flaw, except with the case for using SQL logins for simple web apps.
Design security as a basic part of the database and application design, not as an afterthought for the DBA to handle when the app goes production.
Bryant E. Byrd, MCDBA
SQL Server DBA/Systems Engineer