SQL Server : 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 INTEXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode', @AuthenticationMode OUTPUTSELECT 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'