CIS Microsoft SQL Server 2022 Benchmark

This recommendation verifies that Latest SQL Server Cumulative Update Is Installed on the SQL Server 2022 database server. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to latest SQL Server Cumulative Update Is Installed may leave the SQL Server 2022 database server vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion,
       SERVERPROPERTY('ProductLevel') AS ProductLevel,
       SERVERPROPERTY('Edition') AS Edition;

Apply the latest cumulative update from the Microsoft SQL Server download center.

This recommendation verifies that Only Required SQL Server Components Are Installed on the SQL Server 2022 database server. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to only Required SQL Server Components Are Installed may leave the SQL Server 2022 database server vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

-- Review installed features via SQL Server Installation Center
-- or check: Programs and Features in Control Panel

Uninstall unused features such as Reporting Services, Analysis Services, Integration Services, or Machine Learning Services if not required.

This recommendation verifies that SQL Server Service Accounts Use Managed Service Accounts on the SQL Server 2022 database server. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to sQL Server Service Accounts Use Managed Service Accounts may leave the SQL Server 2022 database server vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

-- Check via SQL Server Configuration Manager:
-- SQL Server Services → right-click → Properties → Log On tab

Use Group Managed Service Accounts (gMSA) or virtual accounts for SQL Server service accounts instead of domain user accounts.

This setting controls whether Ad Hoc Distributed Queries is disabled on the SQL Server 2022 database server. Disabling this feature reduces the attack surface by removing unnecessary functionality that could be exploited by an attacker.

Leaving Ad Hoc Distributed Queries enabled when it is not required unnecessarily expands the attack surface. An attacker could leverage this feature to gain unauthorized access or escalate privileges on the SQL Server 2022 database server.

SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'Ad Hoc Distributed Queries';
-- value_in_use should be 0

EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE;
EXECUTE sp_configure 'Ad Hoc Distributed Queries', 0; RECONFIGURE;
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;

This setting controls whether CLR Enabled is disabled on the SQL Server 2022 database server. Disabling this feature reduces the attack surface by removing unnecessary functionality that could be exploited by an attacker.

Leaving CLR Enabled enabled when it is not required unnecessarily expands the attack surface. An attacker could leverage this feature to gain unauthorized access or escalate privileges on the SQL Server 2022 database server.

SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'clr enabled';
-- value_in_use should be 0

EXECUTE sp_configure 'clr enabled', 0; RECONFIGURE;

This setting controls whether Cross DB Ownership Chaining is disabled on the SQL Server 2022 database server. Disabling this feature reduces the attack surface by removing unnecessary functionality that could be exploited by an attacker.

Leaving Cross DB Ownership Chaining enabled when it is not required unnecessarily expands the attack surface. An attacker could leverage this feature to gain unauthorized access or escalate privileges on the SQL Server 2022 database server.

SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'cross db ownership chaining';
-- value_in_use should be 0

EXECUTE sp_configure 'cross db ownership chaining', 0; RECONFIGURE;

This setting controls whether xp_cmdshell is disabled on the SQL Server 2022 database server. Disabling this feature reduces the attack surface by removing unnecessary functionality that could be exploited by an attacker.

Leaving xp_cmdshell enabled when it is not required unnecessarily expands the attack surface. An attacker could leverage this feature to gain unauthorized access or escalate privileges on the SQL Server 2022 database server.

SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'xp_cmdshell';
-- value_in_use should be 0

EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE;
EXECUTE sp_configure 'xp_cmdshell', 0; RECONFIGURE;
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;

This setting controls whether Ole Automation Procedures is disabled on the SQL Server 2022 database server. Disabling this feature reduces the attack surface by removing unnecessary functionality that could be exploited by an attacker.

Leaving Ole Automation Procedures enabled when it is not required unnecessarily expands the attack surface. An attacker could leverage this feature to gain unauthorized access or escalate privileges on the SQL Server 2022 database server.

SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'Ole Automation Procedures';
-- value_in_use should be 0

EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE;
EXECUTE sp_configure 'Ole Automation Procedures', 0; RECONFIGURE;
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;

This setting controls whether Remote Access is disabled on the SQL Server 2022 database server. Disabling this feature reduces the attack surface by removing unnecessary functionality that could be exploited by an attacker.

Leaving Remote Access enabled when it is not required unnecessarily expands the attack surface. An attacker could leverage this feature to gain unauthorized access or escalate privileges on the SQL Server 2022 database server.

SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'remote access';
-- value_in_use should be 0

EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE;
EXECUTE sp_configure 'remote access', 0; RECONFIGURE;
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;
-- Requires restart of the Database Engine

This recommendation verifies that Windows Authentication Mode Is Used on the SQL Server 2022 database server. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to windows Authentication Mode Is Used may leave the SQL Server 2022 database server vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS IsWindowsAuthOnly;
-- Should return 1

Use SQL Server Management Studio: Right-click server → Properties → Security → select Windows Authentication mode. Restart the SQL Server service.

This setting controls whether the sa Account is disabled on the SQL Server 2022 database server. Disabling this feature reduces the attack surface by removing unnecessary functionality that could be exploited by an attacker.

Leaving the sa Account enabled when it is not required unnecessarily expands the attack surface. An attacker could leverage this feature to gain unauthorized access or escalate privileges on the SQL Server 2022 database server.

SELECT name, is_disabled
FROM sys.server_principals
WHERE sid = 0x01;
-- is_disabled should be 1

ALTER LOGIN [sa] DISABLE;

This recommendation verifies that the sa Account Is Renamed on the SQL Server 2022 database server. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to the sa Account Is Renamed may leave the SQL Server 2022 database server vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

SELECT name
FROM sys.server_principals
WHERE sid = 0x01;
-- Should NOT be 'sa'

ALTER LOGIN [sa] WITH NAME = [renamed_sa_account];

This recommendation verifies that BUILTIN\Administrators Is Not a SQL Server Login on the SQL Server 2022 database server. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to bUILTIN\Administrators Is Not a SQL Server Login may leave the SQL Server 2022 database server vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

SELECT name FROM sys.server_principals
WHERE name = 'BUILTIN\Administrators';
-- Should return empty

DROP LOGIN [BUILTIN\Administrators];

Orphaned Users should be removed from the SQL Server 2022 database server when not required. Removing unnecessary components reduces the attack surface and limits the number of potential vulnerabilities that need to be managed.

Retaining Orphaned Users when it is not needed increases the attack surface. Unpatched or unused components are frequent targets for exploitation and should be removed as part of system hardening.

EXEC sp_change_users_login @Action='Report';
-- Should return empty (no orphaned users)

Map orphaned users to logins or drop them: DROP USER [orphaned_user];

File and directory permissions for Excessive Server-Level should be set to Revoked. Overly permissive access controls can allow unauthorized users to read, modify, or execute sensitive files, potentially compromising the SQL Server 2022 database server.

Incorrect permissions on Excessive Server-Level could allow unauthorized reading, writing, or execution of critical files. Proper file permissions are a foundational control that prevents privilege escalation and data tampering.

SELECT pr.name, pe.permission_name, pe.state_desc
FROM sys.server_principals pr
JOIN sys.server_permissions pe ON pr.principal_id = pe.grantee_principal_id
WHERE pe.permission_name IN ('CONTROL SERVER','ALTER ANY DATABASE','ALTER ANY LOGIN')
AND pr.name NOT IN ('sa','##MS_PolicyEventProcessingLogin##','##MS_PolicyTsqlExecutionLogin##')
ORDER BY pr.name;

Revoke unnecessary permissions following the principle of least privilege.

This recommendation verifies that the guest User Has No Database Permissions on the SQL Server 2022 database server. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to the guest User Has No Database Permissions may leave the SQL Server 2022 database server vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

-- Run in each user database:
SELECT dp.name, dbp.permission_name, dbp.state_desc
FROM sys.database_principals dp
JOIN sys.database_permissions dbp ON dp.principal_id = dbp.grantee_principal_id
WHERE dp.name = 'guest'
AND dbp.permission_name != 'CONNECT';

REVOKE CONNECT FROM guest;

This recommendation addresses the proper configuration of SQL Server Audit on the SQL Server 2022 database server. Proper configuration ensures the component operates securely and in accordance with organizational security policies.

Misconfiguration of SQL Server Audit can lead to security gaps that may be exploited by attackers. A properly configured SQL Server 2022 database server reduces exposure to both known vulnerabilities and configuration drift.

SELECT name, status_desc, audit_file_path
FROM sys.dm_server_audit_status;

CREATE SERVER AUDIT [CIS_Audit]
TO FILE (FILEPATH = 'D:\SQLAudit\', MAXSIZE = 100 MB,
         MAX_ROLLOVER_FILES = 10, RESERVE_DISK_SPACE = OFF)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT [CIS_Audit] WITH (STATE = ON);

This recommendation verifies that Server Audit Specification Captures Required Events on the SQL Server 2022 database server. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to server Audit Specification Captures Required Events may leave the SQL Server 2022 database server vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

SELECT a.name AS AuditName, s.name AS SpecName,
       d.audit_action_name, d.is_group
FROM sys.server_audit_specifications s
JOIN sys.server_audits a ON s.audit_guid = a.audit_guid
JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id;

CREATE SERVER AUDIT SPECIFICATION [CIS_ServerSpec]
FOR SERVER AUDIT [CIS_Audit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP)
WITH (STATE = ON);

This recommendation configures Login Auditing to Failed and Successful Logins on the SQL Server 2022 database server. Setting this value appropriately ensures the system operates within the security parameters defined by the CIS benchmark.

An improperly configured value for Login Auditing could weaken security controls or allow unintended behavior. Setting this to Failed and Successful Logins ensures the SQL Server 2022 database server operates within a well-defined security boundary.

EXEC xp_loginconfig 'audit level';
-- Should return 'all' (both failed and successful)

SSMS → Right-click server → Properties → Security → Login auditing → select Both failed and successful logins. Restart SQL Server.

This recommendation verifies that SQL Server Error Log File Count Is Adequate on the SQL Server 2022 database server. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to sQL Server Error Log File Count Is Adequate may leave the SQL Server 2022 database server vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',
  N'Software\Microsoft\MSSQLServer\MSSQLServer',
  N'NumErrorLogs';
-- Should be >= 12

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
  N'Software\Microsoft\MSSQLServer\MSSQLServer',
  N'NumErrorLogs', REG_DWORD, 12;

This recommendation verifies that TDE Is Enabled for User Databases on the SQL Server 2022 database server. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to tDE Is Enabled for User Databases may leave the SQL Server 2022 database server vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

SELECT db.name, dek.encryption_state,
       dek.encryptor_type, dek.key_algorithm
FROM sys.databases db
LEFT JOIN sys.dm_database_encryption_keys dek ON db.database_id = dek.database_id
WHERE db.database_id > 4;
-- encryption_state 3 = encrypted

-- Create master key and certificate:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong_password>';
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';
-- Enable TDE on user database:
USE [YourDatabase];
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;
ALTER DATABASE [YourDatabase] SET ENCRYPTION ON;

This recommendation verifies that Force Encryption Is Enabled for Network Connections on the SQL Server 2022 database server. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to force Encryption Is Enabled for Network Connections may leave the SQL Server 2022 database server vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

-- Check via SQL Server Configuration Manager:
-- SQL Server Network Configuration → Protocols → Properties → Flags tab
-- ForceEncryption should be Yes
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',
  N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib',
  N'ForceEncryption';

SQL Server Configuration Manager → Protocols → Properties → Flags → set ForceEncryption = Yes. Restart SQL Server.

This recommendation verifies that Backup Encryption Is Used on the SQL Server 2022 database server. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to backup Encryption Is Used may leave the SQL Server 2022 database server vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

SELECT bs.database_name, bs.backup_start_date,
       bs.key_algorithm, bs.encryptor_type
FROM msdb.dbo.backupset bs
ORDER BY bs.backup_start_date DESC;

BACKUP DATABASE [YourDatabase]
TO DISK = 'D:\Backups\YourDatabase.bak'
WITH ENCRYPTION (ALGORITHM = AES_256,
  SERVER CERTIFICATE = BackupCert),
  COMPRESSION;

This setting controls whether SQL Server Browser Service is disabled on the SQL Server 2022 database server. Disabling this feature reduces the attack surface by removing unnecessary functionality that could be exploited by an attacker.

Leaving SQL Server Browser Service enabled when it is not required unnecessarily expands the attack surface. An attacker could leverage this feature to gain unauthorized access or escalate privileges on the SQL Server 2022 database server.

-- PowerShell:
Get-Service SQLBrowser | Select-Object Name, Status, StartType
-- StartType should be Disabled

-- PowerShell:
Stop-Service SQLBrowser
Set-Service SQLBrowser -StartupType Disabled

This recommendation verifies that a Non-Default Port Is Used on the SQL Server 2022 database server. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to a Non-Default Port Is Used may leave the SQL Server 2022 database server vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

-- SQL Server Configuration Manager → Protocols for MSSQLSERVER → TCP/IP → Properties → IP Addresses
-- Check TCP Port value (should NOT be 1433)

Change the TCP port to a non-standard value via SQL Server Configuration Manager. Restart SQL Server. Update firewall rules accordingly.

This setting controls whether Named Pipes Protocol is disabled on the SQL Server 2022 database server. Disabling this feature reduces the attack surface by removing unnecessary functionality that could be exploited by an attacker.

Leaving Named Pipes Protocol enabled when it is not required unnecessarily expands the attack surface. An attacker could leverage this feature to gain unauthorized access or escalate privileges on the SQL Server 2022 database server.

-- SQL Server Configuration Manager → Protocols for MSSQLSERVER
-- Named Pipes should be Disabled

Disable Named Pipes in SQL Server Configuration Manager unless specifically required. Restart SQL Server.

This setting controls whether Remote Admin Connections is disabled on the SQL Server 2022 database server. Disabling this feature reduces the attack surface by removing unnecessary functionality that could be exploited by an attacker.

Leaving Remote Admin Connections enabled when it is not required unnecessarily expands the attack surface. An attacker could leverage this feature to gain unauthorized access or escalate privileges on the SQL Server 2022 database server.

SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'remote admin connections';
-- value_in_use should be 0

EXECUTE sp_configure 'remote admin connections', 0; RECONFIGURE;

This setting controls whether Default Trace is enabled on the SQL Server 2022 database server. Enabling this feature strengthens the security posture by enforcing the recommended configuration via SQL Server Management Studio, T-SQL, or server configuration.

Without Default Trace enabled, the SQL Server 2022 database server may lack critical protections against known attack vectors. Enabling this control mitigates risk and aligns the deployment with industry-accepted security baselines.

SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'default trace enabled';
-- value_in_use should be 1

EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE;
EXECUTE sp_configure 'default trace enabled', 1; RECONFIGURE;
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;

This setting controls whether scan for startup procs is disabled on the SQL Server 2022 database server. Disabling this feature reduces the attack surface by removing unnecessary functionality that could be exploited by an attacker.

Leaving scan for startup procs enabled when it is not required unnecessarily expands the attack surface. An attacker could leverage this feature to gain unauthorized access or escalate privileges on the SQL Server 2022 database server.

SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'scan for startup procs';
-- value_in_use should be 0

EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE;
EXECUTE sp_configure 'scan for startup procs', 0; RECONFIGURE;
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;
-- Requires restart

This recommendation configures TRUSTWORTHY to OFF for User Databases on the SQL Server 2022 database server. Setting this value appropriately ensures the system operates within the security parameters defined by the CIS benchmark.

An improperly configured value for TRUSTWORTHY could weaken security controls or allow unintended behavior. Setting this to OFF for User Databases ensures the SQL Server 2022 database server operates within a well-defined security boundary.

SELECT name, is_trustworthy_on
FROM sys.databases
WHERE database_id > 4 AND is_trustworthy_on = 1;
-- Should return empty

ALTER DATABASE [YourDatabase] SET TRUSTWORTHY OFF;

This recommendation configures AUTO_CLOSE to OFF on the SQL Server 2022 database server. Setting this value appropriately ensures the system operates within the security parameters defined by the CIS benchmark.

An improperly configured value for AUTO_CLOSE could weaken security controls or allow unintended behavior. Setting this to OFF ensures the SQL Server 2022 database server operates within a well-defined security boundary.

SELECT name, is_auto_close_on
FROM sys.databases
WHERE is_auto_close_on = 1;
-- Should return empty

ALTER DATABASE [YourDatabase] SET AUTO_CLOSE OFF;

This recommendation verifies that the Model Database Is Secured on the SQL Server 2022 database server. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to the Model Database Is Secured may leave the SQL Server 2022 database server vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

USE model;
SELECT dp.name, dbp.permission_name, dbp.state_desc
FROM sys.database_principals dp
JOIN sys.database_permissions dbp
ON dp.principal_id = dbp.grantee_principal_id
WHERE dp.name NOT IN ('public','dbo','guest','INFORMATION_SCHEMA','sys');

Review and remove unnecessary permissions from the model database as it serves as the template for all new databases.