SQL Server Hardening

Microsoft SQL Server is one of the most common repositories for an organization's most sensitive data — financial records, PII, credentials, intellectual property — which makes a misconfigured instance a high-value target. The default installation prioritizes compatibility over security: several legacy features ship enabled, network encryption isn't enforced, and the sa account is a well-known name to attack. This guide covers hardening SQL Server (2016–2022, on-premises and IaaS) aligned to the CIS Microsoft SQL Server Benchmarks and NIST SP 800-53 Rev. 5 control mappings.

Scope: SQL Server Database Engine configuration, authentication, network, and auditing hardening. Host OS hardening (Windows Server) and Azure SQL Database/Managed Instance (PaaS, which removes most OS/engine-level concerns) are covered separately.

1. Installation and Surface Area Reduction

Install only the features actually required. Every additional feature (Reporting Services, Integration Services, Full-Text Search, R/Python Machine Learning Services) is additional attack surface and additional patching burden.

• Choose Windows Authentication Mode during setup unless mixed mode is a documented requirement (see Section 2).
• Do not install SQL Server on a domain controller — it violates least-privilege separation and complicates both SQL and AD hardening.
• Remove or disable unused instance features post-install via SQL Server Configuration Manager: SQL Server Browser (if not running named instances that need discovery), SQL Server PolyBase, Machine Learning Services.
• Uninstall or restrict sample databases in production (AdventureWorks, etc.) — never leave sample/demo data or objects in a production instance.

Reduce the Configurable Surface Area

Disable features that are off by default but frequently get turned on for a one-off task and never turned back off:

-- Show advanced options so the settings below are visible
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

-- Disable xp_cmdshell -- allows OS command execution from T-SQL
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

-- Disable Ole Automation Procedures -- COM object instantiation from T-SQL
EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE;

-- Disable Ad Hoc Distributed Queries -- OPENROWSET/OPENDATASOURCE
EXEC sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;

-- Disable Database Mail XPs unless email alerting is required
EXEC sp_configure 'Database Mail XPs', 0;
RECONFIGURE;

-- Disable remote access unless linked servers are required
EXEC sp_configure 'remote access', 0;
RECONFIGURE;

-- Restrict CLR to signed/trusted assemblies only (SQL Server 2017+)
EXEC sp_configure 'clr strict security', 1;
RECONFIGURE;

xp_cmdshell is the single highest-value control on this list — it is the most common post-exploitation pivot from SQL injection to OS command execution. Confirm it's disabled everywhere:

SELECT name, value, value_in_use
FROM sys.configurations
WHERE name = 'xp_cmdshell';

2. Authentication and Authorization

Prefer Windows Authentication

Windows/AD authentication centralizes credential management, inherits domain password policy and account lockout, and eliminates a second credential store to secure. Use Mixed Mode only where a documented application requirement forces SQL logins (e.g., a vendor app that doesn't support integrated auth) — and scope those SQL logins as tightly as possible.

Secure the sa Account

-- Rename sa to something non-obvious (does not appear in sys.sql_logins by the old name)
ALTER LOGIN sa WITH NAME = [renamed_disabled_account];

-- Disable sa entirely if Windows Auth covers all access
ALTER LOGIN [renamed_disabled_account] DISABLE;

-- If sa must remain enabled, enforce a strong, unique, vaulted password and policy
ALTER LOGIN [renamed_disabled_account] WITH PASSWORD = '<strong-unique-password>'
    MUST_CHANGE, CHECK_POLICY = ON, CHECK_EXPIRATION = ON;

sa is the first credential every automated SQL Server attack tool tries. Renaming it defeats naive brute-force tooling; disabling it (when Windows Auth is available) removes the risk entirely.

Least Privilege — Roles, Not Blanket Grants

• Never grant sysadmin to application service accounts. An application connecting as sysadmin means any SQL injection vulnerability in that application is a full instance compromise.
• Use fixed database roles (db_datareader, db_datawriter, db_ddladmin) or custom roles scoped to exactly the objects an application needs, rather than db_owner.
• Use contained database users (SQL Server 2012+) for application logins where practical — they avoid server-level login sprawl and simplify database portability without expanding server-level surface area.

-- Example: application role scoped to specific procedures/tables only
CREATE ROLE app_readwrite AUTHORIZATION dbo;
GRANT SELECT, INSERT, UPDATE ON SCHEMA::app TO app_readwrite;
GRANT EXECUTE ON SCHEMA::app TO app_readwrite;
ALTER ROLE app_readwrite ADD MEMBER [app_service_account];

• Audit role membership regularly — privilege accumulates over time as staff change roles ("privilege creep"):

SELECT dp.name AS principal_name, r.name AS role_name
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
ORDER BY r.name;

Disable Cross-Database Ownership Chaining and Guest Access

-- Disable server-wide cross-db ownership chaining (default off, verify)
EXEC sp_configure 'cross db ownership chaining', 0;
RECONFIGURE;

-- Revoke guest access in every user database (guest cannot be dropped, only disabled)
USE [YourDatabase];
REVOKE CONNECT FROM guest;

3. Network Security

• Firewall: Restrict TCP 1433 (default instance) and the UDP 1434 SQL Browser service to only the application/management subnets that require it — never expose SQL Server directly to the internet.
• Named instances: Either statically assign and firewall the instance's dynamic port, or restrict SQL Browser (UDP 1434) to trusted subnets — it's a discovery service that will happily enumerate instance names to anyone who can reach it.
• Disable unused protocols: In SQL Server Configuration Manager, disable any of Shared Memory / Named Pipes / TCP-IP that aren't required. Most modern deployments only need TCP/IP.
• Force encryption in transit:

# SQL Server Configuration Manager → SQL Server Network Configuration → Protocols
Force Encryption: Yes
Certificate: <CA-issued cert, not the self-signed default>

A self-signed certificate (the out-of-the-box default when Force Encryption is enabled without importing one) still encrypts the channel but doesn't let clients validate the server's identity — use a certificate from an internal or public CA in production.

• Disable legacy TLS: Ensure the underlying OS (Windows Server) has TLS 1.0/1.1 and SSL 2.0/3.0 disabled via registry (SCHANNEL) — SQL Server inherits the OS TLS stack. TLS 1.2 minimum, TLS 1.3 where the SQL Server version and client drivers support it (2022+).

4. Encryption

-- Transparent Data Encryption (TDE)
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong-password>';
CREATE CERTIFICATE TDE_Cert WITH SUBJECT = 'TDE Certificate';

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

-- Encrypted backup, independent of TDE -- protects backups even if TDE is off
BACKUP DATABASE [YourDatabase]
TO DISK = 'D:\Backups\YourDatabase.bak'
WITH COMPRESSION,
     ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = TDE_Cert);

• Back up the TDE certificate and private key to a location separate from the database backups, and vault it securely — losing it makes encrypted backups and detached encrypted databases permanently unrecoverable.
• Consider Always Encrypted (with secure enclaves for richer query support in 2019+) for columns holding regulated data (SSNs, payment card data, health records) where even DBAs with sysadmin shouldn't see plaintext.

5. Auditing and Monitoring

SQL Server Audit (Enterprise-grade, built-in)

-- Server-level audit object
CREATE SERVER AUDIT [Instance_Audit]
TO FILE (FILEPATH = 'D:\Audit\', MAXSIZE = 256 MB, MAX_ROLLOVER_FILES = 50)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT [Instance_Audit] WITH (STATE = ON);

-- Audit failed and successful logins server-wide
CREATE SERVER AUDIT SPECIFICATION [Login_Audit_Spec]
FOR SERVER AUDIT [Instance_Audit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON);

-- Database-level audit of permission and schema changes
USE [YourDatabase];
CREATE DATABASE AUDIT SPECIFICATION [DB_Change_Audit_Spec]
FOR SERVER AUDIT [Instance_Audit]
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP)
WITH (STATE = ON);

What to Alert On

Forward SQL Server Audit output and the Windows Security event log (for Windows-Auth logins) to a central SIEM — this supports NIST SP 800-53 AU-2/AU-6.

C2 Audit Mode / Common Criteria Compliance

For regulated environments requiring a documented compliance mode, common criteria compliance enabled and the legacy c2 audit mode server options provide exhaustive object-access auditing — at a meaningful performance cost, so scope their use to environments where the requirement is explicit.

6. Service Accounts

• Run the SQL Server Database Engine and Agent services under dedicated, least-privilege service accounts — never Local System or a shared domain admin account.
• Prefer Group Managed Service Accounts (gMSA) in AD environments — password rotation is automatic and no human ever needs to know the credential.
• Grant the service account only the local rights SQL Server setup actually requires (Log on as a service, access to data/log/backup directories) — not local Administrator.
• If SQL Server Agent runs jobs under a proxy, scope each proxy's credential to the minimum required subsystem (CmdExec, PowerShell, SSIS) rather than a single all-powerful proxy account.

7. Patch Management

• Track and apply Cumulative Updates (CUs) on a defined cadence (monthly-to-quarterly review, faster for CVEs rated critical) — CUs are cumulative and considered the primary patching mechanism (as of SQL Server 2017+, patching moved away from separate Service Packs).
• Subscribe to the Microsoft Security Response Center (MSRC) and SQL Server release-notes feeds so critical CVEs (e.g., remote code execution in the Database Engine or Reporting Services) are caught immediately, not on the next routine patch cycle.
• Patch test/staging first, validate application compatibility, then roll to production within a documented SLA tied to severity.
• Confirm the running build/patch level:

SELECT @@VERSION;
SELECT SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('ProductLevel'), SERVERPROPERTY('ProductUpdateLevel');

8. Backup Security

• Encrypt every backup (see Section 4) — a stolen unencrypted .bak file is a full data breach.
• Restrict filesystem/share ACLs on backup destinations to the SQL Server service account and backup operators only — backups frequently have weaker access controls than the live database.
• Store an offsite/immutable copy (object-lock storage, offline media, or a backup product with immutability) so a ransomware event that reaches the backup share can't destroy recovery capability.
• Test restores regularly. An unverified backup is not a control — validate with periodic restore-to-scratch-instance drills, not just backup job success/failure status.

Hardening Checklist

NIST SP 800-53 Rev. 5 Control Mapping

References

• CIS Microsoft SQL Server Benchmarks
• NIST SP 800-53 Rev. 5 — Security and Privacy Controls
• Microsoft Learn — SQL Server Security Best Practices
• Microsoft Learn — Transparent Data Encryption (TDE)
• Microsoft Learn — Always Encrypted
• Microsoft Security Response Center