1) Use an exisitng DMV(Dynamic Management View):
select DISTINCT auth_scheme from sys.dm_exec_connections
2) Check the Windows Registry via Extended Stored Procedure T-SQL:
EXEC master.sys.xp_loginconfig 'login mode'
3) Check the Windows Registry for the instance value(reg key path will vary):
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]
4) Check the SSMS Instance property:
- Open SQL Server Management Studio Object Explorer.
- Right-click on the server name.
- Click Properties and go to Security page to check the SQL Server Authentication setting.