Windows Authentication is for the environment that all of your users are part of a Windows domain (have a Windows NT/2000 user account or is a member of a group). Your access to SQL Server is controlled by your Windows NT/2000 account or group, which is authenticated when you log on to the Windows operating system on the client.
If you have a mixed environment (e.g. Windows and Novell), then you have to use SQL Server Authentication where you will have to create a Login ID and Password in SQL Server.
Don’t be confused with “Authentication” and “Authorization.” The “Authentication” is dealing with user’s connection to SQL Server and the “Authorization” is dealing with what data privilege a user has once he is connected to the SQL Server.
Secondly, don’t be confused with “Authentication,”(think of it as a “method”), and “Authentication Mode.” There are two kinds of authentication – Windows authentication and SQL Server authentication. And there are two kinds of authentication mode – Windows authentication mode and mixed authentication
The Windows authentication mode only allows you to login/connect to SQL Server with Windows authentication. The mixed authentication mode allows you to login/connect to SQL Server with either Windows authentication or SQL Server authentication.
Again, if your environment is pure Windows, then you should use Windows authentication mode, which uses Windows authentication to login/connect to SQL Server. If your environment is not pure Windows, like Novell and Windows, then you have to use mixed authentication mode, which will prompt you, during connection, to choose either Windows authentication or SQL Server authentication to connect to SQL Server.
If you are a member of Windows’ administrator group then, it doesn’t matter if you are using Windows authentication mode or mixed authentication mode, you should be able to use Windows Authentication to connect to SQL Server. However, what you can do after the connection depends on what you have been authorized.
Disadvantages of SQL Server Authentication
- 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.
- SQL Server Authentication cannot use Kerberos security protocol.
- Windows offers additional password policies that are not available for SQL Server logins.
Advantages of SQL Server Authentication
- Allows SQL Server to support older applications and applications provided by third parties that require SQL Server Authentication.
- Allows SQL Server to support environments with mixed operating systems, where all users are not authenticated by a Windows domain.
- 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.
- Allows SQL Server to support Web-based applications where users create their own identities.
- Allows software developers to distribute their applications by using a complex permission hierarchy based on known, preset SQL Server logins.