SQL Server Database Security Hardening: The 15-Point Audit Checklist for Instances Exposed to Application Workloads

Check 1: xp_cmdshell disabled

-- Check current state
SELECT name, value_in_use FROM sys.configurations WHERE name = 'xp_cmdshell';
-- Compliant if value_in_use = 0

-- Disable
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;

Check 2: CLR integration disabled (if not required)

-- Check current state
SELECT name, value_in_use FROM sys.configurations WHERE name = 'clr enabled';

-- Disable
EXEC sp_configure 'clr enabled', 0; RECONFIGURE;

CLR integration allows custom .NET assemblies to run inside SQL Server. Attackers use CLR assemblies to execute arbitrary code. Disable unless specific application functionality requires it.

Check 3: Ad hoc distributed queries disabled

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

Check 4: OLE Automation disabled

SELECT name, value_in_use FROM sys.configurations WHERE name = 'Ole Automation Procedures';
-- Should be 0 -- allows COM object creation from T-SQL

Check 5: Database Mail XPs disabled (if not using Database Mail)

SELECT name, value_in_use FROM sys.configurations WHERE name = 'Database Mail XPs';
-- Should be 0 unless SQL Agent alert emails are required

Run all feature checks at once:

SELECT name, value_in_use AS [Current Value],
    CASE
        WHEN name IN ('xp_cmdshell','clr enabled','Ad Hoc Distributed Queries',
                      'Ole Automation Procedures','Database Mail XPs')
             AND value_in_use = 1 THEN 'FAIL - DISABLE'
        ELSE 'PASS'
    END AS [Security Status]
FROM sys.configurations
WHERE name IN ('xp_cmdshell','clr enabled','Ad Hoc Distributed Queries',
               'Ole Automation Procedures','Database Mail XPs')
ORDER BY name;

Check 6: sa account disabled or renamed

-- Check sa account status
SELECT name, is_disabled FROM sys.server_principals WHERE name = 'sa';
-- is_disabled should be 1

-- Disable sa account
ALTER LOGIN [sa] DISABLE;

-- Or rename (additional obfuscation if SA can't be disabled)
ALTER LOGIN [sa] WITH NAME = [sql_admin_disabled];

Check 7: Authentication mode -- Windows Authentication only (if no SQL auth required)

-- Check authentication mode (1 = Windows only, 2 = Mixed mode)
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS WindowsAuthOnly;
-- Should be 1 for Windows-only environments

Change via: SQL Server Management Studio > Server Properties > Security > Server authentication > Windows Authentication mode. Requires restart.

Check 8: Sysadmin membership audit

-- List all sysadmin members (should be minimal)
SELECT sp.name AS [Principal], sp.type_desc AS [Type], sp.is_disabled
FROM sys.server_role_members srm
JOIN sys.server_principals sp ON srm.member_principal_id = sp.principal_id
JOIN sys.server_principals r ON srm.role_principal_id = r.principal_id
WHERE r.name = 'sysadmin'
ORDER BY sp.type_desc, sp.name;
-- Expected: BUILTIN\Administrators, NT SERVICE\SQLSERVERAGENT, NT SERVICE\MSSQLSERVER
-- Unexpected: application service accounts, normal user accounts

Check 9: Password policy and lockout enabled for all SQL logins

-- SQL logins with password policy disabled
SELECT name, is_policy_checked, is_expiration_checked
FROM sys.sql_logins
WHERE is_policy_checked = 0 OR is_expiration_checked = 0;
-- All rows are findings -- set CHECK_POLICY=ON for each listed login

-- Fix example
ALTER LOGIN [app_user] WITH PASSWORD = N'[use current password]',
    CHECK_POLICY = ON, CHECK_EXPIRATION = ON;

Check 10: Linked servers audit

-- List all configured linked servers
SELECT srv.name AS [Linked Server],
       srv.product AS [Product],
       srv.provider AS [Provider],
       ll.uses_self_credential,
       sp.name AS [Mapped Login],
       ll.remote_name AS [Remote Login]
FROM sys.servers srv
LEFT JOIN sys.linked_logins ll ON srv.server_id = ll.server_id
LEFT JOIN sys.server_principals sp ON ll.local_principal_id = sp.principal_id
WHERE srv.is_linked = 1
ORDER BY srv.name;

For each linked server found:

1. Verify it is still operationally required
2. Check what credential is used for the remote connection (column uses_self_credential = the current login's credential is forwarded, which is a lateral movement risk)
3. Verify the remote login has the minimum required permissions on the remote server
4. Remove unused linked servers: EXEC sp_dropserver 'LinkedServerName', 'droplogins';

Linked servers are a common lateral movement path: if an attacker compromises a SQL login with sysadmin on Server A that has a linked server to Server B running as sa, they have effectively compromised Server B as well.

Check 11: Endpoints and protocols -- disable unused ones

-- Check active SQL Server endpoints
SELECT name, endpoint_id, protocol_desc, state_desc, type_desc
FROM sys.endpoints
WHERE type_desc != 'TSQL';  -- Skip the default TSQL endpoint
-- Disable any HTTP, SERVICE_BROKER, or MIRRORING endpoints not in use

Check 12: Excessive database permissions -- PUBLIC role and db_owner audit

-- Find objects with permissions granted to PUBLIC (available to all users)
SELECT OBJECT_SCHEMA_NAME(major_id) AS [Schema],
       OBJECT_NAME(major_id) AS [Object],
       permission_name AS [Permission],
       state_desc
FROM sys.database_permissions
WHERE grantee_principal_id = DATABASE_PRINCIPAL_ID('public')
  AND class_desc = 'OBJECT_OR_COLUMN'
ORDER BY [Schema], [Object];

-- List all db_owner members (should only be DBA accounts)
SELECT dp.name AS [Principal], dp.type_desc
FROM sys.database_role_members drm
JOIN sys.database_principals dp ON drm.member_principal_id = dp.principal_id
JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
WHERE r.name = 'db_owner'
ORDER BY dp.name;

Check 13: SQL Server Audit for failed logins and DDL changes

-- Check if SQL Server Audit is configured
SELECT name, status_desc, audit_file_path
FROM sys.server_audits;
-- Finding: no active audit objects means no centralized audit trail

-- Create a minimal server audit (writes to Application Event Log for SIEM forwarding)
CREATE SERVER AUDIT [SecurityAudit]
TO APPLICATION_LOG
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

CREATE SERVER AUDIT SPECIFICATION [LoginAuditSpec]
FOR SERVER AUDIT [SecurityAudit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP);

ALTER SERVER AUDIT [SecurityAudit] WITH (STATE = ON);
ALTER SERVER AUDIT SPECIFICATION [LoginAuditSpec] WITH (STATE = ON);

Check 14: Transparent Data Encryption (TDE) enabled on databases with sensitive data

-- Check encryption state of all user databases
SELECT db.name, dek.encryption_state, dek.encryption_state_desc,
       dek.key_algorithm, dek.key_length
FROM sys.databases db
LEFT JOIN sys.dm_database_encryption_keys dek ON db.database_id = dek.database_id
WHERE db.name NOT IN ('master','model','msdb','tempdb')
ORDER BY db.name;
-- encryption_state 3 = encrypted, 0 or NULL = not encrypted

-- Enable TDE (requires master key and certificate first)
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '[use vault-managed password]';
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';
-- Back up the certificate IMMEDIATELY after creation
BACKUP CERTIFICATE TDECert TO FILE = N'C:\Secure\TDECert.cer'
    WITH PRIVATE KEY (
        FILE = N'C:\Secure\TDECertKey.pvk',
        ENCRYPTION BY PASSWORD = '[use vault-managed password]'
    );

USE YourDatabase;
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDECert;
ALTER DATABASE YourDatabase SET ENCRYPTION ON;

Check 15: Force encrypted connections (TLS in transit)

-- Check current encryption setting (via registry, not T-SQL)
-- SQL Server Configuration Manager > SQL Server Network Configuration >
-- [Instance] Properties > Flags > Force Encryption = Yes

-- Verify client connections are encrypted
SELECT session_id, encrypt_option, auth_scheme, net_transport
FROM sys.dm_exec_connections
WHERE encrypt_option = 'FALSE';
-- Any results indicate unencrypted connections -- trace to client application and force encryption