0 SQL Server authentication mode

SQL Server support 2 authentication mode.

Windows Authentication mode and SQL server/Windows Authentication mode.

In Windows authentication mode, SQL Server only allow domain account connect to the SQL Server.

This serve configuration can not be checked or changed via sp_configure.

Check the authentication mode

--- 1: Windows Authentication mode, 0: Mixed Mode

EXEC master.sys.xp_loginconfig 'login mode'

EXEC  xp_instance_regread 'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode'

SELECT  SERVERPROPERTY('IsIntegratedSecurityOnly')   

Change the Authentication mode

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

GO

After the changed, we need to bounce SQL Server to make it effective.

Below is the result after the SQL Server restart.