Home / Databases / SQL Server

DatabasesChecklistIntermediate

Advisory: a SQL Server security baseline - authentication, roles, encryption and auditing by design

SQL Server security drifts when each instance is configured by hand. Define a baseline covering authentication, least-privilege roles, encryption in transit and at rest, auditing, surface area and patching, apply it with scripts or policy, and check every instance against it regularly.

Deepak Bhardwaj2 min read

Applies toSQL Server 2019 and 2022 (and later) on-premises and in cloud VMs; many points also apply to Azure SQL Managed Instance

Authentication

  • Prefer Windows/Entra ID authentication; limit SQL logins to applications that can't use them.
  • Rename or disable sa; strong, managed passwords for remaining SQL logins with policy checks on.
  • Service accounts: gMSA (or virtual accounts), no domain admin rights.
SELECT name, is_disabled, is_policy_checked, is_expiration_checked
FROM sys.sql_logins;

Least privilege

Need Grant
Application Database role with specific permissions on schemas/procedures; no db_owner
Read-only reporting db_datareader or a custom role on specific schemas
DBAs Named admin group with sysadmin; break-glass account separately
Monitoring VIEW SERVER STATE / VIEW SERVER PERFORMANCE STATE (2022+), not sysadmin
Agent job management SQLAgent roles (see the Agent roles post) 
-- Members of sysadmin
SELECT m.name FROM sys.server_role_members rm
JOIN sys.server_principals r ON r.principal_id = rm.role_principal_id
JOIN sys.server_principals m ON m.principal_id = rm.member_principal_id
WHERE r.name = 'sysadmin';

Encryption

Layer Option
In transit TLS with a trusted certificate; force encryption; TLS 1.2+ (TDS 8.0 strict encryption in 2022)
At rest TDE for databases; backup encryption; certificates backed up securely
Sensitive columns Always Encrypted (with secure enclaves where needed) or application-level encryption
Masking Dynamic data masking for non-privileged users (not a security boundary on its own)

Auditing

  • SQL Server Audit for logins (failed and successful as needed), permission changes, role membership changes, and access to sensitive tables.
  • Ship audit files to a central SIEM; restrict who can change the audit.

Surface area

  • Disable xp_cmdshell, OLE automation and CLR unless required (and documented).
  • Remove unused linked servers; use specific logins for those that remain.
  • Firewall rules: only application and admin subnets to the SQL port.
SELECT name, value_in_use FROM sys.configurations
WHERE name IN ('xp_cmdshell','Ole Automation Procedures','clr enabled','remote admin connections');

Patching and checks

  • Cumulative updates on a schedule with test rings.
  • Run a vulnerability assessment (SSMS/Defender for SQL) quarterly.
  • Check instances against the baseline with scripts or dbatools; report drift.

Tip

Write the baseline as code: a T-SQL or PowerShell script that both checks and (optionally) fixes. Running it across all instances weekly turns security from a one-off project into routine.

Checklist

  • Authentication and service accounts hardened
  • Least-privilege roles; sysadmin minimal
  • TLS, TDE and backup encryption
  • Audits shipped centrally
  • Surface area reduced
  • Patching cadence and baseline drift checks

Related fixes