Science, Tech, Math › Computer Science Choosing a SQL Server Authentication Mode Use one of two options for logging into a SQL Server database Print Science, Tech, Math PHP Programming Perl Python Java Programming Javascript Programming Delphi Programming C & C++ Programming Ruby Programming Visual Basic View More By Mike Chapple Mike Chapple Writer University of Idaho Auburn University Notre Dame Mike Chapple is an IT professional with more than 10 years' experience cybersecurity and extensive knowledge of SQL and database management. Learn about our Editorial Process Updated on September 11, 2020 Microsoft SQL Server offers administrators two choices for implementing how the system will authenticate users: Windows authentication mode or mixed authentication mode. About SQL Server Authentication Modes Let's explore these two modes a little further: 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 the SQL Server-specific login functionality, and the user's identity is confirmed solely through his Windows account. This mode is sometimes referred to as integrated security because of SQL Server's dependence 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 both stored in SQL Server, and users must be re-authenticated each time they connect. Selecting an Authentication Mode Microsoft’s best-practice recommendation is to use Windows authentication mode whenever possible. The main benefit is that the use of this mode allows you to centralize account administration for your entire enterprise in a single place: Active Directory. This tool dramatically reduces the chances of permissions errors. Because the user's identity is confirmed by Windows, specific Windows user and group accounts can be configured to log in to SQL Server. Further, Windows authentication uses encryption to authenticate SQL Server users. SQL Server authentication, on the other hand, allows usernames and passwords to be passed throughout the network, making them less secure. This mode can be a good choice, however, if users connect from different non-trusted domains or when possibly less-secure internet applications are in use, such as ASP.net. Cite this Article Format mla apa chicago Your Citation Chapple, Mike. "Choosing a SQL Server Authentication Mode." ThoughtCo, Apr. 5, 2023, thoughtco.com/choosing-a-sql-server-authentication-mode-1019804. Chapple, Mike. (2023, April 5). Choosing a SQL Server Authentication Mode. Retrieved from https://www.thoughtco.com/choosing-a-sql-server-authentication-mode-1019804 Chapple, Mike. "Choosing a SQL Server Authentication Mode." ThoughtCo. https://www.thoughtco.com/choosing-a-sql-server-authentication-mode-1019804 (accessed March 19, 2024). copy citation