SQL Server Authentication Modes

by Manzoor Siddiqui · Published July 24, 2015 · Updated September 24, 2015

Authentication is the process of verifying the credentials and if it is correct then allowing to access the database and its components. Only valid identity will be granted the access to the database server. Any user who provides correct login id and password are granted access to SQL Server so it is very important to manage the login details properly and secure the same. SQL Server also trusts windows authentication and once valid credentials are provided, it grants access.

SQL Server can be configured in two authentication modes.

1. Windows Authentication Mode

2. SQL Server and Windows Authentication Mode

In Windows Authentication Mode, only genuine windows authenticated users are granted access. When user tries to login, SQL Server instance verifies the windows login and grants the access.

In SQL Server and Windows Authentication Mode, both type of users i.e. SQL Server and Windows authenticated are verified and permitted to access the server.

You can choose either Windows Authentication Mode or SQL Server and Windows Authentication Mode at the time of SQL Server installation. You can change this setting any time after installation also. You can right click on database instance and go to Properties then select Security page and you can choose among these two options under Server authentication as shown below.

Once you have changed server authentication mode, it will prompt you to restart the SQL Server, click OK to confirm the same.

During SQL Server installation, when you set SQL Server and Windows Authentication Mode, then ‘sa’ login is enabled and if you select Windows Authentication Mode then ‘sa’ login is disabled and you have to manually activate it if you want to use it. Windows authentication is normally performed by Kerberos protocol.