Thursday, February 5, 2009

Advantage Disadvantage or Pros Cons or Difference between Mixed Mode SQL Authentication and Windows Authentication in SQL Server

Just had a chance to compile this information which will be useful. Do comment incase anything is wrong or anything you want to add to it.

SQL Authentication Advantages:
1. Allows SQL Server to support older applications and applications provided by third parties that require SQL Server Authentication.
2. Allows SQL Server to support environments with mixed operating systems, where all users are not authenticated by a Windows domain.
3. Allows users to connect from unknown or untrusted domains. For instance, an application where established customers connect with assigned SQL Server logins to receive the status of their orders.
4. Allows SQL Server to support Web-based applications where users create their own identities.
5. Allows software developers to distribute their applications by using a complex permission hierarchy based on known, preset SQL Server logins

SQL Authentication Dis-advantages:
1. You must supply the SQL Server login and password when you connect to SQL Server as SQL Login.
2. If a user is a Windows domain user who has a login and password for Windows, he must still provide another (SQL Server) login and password to connect. Keeping track of multiple names and passwords is difficult for many users. Having to provide SQL Server credentials every time that one connects to the database can be annoying.
3. SQL Server Authentication cannot use Kerberos security protocol.
4. Windows offers additional password policies that are not available for SQL Server logins.

Windows Authenication Advantages:
1. Single-password access to all resources (Including SQL Server) on a Windows NT domain, Password Expiry Policy and encryption across the network (don't have to send login names and passwords over the network).
2. Also Auditing, Minimum password length and account lockout after multiple invalid login requests.
3. You do not have to specify a login name and password, to connect to SQL Server.
4. Adding login is controlled in Active Directory. So you can create a ACL and keep adding users there and just add that group name in SQL Server Login.

Windows Authenication Dis-Advantages:
1. Not available in Windows 9x.
2. Need Active Directory access for SQL DBA else he may not be aware of users being added to AD group.

No comments:

Post a Comment

Please do not spam!