Windows Authentication
Mixed-mode Authentication
Where 'Mixed-Mode is both Windows Authentication and SQL Server Authentication.
Windows Authentication is the most secure, but we often have to use SQL Authentication, for the non-Windows platforms, such as Linux. Hence, the Mixed-mode approach.
Here are a few different ways, aside from SSMS, that you can use to check your authentication mode:
1. xp_instance_regread -
This procedure allows us to read the registry, where SQL Server stores '1' for Windows Authentication, and '2' for SQL Server / Mixed Mode Authentication.
DECLARE @AuthenticationMode INT EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', @AuthenticationMode OUTPUT SELECT CASE @AuthenticationMode WHEN 1 THEN 'Windows Authentication' WHEN 2 THEN 'Windows and SQL Server Authentication' ELSE 'Unknown' END as [Authentication Mode]
2. Server Property -
The Server Property function returns '1' for Windows Authentication, and '0' for SQL Server/Mixed-Mode Authentication.
SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows Authentication' WHEN 0 THEN 'Windows and SQL Server Authentication' END as [Authentication Mode]
NOTE: It is not the same as what is stored in the registry, and returned from the use of xp_instance_regread.
3. xp_loginfo -
Returns a value of 'Windows NT Authentication' for Windows Authentication and 'Mixed' for SQL Server/Mixed-Mode Authentication.
EXEC master.sys.xp_loginconfig 'login mode'