Microsoft SQL Server 2016 offers administrators two options for implementing how the system will authenticate users: Windows Authentication Mode or Mixed Authentication Mode.
Windows authentication means that SQL Server validates a user’s identity using only their Windows username and password. If the user has already been authenticated by the Windows system, SQL Server does not prompt for a password.
Mixed mode means that SQL Server allows both Windows Authentication and SQL Server Authentication. SQL Server authentication creates user logins unrelated to Windows.
Authentication Basics
Authentication is the process of confirming the identity of a user or computer. The process typically consists of four steps:
- The user makes a statement of identity, usually by providing a username.
- The system challenges the user to prove their identity. The most common challenge is the request for a password.
- The user responds to the challenge by providing the requested proof, usually a password.
- The system verifies that the user has provided acceptable proof, for example by checking the password against a local password database or by using a centralized authentication server.
For our discussion of SQL Server authentication modes, the critical point is at the fourth step above: the point at which the system verifies the user’s proof of identity. Choosing an authentication mode determines where SQL Server will verify the user’s password.
About SQL Server Authentication Modes
Let’s explore these two modes a bit more:
Windows Authentication Mode requires users to provide a valid Windows username and password to access the database server. If this mode is chosen, SQL Server disables SQL Server-specific login functionality and the user’s identity is confirmed only through their Windows account. This mode is sometimes called integrated security because of SQL Server’s dependency on Windows for authentication.
Mixed authentication mode allows the use of Windows credentials but supplements them with local SQL Server user accounts that the administrator creates and maintains within SQL Server. The user’s username and password are stored in SQL Server, and users must re-authenticate each time they connect.
Selecting an authentication mode
Microsoft’s recommended best practice is to use Windows Authentication Mode whenever possible. The main advantage is that using this mode allows you to centralize account management for your entire company in one place: Active Directory. This drastically reduces the chances of error or carelessness. Because the user’s identity is confirmed by Windows, specific Windows user and group accounts can be configured to log on to SQL Server. Also, Windows Authentication uses encryption to authenticate users to SQL Server.
SQL Server authentication, on the other hand, allows usernames and passwords to be passed over the network, making them less secure. This mode can be a good option, however, if users are connecting from different untrusted domains or when using less secure Internet applications such as ASP.NET.
>.For example, consider the scenario where a trusted database administrator leaves your organization on unfriendly terms. If you are using Windows Authentication mode, revocation of that user’s access occurs automatically when you disable or delete the DBA’s Active Directory account.
If you are using mixed authentication mode, you should not only disable the DBA’s Windows account, but you should also check the local user listings on each database server to ensure that there are no local accounts that the DBA can access. know the password. That’s a lot of work!
In short, the mode you choose affects both the level of security and the maintainability of your organization’s databases.