SQL Server Authentication Modes

In order to access databases on a SQL server, one must have a login account and be authenticated. The login is created by the SQL server system administrator and he/she indicates which type of authentication is required by the user.

There are two forms of authentication used to gain access to a SQL server

• SQL authentication
• Windows authentication

User logins are created in SQL server and stored there as well. These credentials are required whenever the user connects to the SQL server.

Windows authentication is the primary form of authentication in many corporate environments due to its enhanced security and integration with Windows. In this case, authentication is performed by Windows which then establishes a trusted connection to the SQL server.  Since the user is authenticated by Windows and it has a trusted connection with the server, the SQL server does not ask for the password.

To better understand the advantages and disadvantages of each authentication type, refer to the following Microsoft article: https://msdn.microsoft.com/en-us/library/ms144284.aspx