You should keep the preceding information in mind as you select the security mode to support on your SQL Server instance.
SQL Server authentication should be used only when Windows authentication cannot be used-for instance, because you need to offer access to a user who does not have a Windows account or because users are connecting from Windows systems that are not recognized by your NT or Active Directory domain. Neither can you control how, when, or from where a user connects.
There are no restrictions on the length, complexity, expiration, or reuse of passwords.
In other words, users must separately maintain the password for every SQL Server login they have. If the user changes a password on one server, it has no effect on the user’s passwords on other servers.
The logins and passwords need to be created and stored on each SQL Server to which the user needs access. SQL Server authentication, on the other hand, offers a relatively basic security model. For a large, multiuser, secure environment, Windows authentication should be the clear choice. If that user changes his or her password in Active Directory, the user doesn’t need to also change it in SQL too, because SQL doesn’t store the user’s password. A user account that exists anywhere in the Active Directory structure can be granted access to not only one but any SQL Server in the enterprise. Add to that the robust nature of Active Directory, and you have a true enterprise-level user and resource management solution. Windows users can be allowed to authenticate only during predefined time intervals, the stations they can connect from can be restricted, and when the system is used in conjunction with IPSec, the network administrator can even control which protocols, networks, and remote services to which users have access. The length, complexity, expiration date, and reuse of passwords can be controlled. Windows user accounts can be restricted in a variety of ways. A number points can be made in support of this idea.
It might be seen from the preceding section that the Windows authentication mechanism offers a higher level of security than the SQL Server authentication mechanism. In the previous section, Windows authentication and SQL Server authentication were both explained. In Designing SQL Server 2000 Databases, 2001 Selecting a Security Mode