9.1 Authentication Modes

 Authentication modes in SQL Server define how users connect to the database engine. There are two primary authentication modes: Windows Authentication and Mixed Mode (Windows Authentication and SQL Server Authentication).

9.1.1 Windows Authentication

Windows Authentication relies on the Windows operating system to authenticate users. It is more secure and often preferred in enterprise environments.

Query: Check Authentication Mode

-- Check the current authentication mode
SELECT
    SERVERPROPERTY('MachineName') AS 'Server Machine Name',
    SERVERPROPERTY('Edition') AS 'SQL Server Edition',
    SERVERPROPERTY('IsIntegratedSecurityOnly') AS 'Windows Authentication Only';

Output

Server Machine Name	SQL Server Edition	Windows Authentication Only
YourMachine	Enterprise Edition	1 (Yes)

In the output, "Windows Authentication Only" is set to 1, indicating that only Windows Authentication is enabled.

9.1.2 Mixed Mode Authentication

Mixed Mode Authentication allows both Windows Authentication and SQL Server Authentication. SQL Server Authentication requires a username and password stored in SQL Server.

Query: Check Authentication Mode and List Logins

```sql
-- Check the authentication mode and list logins
EXEC xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', 'LoginMode';
SELECT name AS 'Login Name', type_desc AS 'Login Type'
FROM sys.server_principals
WHERE type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP');

Output

Authentication Mode	Login Name	Login Type
Mixed Mode (1)	sa	SQL_LOGIN
	YourWindowsUser	WINDOWS_LOGIN
	YourWindowsGroup	WINDOWS_GROUP

In the output, "Authentication Mode" is set to 1, indicating Mixed Mode. Logins include both SQL Server Authentication (SQL_LOGIN) and Windows Authentication (WINDOWS_LOGIN and WINDOWS_GROUP).

9.1.3 Changing Authentication Mode

Changing authentication mode requires restarting the SQL Server service.

Query: Change Authentication Mode

-- Change authentication mode to Mixed Mode (1)
USE master;
EXEC xp_instance_regwrite 'HKEY_LOCAL_MACHINE', 
'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', 'LoginMode',
 'REG_DWORD', 1;

After running the above query, restart the SQL Server service.

Note: Changing authentication mode should be done with caution and during a maintenance window.