How to check SQL Server Authentication Mode

There are two authentication modes in SQL server:

  1. Windows Authentication Mode
  2. Windows and SQL Server Authentication Mode (Mixed Mode)

 

Windows Authentication Mode

In windows authentication mode windows logins use to connect to SQL server. Windows Authentication uses the Kerberos security protocol. In enterprise environments, Windows login credentials are normally Active Directory domain credentials

Mixed Mode Authentication

In Mixed mode authentication, “Windows authentication” or “SQL server authentication” use to connect to SQL Server. Windows Authentication Mode is much more secure than Mixed Mode. SQL Server Authentication is provided for backward compatibility only. Whenever possible use Windows Authentication.

Following way we can check SQL Server Authentication Mode 


Option 1: Check from Graphical view:

Right Click Instance and left click “Properties”


Click Security Tab where you can see the Server Authentication


Option 2:  Check Using Server Property

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')  

WHEN 1 THEN 'Windows Authentication'  

WHEN 0 THEN 'Windows and SQL Server Authentication'  

END as [Authentication Mode]

 

Authentication Mode

Windows and SQL Server Authentication

 

 

Option 3: Check Using xp_logininfo

EXEC Master.sys.xp_loginconfig 'login mode'

Name

config_value

Logon mode

mixed


Comments