How to Recognize Authentication Mode In SQL Server?

Summary: This guide is all about the process of learning how to recognize authentication mode in SQL server database smartly. There are step-by-step methods along with the images & commands mentioned below for users to recognize SQL server authentication mode without hassles.

Table of Content

• An Overview
• Enable Windows Authentication
• Enable SQL Authentication
• Recognize Authentication Mode
• Conclusion

Overview

SQL Server supports two types of authentication mode; Windows Authentication and Server Authentication. SQL administrator can apply one of these authentication modes as per the requirement of the organization or as per the need for the database management.

Authentication log-ins can be made as; Windows Authentication, where user can connect to Server by making Windows login only, or Mixed Mode Authentication where either SQL Server authentication or Windows authentication can be used.

Enable Windows Authentication

While accessing the SQL Server in the system where the Server is installed, you will not get any message prompt for putting the username and password. In case you are using authentication mode window, in that case also you will not need it. While using this type of authentication, system is aware of the fact that user has already logged in with correct Windows credentials and hence allows user to access the databases as well.

This is also a very much convenient mode. As once you have logged into Windows machine; one can access SQL databases without any extra effort. However, this is a little less secure when more users are involved in the SQL set-up. When remote connections are used, SQL Server authentication(SQL Authentication) is preferable.

Enable SQL Authentication

SQL Authentication is considered to be a classic authentication. This requires users to put the username and password for accessing SQL databases. It is possible that a single SQL Server instance has numerous user accounts through various credentials.

Shared Servers are also used by many users who can access respective databases. In such cases SQL Authentication should be applied. If any user account is not defined by you, a root account, i.e. SA is automatically added. That too along with the password provided by you.

Remote or local connection can be made using these credentials. There are various other scenarios where SQL authentication must be preferred.

How to Recognize Authentication Mode in SQL Server?

Method 1. Launch the SQL Server Management Studio Object Explorer. Go to the Server name provided and right-click it and then click on Properties option.

Now in the left-pane, select Security option and here check which SQL Server authentication is applied. Here Windows Authentication is selected and hence this authentication is applied.

Method 2. Users can also run the following script in order to know what exactly the SQL Server Authentication mode is applied. When the value is “1” authentication mode is Windows & if the value is “0” ‘Windows and SQL Server Authentication’ is applied.

• Script to be run;

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
WHEN 1 THEN 'Windows Authentication'   
WHEN 0 THEN 'Windows and SQL Server Authentication'  
END as [Authentication Mode]  

• Run the script like below-mentioned image.

• Execute the Query and check the results. Here as the SQL Server has Windows Authentication, this Authentication will be displayed.

Method 3: Users can recognize Authentication mode in SQL Server using the xp_instance_regreadprocess also. This lets you read the registry value which stores “1” for Windows Authentication and “2” for the Mixed Mode.

Below mentioned query can be executed for checking the Authentication of SQL Server.

DECLARE @AuthenticationMode INT  
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer',   
N'LoginMode', @AuthenticationMode OUTPUT  

Also Read: Fragmentation in SQL Server

Conclusion

The methods mentioned above recognize Authentication mode in SQL Server. One can perform these methods without needing any external expertise. Windows Registries can also be checked using the Regedit utility. In a nutshell, it’s not that tough to check SQL server authentication mode with the help of experts’ advice.