Microsoft SQL Server supports two authentication modes; Windows Authentication and Mixed Mode. Before we move on, I would like to introduce another terminology called authentication method. Authentication method is the way a user is authenticated. Authentication mode on the other hand determines the authentication methods accepted by Microsoft SQL Server.
There are two authentication methods supported by Microsoft SQL Server. They are named Windows Authentication and SQL Server Authentication. The former relies on the Microsoft Windows operating system to authenticate users. The latter will have the user authenticated by Microsoft SQL Server based on the username and password supplied by the client application. The powerful "sa" is an example of login authenticated by SQL Server Authentication.
The following figure shows the login screen of Microsoft SQL Server Management Studio. Note the drop down list with the authentication methods offered.
Let us get back to authentication mode. The mode Windows Authentication will accept the first method namely Windows Authentication only. As for Mixed Mode, both Windows Authentication and SQL Server Authentication are accepted. In other words, logins such as "sa" will be denied if the mode Windows Authentication is active. The following figure shows the configuration of Authentication mode. You will find it at SQL Server Properties Security page.
Windows Authentication is the preferred mode because it provides the highest level of security. It is the default authentication mode for Microsoft SQL Server 2005. The authentication and account policy are enforced by Microsoft Windows operating system.
Mixed Mode is typically used with legacy systems that do not use Windows user accounts. It is also used when the environment consists of clients that need access but is running on operating systems that cannot authenticate with the Microsoft Windows operating system. This happened to clients running Mac OS, UNIX, etc. These clients could login using SQL Server Authentication.
When using Mixed Mode, extra attention has to be given to the login "sa". This account is highly privileged and could pose security threat. It is advisable to create a strong password for this account in case Mixed Mode is used.
0 comment :