How to check the Authentication Mode with tSQL.

Of course you know, we've got two different Authentication Modes -
  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'